Create SQL tables, views, and indexes with long names

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 *-------------------------------------------------- <---------------------- Source Specifications ---- D FIRST_LONG_FIELD_NAME... D 7A D SECOND_LONG_FIELD_NAME... D 26Z D ANOTHER_SECOND 50A VARYING

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.