Wednesday, December 20, 2017

Using alias for data structure subfields

externally described data structures using alias subfield names

Have you ever used a file or table to externally define a data structure, and then wondered how to use the alias field/column names rather than the short ones?

Fortunately it is simple to do. But, before I start with showing the file and table I will be using in these examples. Let me start with the file, TESTFILE:

A          R TESTFILER
A            F001           7A         ALIAS(FIRST_FIELD)
A            F002           7P 2       ALIAS(SECOND_FIELD)
A            F003            L         ALIAS(DATE_FIELD)
A            F004            T         ALIAS(TIME_FIELD)

Defining an alias in a DDS file is straightforward, I just use the ALIAS keyword.

My example DDL table I have deliberately made very simple:

CREATE OR REPLACE TABLE MYLIB.TESTTABLE (
COLUMN_1 FOR "C001" CHAR(7),
COLUMN_2 FOR "C002" DECIMAL(7,2),
DATE_COLUMN FOR "C003" DATE,
TIME_COLUMN FOR "C004" TIME
) ;

The short names will be used by RPG programs, and the (long) column names become the aliases.

Now I have shown the file and table this is a typical RPG showing how I use them both to define data structures.

01  **free
02  ctl-opt option(*srcstmt) alwnull(*usrctl) ;

03  dcl-ds Data1 extname('TESTFILE') end-ds ;

04  dcl-ds Data2 extname('TESTTABLE') qualified dim(99) ;
05  end-ds ;

06  exec sql SET OPTION DATFMT = *ISO, TIMFMT = *ISO ;

07  *inlr = *on ;

Line 1: I now only program in totally free RPG.

Line 2: I have used two control options. The first ensures that the program internal lines numbers will match the source sequence numbers. The second allows for me to control the handling of null within my program. I have to able to handle null as my table could return null values.

Line 3: The first data structure, Data1, is defined using the file, TESTFILE. Notice that the END-DS is on the same line as the rest of the data structure definition. I just wanted to show that this was possible, even though I prefer to have the END-DS on its own line.

Line 4 and 5 : The data structure Data2 is defined using the table, TESTTABLE. As line 4 ends with a semicolon the END-DS must be on its own line.

Line 6: As the TESTTABLE contains a date column and a time column I need to define what format the date and time are in. I use the SET OPTION statement to do this. Failure to do this can make Data2 unusable to receive results from a SQL Fetch statement.

When I compile the program and I look in the source listing I can see that the fields/columns from the file and table have been included as subfield in the data structures, but the short names have been used.

dcl-ds Data1 extname('TESTFILE') end-ds ;
 *------------------------------------------------------
 * Data structure . . . . . . :  DATA1
 * External format  . . . . . :  EG007R : MYLIB/TESTFILE
 *------------------------------------------------------
<---------------------- Source Specifications ----------
....1....+....2....+....3....+....4....+....5....+....6.
D F001                           7A
D F002                           7P 2
D F003                          10D   DATFMT (*ISO-)
D F004                           8T   TIMFMT (*ISO.)


dcl-ds Data2 extname('TESTTABLE') qualified dim(99) ;
end-ds ;
 *----------------------------------------------------------
 * Data structure . . . . . . :  DATA2
 * External format  . . . . . :  TESTTABLE : MYLIB/TESTTABLE
 *----------------------------------------------------------
<---------------------- Source Specifications --------------
....1....+....2....+....3....+....4....+....5....+....6....+
D C001                           7A
D C002                           7P 2
D C003                          10D   DATFMT (*ISO-)
D C004                           8T   TIMFMT (*ISO.)

To use the long names all I have to do is to use the ALIAS in the data structure definitions, see lines 3 and 4, below.

01  **free
02  ctl-opt option(*srcstmt) alwnull(*usrctl) ;

03  dcl-ds Data1 extname('TESTFILE') alias end-ds ;

04  dcl-ds Data2 extname('TESTTABLE') qualified alias dim(99) ;
05  end-ds ;

06  exec sql SET OPTION DATFMT = *ISO, TIMFMT = *ISO ;

07  *inlr = *on ;

Now when I compile the program and look at the compile listing I can see that the aliases have been used.

dcl-ds Data1 extname('TESTFILE') alias end-ds ;
 *------------------------------------------------------
 * Data structure . . . . . . :  DATA1
 * External format  . . . . . :  EG007R : MYLIB/TESTFILE
 *------------------------------------------------------
<---------------------- Source Specifications ----------
....1....+....2....+....3....+....4....+....5....+....6.
D FIRST_FIELD                    7A
D SECOND_FIELD                   7P 2
D DATE_FIELD                    10D   DATFMT (*ISO-)
D TIME_FIELD                     8T   TIMFMT (*ISO.)


dcl-ds Data2 extname('TESTTABLE') qualified alias dim(99) ;
end-ds ;
 *----------------------------------------------------------
 * Data structure . . . . . . :  DATA2
 * External format  . . . . . :  TESTTABLE : MYLIB/TESTTABLE
 *----------------------------------------------------------
<---------------------- Source Specifications --------------
....1....+....2....+....3....+....4....+....5....+....6....+
D COLUMN_1                       7A
D COLUMN_2                       7P 2
D DATE_COLUMN                   10D   DATFMT (*ISO-)
D TIME_COLUMN                    8T   TIMFMT (*ISO.)

Now I must the alias names for the subfields in the program, as the short names have not been defined.

 

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

2 comments:

  1. Good info for data sets...the future

    ReplyDelete
  2. Dear Simon thank you for your great example posted here!
    I successfully implemented it for my program querying the System Tables. Usually I read the articles from your site like chapters from Bible

    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.