Wednesday, March 3, 2021

Renaming existing table with a short system name

rename a sql table to give it a good short system name

Someone created a SQL DDL Table with a long name and with long column names only, and did not give short or system names. I have discussed in an earlier post how to add the short/system names to the Table and columns when you are creating the table. But in this example the programmer who created just created the Table with the long names only:

01  DROP TABLE IF EXISTS MYLIB.THIS_IS_A_LONG_NAME ;

02  CREATE TABLE MYLIB.THIS_IS_A_LONG_NAME(
03    FIRST_LONG_FIELD_NAME CHAR(7),
04    SECOND_LONG_FIELD_NAME TIMESTAMP
05  ) ;

Line 1: This deletes the Table if it already exists. A nice addition in the latest round of Technology Refreshes is the addition of the IF EXISTS, it prevents the DROP TABLE from generating an error if the Table does not exist.

Lines 2 – 5: The SQL statement I used to create my example Table.

Line 2: I am creating this Table in my library, MYLIB, and its name is longer than the ten characters maximum allowed by the system names.

Lines 3 and 4: The Table contains two columns, both with names longer than ten characters.

I can see what short system name has been chosen for this new Table using the SYSTABLES View:

01  SELECT TABLE_NAME,SYSTEM_TABLE_NAME
02    FROM QSYS2.SYSTABLES 
03   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
04     AND TABLE_NAME = 'THIS_IS_A_LONG_NAME' ;

Line 1: TABLE_NAME is the long SQL name for the Table, while SYSTEM_TABLE_NAME is the short system name.

The results of the above statement are:

TABLE_NAME           SYSTEM_TABLE_NAME
-------------------  -----------------
THIS_IS_A_LONG_NAME  THIS_00001

You have to agree that the generated system name is not very descriptive of the Table. Fortunately there is a SQL RENAME statement that allows me to change the long name, change the short system name, or both. In this case I just want to change the short system name:

RENAME TABLE MYLIB.THIS_IS_A_LONG_NAME TO SYSTEM NAME THISSYSNME ;

Now when I run the same SQL statement over SYSTABLES I see the changed system name:

TABLE_NAME           SYSTEM_TABLE_NAME
-------------------  -----------------
THIS_IS_A_LONG_NAME  THISSYSNME

What about the column names? To see the system generated short name of those I use the SYSCOLUMNS View:

01  SELECT TABLE_NAME,COLUMN_NAME,SYSTEM_COLUMN_NAME
02    FROM QSYS2.SYSCOLUMNS
03   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
04     AND TABLE_NAME = 'THIS_IS_A_LONG_NAME' ;

I can clearly see that the system generated short column names are at best limited and will lead to confusion later.

TABLE_NAME           COLUMN_NAME             SYSTEM_COLUMN_NAME
-------------------  ----------------------  ------------------   
THIS_IS_A_LONG_NAME  FIRST_LONG_FIELD_NAME   FIRST00001
THIS_IS_A_LONG_NAME  SECOND_LONG_FIELD_NAME  SECON00001

I searched the IBM's KnowledgeCenter and found references for the other flavors of Db2 that they have the ability to rename columns. I did reach out to IBM to ask them if there was a method to rename a column using Db2 for i. Their response was that it is not currently possible. They did say that there is a RFE to bring Db2 for i in line with the other flavors. You can vote for this RFE here. I voted for it.

The solution they offered was to use the CREATE OR REPLACE TABLE:

01  CREATE OR REPLACE TABLE MYLIB.THIS_IS_A_LONG_NAME  
02  (FIRST_LONG_FIELD_NAME FOR "FIRST" CHAR(7),
03   SECOND_LONG_FIELD_NAME FOR "SECOND" TIMESTAMP) 
04  ON REPLACE PRESERVE ROWS ;

Line 4 is extremely important as that tells Db2 for i if it replaces an existing table it will copy data to the new Table. I did not give table a system short name as I had done before. But if I wanted to do so the statement would just require one additional line:

01  CREATE OR REPLACE TABLE MYLIB.THIS_IS_A_LONG_NAME
02     FOR SYSTEM NAME "THISSYSNME" 
03  (FIRST_LONG_FIELD_NAME FOR "FIRST" CHAR(7),
04   SECOND_LONG_FIELD_NAME FOR "SECOND" TIMESTAMP) 
05  ON REPLACE PRESERVE ROWS ;

Line 2: This is the addition for the short system name for the Table.

Now when I run the SQL statement over SYSCOLUMNS I can see the new short system column names:

TABLE_NAME           COLUMN_NAME             SYSTEM_COLUMN_NAME
-------------------  ----------------------  ------------------  
THIS_IS_A_LONG_NAME  FIRST_LONG_FIELD_NAME   FIRST
THIS_IS_A_LONG_NAME  SECOND_LONG_FIELD_NAME  SECOND

I do recommend you vote for the RFE to make it easier for all of us to make this seemingly simple change to our tables.

 

You can learn more about the RENAME TABLE SQL statement from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

4 comments:

  1. I make it a habit of using Generate SQL immediately after creating new tables. It makes finding the automatically generated short names easy to find and fix. I find that I frequently have overlooked supplying short names for fields that are 11 or 12 characters long.

    ReplyDelete
  2. Great tip! Thanks Simon!

    ReplyDelete
  3. I believe ON REPLACE PRESERVE ROWS is the default so you don't have to specify it and the data is still preserved.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.