How to SQL table's long names in RPG program

The seed for this post came from a conversation I had with one of my work colleagues. He complained that even though he could give the columns (fields) in a DDL (SQL) tables names longer than ten characters he found that it was hard to use these names in programs, especially those using printer or display files.

He is not the first person who had made this observation to me. Over the time I have written this web site others have made the same observation.

Let me show what I shared with him so that he could easily overcome this perceived shortcoming.

We need to start with a DDL table, with long column names:

01 CREATE TABLE MYLIB.MYTABLE ( 02 FIRST_COLUMN CHAR(3) NOT NULL, 03 SECOND_COLUMN VARCHAR(20), 04 THIRD_COLUMN DECIMAL(10,2), 05 FOURTH_COLUMN VARCHAR(50) 06 ) ;

After I have created the table I use the SYSCOLUMNS Db2 of i view to see what names have been automatically picked as the "system" names, I call them "short" names.

SELECT SYSTEM_COLUMN_NAME,COLUMN_NAME FROM QSYS2.SYSCOLUMNS WHERE TABLE_NAME = 'MYTABLE' AND TABLE_SCHEMA = 'MYLIB' SYSTEM_COLUMN_NAME COLUMN_NAME FIRST00001 FIRST_COLUMN SECON00001 SECOND_COLUMN THIRD00001 THIRD_COLUMN FOURT00001 FOURTH_COLUMN

I have to agree with my colleague that those a pretty nasty "short" names for the columns.

My colleague had asked specifically about the "long" names with display and printer files.

I need to use the "short" names in both files. Below is how I would define them in a subfile record format.

01 A R SFL01 SFL 02 A Z1RRN 5S 0H 03 A Z1OPT 1A B 2 2 04 A FIRST00001R O 2 4 05 A SECON00001R O 2 8 06 A THIRD00001R O 2 29EDTCDE(J) 07 A FOURT00001R H

Those of you familiar with printer files will recognize this as a printer file record format.

01 A REF(MYTABLE) 02 A R PRINT 03 A FIRST00001R 1SPACEB(001) 04 A SECON00001R +1 05 A THIRD00001R +1EDTCDE(J) 06 A FOURT00001R +1

I created a very simple RPG program, and this is part of it.

01 **free 02 dcl-ds Data extname('MYTABLE') qualified dim(9999) 03 end-ds ; 04 dcl-f TESTDSPF workstn indds(Dspf) sfile(SFL01:d_Z1RRN) prefix(d_) ; 05 dcl-f TESTPRTF printer prefix(p_) ;

Line 1: Let my RPG be free.

Lines 2 and 3: All of the programs I write now use SQL to get the data from the tables/files. As I would be using a multiple row fetch to get the data from the table, I need a data structure array for the columns I will fetch. Rather than define the subfields within the program I have defined it as externally defined using the table itself.

Line 4: This is the file definition for the display file. The only reason I have used the PREFIX keyword is to ensure that in the compile listing all the fields from the display file will start d_.

Line 5: Same is true of the file definition for the printer. By using the PREFIX all printer file fields will start p_.

What does the compile listing show?

dcl-ds Data extname('MYTABLE') qualified dim(9999) end-ds ; *-------------------------------------------------- * Data structure . . . . : DATA * External format . . . : MYTABLE : MYLIB/MYTABLE *-------------------------------------------------- <---------------------- Source Specifications ------ ....1....+....2....+....3....+....4....+....5....+.. D FIRST00001 3A D SECON00001 20A VARYING D THIRD00001 10P 2 D FOURT00001 50A VARYING O u t p u t B u f f e r P o s i t i o n s Statement Start End Field or Constant Number Pos Pos 4000002 1 5 D_Z1RRN 4000003 6 6 D_Z1OPT 4000004 7 9 D_FIRST00001 4000005 10 29 D_SECON00001 4000006 30 39 D_THIRD00001 4000007 40 89 D_FOURT00001 6000002 1 3 P_FIRST00001 6000003 4 23 P_SECON00001 6000004 24 33 P_THIRD00001 6000005 34 83 P_FOURT00001

All of the fields based upon the columns in my table use the "short" names. This is what is driving my colleague to only use ten long column names.

The solution is just five characters long, ALIAS. All I need to do is add the that keyword to my data structure array definition and to the definition of my two files.

02 dcl-ds Data extname('MYTABLE') qualified alias dim(9999) 03 end-ds ; 04 dcl-f TESTDSPF workstn indds(Dspf) sfile(SFL01:d_Z1RRN) prefix(d_) alias ; 05 dcl-f TESTPRTF printer alias prefix(p_) ;

After compiling this program when I look in the compile listing I can see that the ALIAS has allowed the "long" names of the columns to be used for the data structure's subfields and the display and printer file's fields.

dcl-ds Data extname('MYTABLE') qualified alias dim(9999) end-ds ; *-------------------------------------------------- * Data structure . . . . : DATA * External format . . . : MYTABLE : MYLIB/MYTABLE *-------------------------------------------------- <---------------------- Source Specifications ------ ....1....+....2....+....3....+....4....+....5....+.. D FIRST_COLUMN 3A D SECOND_COLUMN 20A VARYING D THIRD_COLUMN 10P 2 D FOURTH_COLUMN 50A VARYING O u t p u t B u f f e r P o s i t i o n s Statement Start End Field or Constant Number Pos Pos 4000002 1 5 D_Z1RRN 4000003 6 6 D_Z1OPT 4000004 7 9 D_FIRST_COLUMN 4000005 10 29 D_SECOND_COLUMN 4000006 30 39 D_THIRD_COLUMN 4000007 40 89 D_FOURTH_COLUMN 6000002 1 3 P_FIRST_COLUMN 6000003 4 23 P_SECOND_COLUMN 6000004 24 33 P_THIRD_COLUMN 6000005 34 83 P_FOURTH_COLUMN

Yes, it is that simple. There is no need to change the files, just the RPG program using them.

Now my colleague can go back to making column names in his tables that are meaningful, rather than abbreviating the names to just ten long.

 

This article was written for IBM i 7.3, and should work for 7.1 and later too.