How to change a Files alternative name using IBM i SQL

IBM i

Aug 23

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

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 🙂 )

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.