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…
We can use the SQL “alternative name” feature of SQL. So how do we change that with IBM i?
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:
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
RENAME TABLE NLITTEN/THISTABLEISVERYFUNKY TO SYSTEM NAME THISTABFUN
for the same result.
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 🙂 )
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.
Copying iSeries fields from numeric to Alpha – aka using SQL to change column data type
How to capture IBM-i job info for submitted jobs
What is IBM i Email and SPF?
Updating Numeric DTAARA in RPGLE
Register license key in SOFTLANDING SOFTMENU
Going the (Levenshtein) Distance in RPG Free
Don’t hardcode library names in your TURNOVER SQL source #youbigsilly
How to Install IBM Access Client Solutions (ACS)
5733XJ1 IBM i Access Client Solutions – QuickStartGuide