I received a question asking me how to create a table with a long name that also had a good short name, and the long column names within the table would have meaningful short names too.

I can easily create a table with a long name and long column names:

01 CREATE TABLE MYLIB.THIS_IS_A_LONG_FILE_NAME ( 02 FIRST_LONG_FIELD_NAME CHAR(7), 03 SECOND_LONG_FIELD_NAME TIMESTAMP 04 ) ;

If I use PDM to look for the object I just generated it is not clear that this is the table from its generated short, or system, name.

Work with Objects Using PDM Library . . . . . MYLIB Opt Object Type Attribute Text THIS_00001 *FILE PF-DTA

The system generated short names for the columns/fields are equally unclear.

Field Beg End Lth Dec Fmt ---------- ----- ----- ---- --- --- FIRST00001 1 7 7 A SECON00001 8 33 26 Z

It will come as no surprise to learn that Db2 for i has an ability to allow us programmers the ability to give our tables and columns short names.

01 CREATE TABLE MYLIB.THIS_IS_A_LONG_FILE_NAME 02 FOR SYSTEM NAME "LONGFILE" ( 03 FIRST_LONG_FIELD_NAME FOR COLUMN "FIRST" CHAR(7), 04 SECOND_LONG_FIELD_NAME FOR COLUMN "SECOND" TIMESTAMP, 05 PRIMARY KEY (FIRST_LONG_FIELD_NAME,SECOND_LONG_FIELD_NAME) 06 ) ;

Line 2: This line is where I can give my table a short, or system, name. The same rules apply as if I was creating a DDS file:

The short name must be 10 characters of less

The short name cannot be the same as any file in library

The short name cannot start with a number, etc.

Lines 3 and 4: The FOR COLUMN is where I give my long column name a short name. Do remember that these have to be the same "rules" I would use if I was defining a field name in a DDS file.

The short name must be 10 characters of less

The short name cannot be the same as any other short name used in the table

The short name cannot start with a number, etc.

Line 5: I am so happy with my table that I going to give it a key. The primary key statement is the equivalent of using the UNIQUE keyword in a DDS file, gives my table a unique key.

I am going to give the columns the equivalent of column headings, lines 7 – 10, field text, lines 11 – 14, and a object description, line 15.

07 LABEL ON COLUMN THIS_IS_A_LONG_FILE_NAME ( 08 FIRST_LONG_FIELD_NAME IS 'First field', 09 SECOND_LONG_FIELD_NAME IS 'Second field' 10 ) ; 11 LABEL ON COLUMN THIS_IS_A_LONG_FILE_NAME ( 12 FIRST_LONG_FIELD_NAME TEXT IS 'First long field name', 13 SECOND_LONG_FIELD_NAME TEXT IS 'Second long field name' 14 ) ; 15 LABEL ON TABLE THIS_IS_A_LONG_FILE_NAME IS 'Long file name' ;

I am getting carried away here, but I am going to create an index:

16 CREATE UNIQUE INDEX MYLIB.THIS_IS_AN_INDEX 17 FOR SYSTEM NAME "LONGFILE1" 18 ON MYLIB.THIS_IS_A_LONG_FILE_NAME 19 (SECOND_LONG_FIELD_NAME,FIRST_LONG_FIELD_NAME) ; 20 LABEL ON INDEX THIS_IS_AN_INDEX IS 'Long file name by SECOND,FIRST' ;

Line 16: By using UNIQUE INDEX , rather than just INDEX , this index's key will be unique.

Line 17: Here I am giving my new index its short name.

Line 20: This is the object description for the index.

I am going hog wild here as I am going to define a view too.

21 CREATE VIEW MYLIB.LONG_VIEW_NAME 22 FOR SYSTEM NAME "LONGVIEW" 23 AS SELECT A.FIRST_LONG_FIELD_NAME, 24 A.SECOND_LONG_FIELD_NAME, 25 B.ANOTHER_SECOND 26 FROM THIS_IS_A_LONG_FILE_NAME A 27 CROSS JOIN 28 ANOTHER_FILE B 29 WHERE A.FIRST_LONG_FIELD_NAME = B.ANOTHER_FIRST ; 30 LABEL ON TABLE LONG_VIEW_NAME IS 'Long file name = Another file' ;

Line 22: This is where I am giving the short, system, name for my view.

This view joins my THIS_IS_A_LONG_FILE_NAME to another table, ANOTHER_FILE , to add the column ANOTHER_SECOND which contains the description of the value in FIRST_LONG_FIELD_NAME .

Line 30: This gives the object description for the view.

I know have three objects with meaningful short names:

Work with Objects Using PDM Library . . . . . MYLIB Opt Object Type Attribute Text LONGFILE *FILE PF-DTA Long file name LONGFILE1 *FILE LF Long file name by SECOND,FIRST LONGVIEW *FILE LF Long file name = Another file

And the field/column names now look like:

Field Beg End Lth Dec Fmt Description ---------- ----- ----- ---- --- --- ---------------------- FIRST 1 7 7 A First long field name SECOND 8 33 26 Z Second long field name

So how do I use all this good stuff in a RPG program with embedded SQL statements. In this very simple example I am just going to perform a multiple row fetch to get rows from the table and place the results into a data structure array.

01 **free 02 ctl-opt option(*nodebugio:*srcstmt:*nounref) ; 03 dcl-ds Data extname('LONGVIEW') alias qualified dim(10) 04 end-ds ; 05 dcl-s Rows int(5) ; 06 exec sql SET OPTION COMMIT=*NONE,CLOSQLCSR=*ENDMOD,NAMING=*SQL ; 07 Rows = %elem(Data) ; 08 clear Data ; 09 exec sql DECLARE C0 CURSOR FOR SELECT * FROM MYLIB.LONG_VIEW_NAME ORDER BY FIRST_LONG_FIELD_NAME DESC FOR READ ONLY ; 10 exec sql OPEN C0 ; 11 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 12 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 13 exec sql CLOSE C0 ; 14 *inlr = *on ;

Line 1: You all know that I am going to write this in totally free RPG.

Line 2: My favorite control options.

Lines 3 and 4: My definition of the data structure array I will place my SQL results into. The EXTNAME keyword defines the data structure to be the same as file name entered, as this is RPG I have to use the short name of the view I created. ALIAS means that the data structure subfields will be know by their alias, long, names.

Line 5: This variable will be used to contain the number of rows fetched by my SQL statement.

Line 6: I always set the SQL options in the RPG programs to make sure that the program is created the way I desire.

Line 7: I am initializing this variable with the number of elements in my data structure array. I will be using this later to limit the numbers of rows fetched into my data structure array.

Line 8: I always clear my data structure array before I use it.

Line 9: This is the definition of the cursor I will be using to get columns from the view I created. I want the results returned in descending order of FIRST_LONG_FIELD_NAME . And the cursor is only read only, not for updating or deleting.

Line 10: I open my cursor.

line 11: Here I attempt to fetch the same number of rows as I have elements in my data structure array into my data structure array.

Line 12: I am not going to use it in this program but I am going to retrieve the number of rows I fetched by using GET DIAGNOSTICS .

Line 13: I close the cursor.

When I compile this program as I have used ALIAS in the definition of the data structure I can see that the compiler and program will use the long names for the subfields.

dcl-ds Data extname('LONGVIEW') alias qualified dim(10) end-ds ; *-------------------------------------------------- * Data structure . . . : DATA * External format . . : LONGVIEW : MYLIB/LONGVIEW * Format text . . . . : FORMAT0001 *--------------------------------------------------

If I use debug and put a break point on line 14 of my RPG program I can view the contents of the data structure array and show that it is using the long subfield names.

EVAL data DATA.FIRST_LONG_FIELD_NAME(1) = 'HIJKLMN' DATA.SECOND_LONG_FIELD_NAME(1) = '2019-03-12-17.06.16.593880' DATA.ANOTHER_SECOND(1) = 'HERE IS MORE DATA ' DATA.FIRST_LONG_FIELD_NAME(2) = 'ABCDEFG' DATA.SECOND_LONG_FIELD_NAME(2) = '2018-12-12-17.06.16.593880' DATA.ANOTHER_SECOND(2) = 'HERE IS SOME DATA ' DATA.FIRST_LONG_FIELD_NAME(3) = ' ' DATA.SECOND_LONG_FIELD_NAME(3) = '0001-01-01-00.00.00.000000' DATA.ANOTHER_SECOND(3) = ' '

I know I have gone "above and beyond" the original question, but you can now see how to give your tables and columns short names, and how to use the long names in a SQL RPG program.

This article was written for IBM i 7.3, and should work for some earlier releases too.