Wednesday, January 16, 2019

Create SQL tables, views, and indexes with long names

create long and short names for sql tables, indexes, and views and their columns

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.

7 comments:

  1. Although using long file names can be helpful you need to be cognizant of issues you may encounter by doing so. Especially when using CPYF, MOVOBJ and CRTDUPOBJ. Here's a link to follow for some helpful information...
    https://www-01.ibm.com/support/docview.wss?uid=nas8N1017004

    ReplyDelete
  2. NIce article Simon. Any reason you surround the system name and the column short names with double-quotes? I've only done so if the names are DB2 SQL words...

    ReplyDelete
    Replies
    1. I don't remember the exact reason why. I just have done it since I started creating DDL tables.

      Thank you for making the point they are not needed, in most cases.

      Delete
    2. I have found that double quotes record the entry exactly as you type in the statement. For system names this is meaningless if you don't use all upper case values. But it works for SQL columns if you wanted to have mixed/lower case column names.

      If you do this the system stores the column name in the catalogs but without the double quotes. So "This is a Column" is stored in the catalog as This is a column. The problem comes in if you generally use the catalog to build objects from the system catalogs, etc.

      I only use the Double quoted SQL columns names for building export items like views and/or work tables. Then I don't need to supply column text and the exported item column name is more understandable.

      -Matt

      Delete
  3. Hello, Is there any way to handle DYNSLT file level keyword in SQL?

    ReplyDelete
  4. DYNSLT is used in DDS logical files, the equivalent is a SQL View. All Views use the equivalent of DYNSLT as they do not "contain" data until they are used.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.