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.
Oddly enough I ran into this issue this morning and used the same technique to resolve it. Kudos! Cheers :)
ReplyDeleteHi 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
ReplyDeleteIt depends...
DeleteIf 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.
I would never have thought that the command would have created another object with the same name. Your workaround makes sense.
ReplyDeleteThrowing out a different approach.
ReplyDeleteCreate 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.
cool one Simon
ReplyDeleteThanks for the helpful article and the comment by Anonymous, I wasn’t aware of "for system name".
ReplyDeleteWe 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.