Renaming existing table with a 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.