.st0{fill:#FFFFFF;}

How to change a Files alternative name using IBM i SQL 

 August 23, 2017

By  NickLitten

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 šŸ™‚ )

NickLitten


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

  • 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"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

    Get In Touch

    Iā€™m always looking for awesome input, feedback and critique!

    >