Pages

Wednesday, May 23, 2018

How to SQL table's long names in RPG program

using long column names from sql table

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.

8 comments:

  1. Always very handy

    ReplyDelete
  2. Something to try...
    Instead of letting the system generate the short name, you can try the 'FOR' option in the DDL. This way you have the best of both worlds. And you get to keep the brain fresh by trying to come up with those short names that make sense. :)


    CREATE TABLE MYLIB.MYTABLE (
    FIRST_COLUMN For MyCol1 CHAR(3) NOT NULL,
    SECOND_COLUMN For MyCol2 VARCHAR(20),
    THIRD_COLUMN For MyCol3 DECIMAL(10,2),
    FOURTH_COLUMN For MyCol4 VARCHAR(50)
    ) ;

    ReplyDelete
    Replies
    1. That works very well for creating "good" short column names.

      I have mentioned what you described in various posts before, for example here.

      Delete
  3. This works great for column names, however, keep in mind your still going to run into a potential problem when creating a SQL long table name (alternative name beyond 10 characters). There is an issue when using the system commands CPYF, MOVOBJ and CRTDUPOBJ which is irrespective and separate from using long column names. When using these system commands we are restricted to the 10 character system name equivalents. This in and of itself is not necessarily and issue but it does represent an issue when using those commands to copy, move or create a duplicate object from system FileName_A to FileName_B within the SAME library. The commands will fail because the alternative name (SQL long name beyond 10 characters) automatically attempts to carry over to File_B and of course duplicate names within the SAME library are not allowed. If copying to a different library we don't have that issue because we can have duplicate file system names and duplicate alternative names as long as they each resides in a different schema and recognized as such ny the system cross reference files.

    There are ways around this issue but it's just something to be cognizant of when using those system commands where SQL alternative names are assigned to tables.

    ReplyDelete
    Replies
    1. Are you talking Table names? (Which can be more than ten long)

      As the long column name does not cause a problem if I use CRTDUPOBJ, MOVOBJ, or CPYF with the table/file in the example I gave above.

      Delete
  4. +1 on FOR 'system-name'. This adds great value to incremental modernization.

    Real example: A need arose to replace old-style packed date/time fields in a DDS PF with DATE/TIME data types. While at it why not convert it to a DB2 table?

    Solution: Refactor PF into a table whose name is the same (short). We could have used 'system-name' to yield a new long table name. We did not for reasons too numerous to discuss here.

    Determine long names for all existing columns, employing FOR 'system-name' to identify the short names. Add new columns for DATE/TIME values (leave the old ones in place). Add PRIMARY KEY (use the new long name). Add LABELs and COMMENTs. Create the table. Populate it with existing data. Create all the DDS LFs (including one JOIN LF). Recompile all referencing code objects. We now have our table.

    We identified code populating the old-style date/time fields and added *additional* code to populate the new columns. Subsequent refactoring will replace the old fields with new ones--eventually they can removed from the table entirely. Going forward new code can use the long names (via ALIAS keyword in RPG). Existing code can be refactored on your timetable and plan.

    This DDS PF had 1/2 dozen LFs with ~115 referencing code objects. ALL recompiled with no change required. I actually did not believe what the IBM i was telling me when I first saw this. "No way can RPG just compile all these programs. Something will break."--this is what I told myself. Each program I tested though--a report, 5250 maintenance program, service program, etc.--all worked. I tested less than 10 and decided to just go for it. No post-move issues arose.

    Naturally every data set (PF/LF) and the code it's married to are different. Some are easy to refactor. Some not so easy. Others are a nightmare. Some may never get done. YMMV.

    ReplyDelete
  5. I define both long and short column names and table names in the DDL. Then I can choose which I want to use in programming, which allows backwards compatibility for those programs I need to modernize the DB for, but not (yet) the program. BTW, seems to me that for DSPF's / PRTF's aliases work only when using REFFLD's in the DSPF/PRTF definitions, yes?

    ReplyDelete
    Replies
    1. Or you can use the ALIAS keyword in the DSPF & PRTF.

      Delete

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.