Wednesday, May 5, 2021

Problem with long SQL object names when using IBM i commands

rename table with new long name

In my opinion the long SQL names I can give to Tables, Views, and Indexes allows me to give these objects good descriptive names. I also give them short system names, so I can easily use IBM i commands with them. But this can cause me problems.

I have a table, you will not be surprised to learn I have called it TESTTABLE and it resides in my library MYLIB, that I want to make a copy of to the library MYLIB2. I am sure I am not the only person who would use the Create Duplicate Object command, CRTDUPOBJ, to do this:

CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(MYLIB) 
            OBJTYPE(*FILE)
            TOLIB(MYLIB2) NEWOBJ(@CRTDUPOBJ) 
            CST(*NO) TRG(*NO)

I get the following message to confirm that the file was created in MYLIB2.

Object @CRTDUPOBJ in MYLIB2 type *FILE created.

Now I want to create a second copy of TESTTABLE in the same library, MYLIB2, as the first duplicate:

CRTDUPOBJ OBJ(TESTTABLE) FROMLIB(MYLIB) 
            OBJTYPE(*FILE)
            TOLIB(MYLIB2) NEWOBJ(@CRTDUPOB2) 
            CST(*NO) TRG(*NO)

This time the duplication of the file failed with the following messages in the job log:

Alternative name for file @CRTDUPOB2 not allowed.
File not created.
Cannot create duplicate file @CRTDUPOB2 in MYLIB2.
0 objects duplicated. 1 objects not duplicated.

If I prompted the first of these messages with the F1 key I see the message's details:

                   Additional Message Information

Message ID . . . :  CPF327E      Severity . . . :  40
Message type . . :  Diagnostic
Date sent  . . . :  DD/DD/DD     Time sent  . . :  TT:TT:TT

Message . . . . :   Alternative name for file @CRTDUPOB2 not allowed.
Cause . . . . . :   An attempt was made to create, move, or change 
  database file @CRTDUPOB2 with alternative name THIS_IS_MY_TEST_TABLE 
  into library MYLIB2. However, the operation was not done for reason
  code 2.  The reason codes are:

  2 - The alternative name is a duplicate of the alternative name for
  database file @CRTDUPOBJ that already exists in library MYLIB2.

This second Table could not be created as I cannot have two Table objects with the same long SQL name. I can check this by running the following SQL statement over the SYSTABLES SQL View:

01  SELECT TABLE_NAME,SYSTEM_TABLE_NAME,SYSTEM_TABLE_SCHEMA 
02    FROM QSYS2.SYSTABLES
03   WHERE SYSTEM_TABLE_SCHEMA IN ('MYLIB','MYLIB2')

Line 1: I think the column names describe the contents of the columns.

Line 3: I am using the WHERE ... IN so it will list all of the files in both of those libraries, rather than using an AND.

The results show the issue, the copied file still has the original long name:

TABLE_NAME             SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
---------------------  -----------------  -------------------
THIS_IS_MY_TEST_TABLE  TESTTABLE          MYLIB
THIS_IS_MY_TEST_TABLE  @CRTDUPOBJ         MYLIB2

I wonder if the Copy File command, CPYF, keeps the long name too?

CPYF FROMFILE(MYLIB/TESTTABLE)
       TOFILE(MYLIB2/@CPYF)
       MBROPT(*NONE) CRTFILE(*YES)

When I run the same SQL statement as before I find, alas, it does.

TABLE_NAME             SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
---------------------  -----------------  -------------------
THIS_IS_MY_TEST_TABLE  TESTTABLE          MYLIB
THIS_IS_MY_TEST_TABLE  @CPYF              MYLIB2

Fortunately SQL offers me a solution to this problem. I can use a CREATE TABLE statement with a LIKE in it and the Table is duplicated just like using the CRTDUPOBJ command.

CREATE TABLE MYLIB2.SQL_LIKE LIKE MYLIB.TESTTABLE

When I check to see what the long name of the new Table is I find:

TABLE_NAME             SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
---------------------  -----------------  -------------------
THIS_IS_MY_TEST_TABLE  TESTTABLE          MYLIB
SQL_LIKE               SQL_LIKE           MYLIB2

The long name was changed.

If I need to copy the contents of TESTTABLE to SQL_LIKE I can just use the following SQL statement:

INSERT INTO MYLIB2.SQL_LIKE (SELECT * FROM MYLIB.TESTTABLE)

Giving a Table a new long or short name can be done using the RENAME SQL statement. To rename the SQL_LIKE Table I could do so using:

RENAME TABLE MYLIB2.SQL_LIKE TO NEW_NAME

When I run the SQL statement over SYSTABLES I see both the long and short names have been changed to NEW_NAME.

TABLE_NAME            SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
--------------------- -----------------  -------------------
NEW_NAME              NEW_NAME           MYLIB2

If I wanted to rename SQL_LIKE and give it a new long and short name I would use the following:

RENAME TABLE MYLIB2.SQL_LIKE
    TO THIS_IS_NEW_NAME 
   FOR SYSTEM NAME NEW_NAME

Which gives me:

TABLE_NAME            SYSTEM_TABLE_NAME  SYSTEM_TABLE_SCHEMA
--------------------- -----------------  -------------------
THIS_IS_NEW_NAME      NEW_NAME           MYLIB2

 

In conclusion, I prevent this problem by using the CREATE TABLE with the LIKE to duplicate the Table, and then use the RENAME to give the copy its own unique long name.

 

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

7 comments:

  1. Oddly enough I ran into this issue this morning and used the same technique to resolve it. Kudos! Cheers :)

    ReplyDelete
  2. Hi Simon: What about long names in SQL. I am writing an application and I had to use the short names in RPG. Is there any way to avoid that?. Sorry if you wrote about it before and I missed. Thanks. Carlos

    ReplyDelete
    Replies
    1. It depends...

      If you are using SQL for I/O then you can use the long or the short names.

      If you are using RPG native I/O (why?) then you need to use the ALIAS keyword. See here.

      Delete
  3. I would never have thought that the command would have created another object with the same name. Your workaround makes sense.

    ReplyDelete
  4. Throwing out a different approach.

    Create the table without duplicating data:
    create table mylib.this_is_my_test_table_2 for system name tsttbl2
    like mylib.this_is_my_test_table_1;

    Create the table and duplicate the data:
    create table mylib.this_is_my_test_table_2 for system name tsttbl2 as
    ( select * from mylib.this_is_my_test_table_1 ) with data;

    Using the prior statement and altering the "with data" can also be used to create a duplicate without data:
    create table mylib.this_is_my_test_table_2 for system name tsttbl2 as
    ( select * from mylib.this_is_my_test_table_1 ) with no data;

    As anything goes, there is more than 1 way to do it. Just thought I would share alternate ways and the way I typically do it.

    ReplyDelete
  5. Reynaldo Dandreb MedillaSeptember 5, 2021 at 8:21 AM

    cool one Simon

    ReplyDelete
  6. Thanks for the helpful article and the comment by Anonymous, I wasn’t aware of "for system name".

    We are discussing if we should use long SQL names at all in our company. It’s nice to have them in SQL but it’s not so convenient to switch between two names depending on what you are doing.

    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.