Don't Let SQL Name Your Baby, Take 2

Published: March 5, 2008

Hey, Ted:

Don't Let SQL Name Your Baby gave some good advice on tackling the problems created by column names longer than 10 characters. Table names of more than 10 characters can also cause problems, especially if you need to add the same table on multiple systems. Plus, if you want to do updates in an RPG program, it's nice to have a record format name different from the table name.

--Tim

Tim points out another discrepancy between the System i's QSYS.LIB file system and relational databases that run on other systems. Using SQL, you can create a table with a very long name, but object names can't be more than 10 characters long. Here's how you might create a table (physical file) with a very long name.

create table tholt/AVeryLongTableName
   (key dec(3), data char(10))

In order for you to access that file from commands like Copy File (CPYF) and Run Query (RUNQRY), the system generates a name, which in this case is something like AVERY00001. If you don't like that name, you can use CL's Rename Object (RNMOBJ) command to rename the physical file.

rnmobj obj(tholt/avery00001) objtype(*file) newobj(averylong)

Or you can use SQL's RENAME command.

rename table avery00001 to system name averylong

In either case, the new i5/OS-compliant file name is AVERYLONG.

Tim has what I consider to be a better method. First, create the table using a desired record format name.

create table rtesttbl etc. etc. etc.

Then rename it.

rename rtesttbl to testing_long_sql_names
for system name testtblf

After this operation, physical file TESTTBLF, with record format RTESTTBL, has a long SQL name of TESTING_LONG_SQL_NAMES.

--Ted

RELATED STORY

Don't Let SQL Name Your Baby

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot