August 23

1 comments

How to change a Files alternative name using IBM i SQL

By NickLitten

August 23, 2017

#alternative, #db2, #SQL, #udb

SQL works very nicely on IBM i Servers — the added benefit is that we can access the database using SQL from other machines, access it using IBM SQL and also using Native (DB2) File IO. But, SQL has some features that native database IO does not support – huge long table names (aka “alternative name”) for example. “OLD RPG” programs can only access file names with a max of 10 characters so…

How can we read these long files names with an old RPG3 program?

We can use the SQL “alternative name” feature of SQL. So how do we change that with IBM i?

IBM i Files are just like SQL Tables but names are limited to 10 characters

So, what happens when we use SQL to create a table with a long file name:

 CREATE TABLE NLITTEN/THISTABLEISVERYFUNKY (SOMEDATA CHAR (100 ))

IBM i SQL creates this table with it’s glorious long file name and it also associates a shortened form of this name so we can read it outsicde of the SQL. The system generates a file name, in this case file(NLITTEN/THIST00001). Not a very nice name as you can see. Luckily we can rename this file using a couple of ways:

RNMobj from the command line

Use RNMOBJ and it doesnt alter the long SQL file name. So, in this example I use:

RNMOBJ OBJ(NLITTEN/THIST00001) 
 OBJTYPE(*FILE) 
 NEWOBJ(THISTABFUN)

So SQL can read the table using its long name of THISTABLEISVERYFUNKY and RPG programs can read it using its short name THISTABFUN

SQL ‘RENAME’ command

RENAME TABLE NLITTEN/THISTABLEISVERYFUNKY TO SYSTEM NAME THISTABFUN

How to change a Files alternative name using IBM i SQL 1

for the same result.

Of course none of this would be a problem if….

It had been created with both long and short names in the beginning

CREATE TABLE NLITTEN/THISTABLEISVERYFUNKY FOR SYSTEM NAME THISTABFUN
 (SOMEDATA CHAR (100 ))

(Thanks Grant 🙂 )

  • Hi Nick,
    Possibly worth mentioning in this post that you can also specify the short name at table creation time
    e.g. CREATE TABLE NLITTEN/THISTABLEISVERYFUNKY FOR SYSTEM NAME THISTABFUN (SOMEDATA CHAR (100 ))
    Regards, Grant

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >

    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"