Using Alias for longer field names

---

 

Update – December 17, 2014

Thanks to a PTF there is now a much easier way to use the ALIAS names in your RPG programs than the way described below.

Read Using aliases can be simple.

 

---

The ALIAS keyword in DDS has been around for many, many years. I first used it in a programming environment where most of other the programmers used Cobol. They explained that if I added a ALIAS keyword to any files I created then they could use the longer alias name rather than the field name. I have to admit that the ability to use a field or variable name that was longer than six characters greatly appealed to me. This was the days of RPGIII and I had to deal with field names like J4UEEC for "End user currency", rather than the alias END-USER-CURRENCY that the Cobol programmers could use (you have to use the underscore in the alias name and the Cobol compiler translate it to a dash). The other thing I was jealous of the Cobol programmers was the Data division being the only place to define program variables.

I also found that if you used the Client Access "Data Transfer from System i" function to save an AS400/IBM i file to a Microsoft Excel file on your PC the alias names would be used as the worksheet's column headings. Which became another reason to define aliases in my files' DDS, even though I could not use them in my RPG programs.

RPG did get its equivalent of the Data division, as the Definition specification or D-spec, when RPGLE was launched. But the ability to use the alias in an RPG program came much later, in IBM i 7.1.

When coding DDS the ALIAS is just another keyword you can use, I have used included it in the source code for physical files:

A R TESTFILER A FLD001 10 ALIAS(FIRST_FIELD) A FLD002 3P 0 ALIAS(SECOND_FIELD) A FLD003 10 ALIAS(THIRD_FIELD) A FLD004 5P 0 ALIAS(FOURTH_FIELD) A K FLD001

And you can do the same in the source code for logical files too:

A R TESTFILELR PFILE(TESTFILE) A FLD001 ALIAS(FIELD_1) A FLD002 ALIAS(FIELD_2) A FLD003 ALIAS(FIELD_3) A FLD004 ALIAS(FIELD_4) A K FLD002 A K FLD001

Below is a small program I created to show how to use the alias names rather than the files' field names. It also demonstrates what I think is one of the cool new features of the all free RPG, that I can mingle definition specifications.

01 dcl-f TESTFILE usage(*input) alias qualified ; 02 dcl-ds PhyFile likerec(TESTFILE.TESTFILER) ; 03 dcl-f TESTFILEL usage(*input) alias qualified ; 04 dcl-ds LglFile likerec(TESTFILEL.TESTFILELR:*input) ; 05 dcl-ds Another extname('TESTFILE') alias qualified end-ds ; 06 read TESTFILE PhyFile ; 07 read TESTFILEL LglFile ; 08 Another = PhyFile ; 09 *inlr = *on ;

When I define the file TESTFILE with the DCL-F, line 1, I have to use the ALIAS and QUALIFIED keywords. Then on the line below I define a data structure, called PhyFile, with DCL-DS with the LIKEREC keyword. The LIKEREC defines the sub-fields in the data structure to be the same as the fields in the record format given, in this case the record format TESTFILER in the file TESTFILE. As I am only using this file for input I do not have to give the types of fields to be included as the default is *INPUT. As the LIKREC is not the subject for this post I will direct you to the appropriate part of IBM's documentation, which you will find near the bottom of this post.

I do the same on lines 3 and 4 for the logical file TESTFILEL and a data structure called LglFile. But this time I am using the *INPUT value in the LIKEREC, even though it is not necessary.

On line 5 I am showing how you can define a data structure, Another, which will allow me to use alias names rather field names for the fields define in TESTFILE.

Lines 6 and 7 show that when I read the two files, TESTFILE and TESTFILEL, I need to read them into the two data structures I defined on lines 2 and 4.

On line 8 I move the contents of the data structure PhyFile into the data structure Another. I put a debug break on line 9 so I could display the sub-field names of the data structures and what values were held in them:

> EVAL phyfile PHYFILE.FIRST_FIELD = 'first---->' PHYFILE.SECOND_FIELD = 222. PHYFILE.THIRD_FIELD = 'third---->' PHYFILE.FOURTH_FIELD = 44444. > EVAL lglfile LGLFILE.FIELD_1 = 'first---->' LGLFILE.FIELD_2 = 222. LGLFILE.FIELD_3 = 'third---->' LGLFILE.FIELD_4 = 44444. > EVAL another ANOTHER.FIRST_FIELD = 'first---->' ANOTHER.SECOND_FIELD = 222. ANOTHER.THIRD_FIELD = 'third---->' ANOTHER.FOURTH_FIELD = 44444.

Below is the pre-all free RPG equivalent of the program above:

01 FTESTFILE IF E K DISK alias qualified 02 FTESTFILEL IF E K DISK alias qualified 03 D PhyFile DS likerec(TESTFILE.TESTFILER) 04 D LglFile DS likerec(TESTFILEL.TESTFILE1R:*input) 05 D Another E DS extname('TESTFILE') alias qualified /free 06 read TESTFILE PhyFile ; 07 read TESTFILEL LglFile ; 08 Another = PhyFile ; 09 *inlr = *on ;

There is not an alias keyword in SQL DDL tables, but you can still use longer names. For example:

CREATE TABLE MYLIB/TESTTABLE (FIRST_COLUMN CHAR(10) NOT NULL, SECOND_COLUMN DECIMAL(3,0), THIRD_COLUMN CHAR(10), FOURTH_COLUMN DECIMAL(5,2)) RCDFMT TESTTABLER ;

When I use the 'Display File Field Description', DSPFFD, you can see that the longer names are the Alternative names, and SQL has chopped them to create the field names:

Data Field Field Type Length FIRST00001 CHAR 10 Alternative name . . . . FIRST_COLUMN SECON00001 PACKED 3 0 Alternative name . . . . SECOND_COLUMN THIRD00001 CHAR 10 Alternative name . . . . THIRD_COLUMN FOURT00001 PACKED 5 0 Alternative name . . . . FOURTH_COLUMN

When you create a SQL view you can set the name of the field and the alternate name:

CREATE VIEW MYLIB/TESTVIEW (FIELD_NUMBER_ONE FOR "FLD001", FIELD_NUMBER_TWO FOR "FLD002", FIELD_NUMBER_THREE FOR "FLD003", FIELD_NUMBER_FOUR FOR "FLD004", FIELD_NUMBER_FIVE FOR "FLD012", FIELD_NUMBER_SIX FOR "FLD013") AS SELECT A.FLD001,A.FLD002,A.FLD003,A.FLD004, B.FLD012,B.FLD013 FROM TESTFILE A,TESTFILE2 B WHERE A.FLD001 = B.FLD010 Data Field Field Type Length FLD001 CHAR 10 Alternative name . . . . FIELD_NUMBER_ONE FLD002 PACKED 3 0 Alternative name . . . . FIELD_NUMBER_TWO FLD003 CHAR 10 Alternative name . . . . FIELD_NUMBER_THREE FLD004 PACKED 5 0 Alternative name . . . . FIELD_NUMBER_FOUR FLD012 CHAR 10 Alternative name . . . . FIELD_NUMBER_FIVE FLD013 PACKED 5 0 Alternative name . . . . FIELD_NUMBER_SIX

The example below is a SQLRPGLE program I wrote:

01 dcl-ds ViewData extname('TESTVIEW') alias qualified end-ds ; 02 dcl-s Count int(3) ; 03 exec sql SELECT * INTO :ViewData FROM TESTVIEW ; 04 exec sql SELECT COUNT(SECOND_COLUMN) INTO :Count FROM TESTTABLE WHERE FIRST_COLUMN = '1' ; 05 *inlr = *on ;

On line 1 I have created a data structure, ViewData, with the alias keyword. When the SELECT statement on line 3 is executed all of the values are moved into the data structure. Now I can use the long names for those columns/sub-fields.

Line 4 shows how I can do a count of the number of records that contain a value using the long names.

As I did in my previous program I put a debug break on line 5 so I could display the sub-field names of the data structure and what values are held:

> EVAL viewdata VIEWDATA.FIELD_NUMBER_ONE = 'first---->' VIEWDATA.FIELD_NUMBER_TWO = 222. VIEWDATA.FIELD_NUMBER_THREE = 'third----> VIEWDATA.FIELD_NUMBER_FOUR = 44444. VIEWDATA.FIELD_NUMBER_FIVE = '12------->' VIEWDATA.FIELD_NUMBER_SIX = 00013. > EVAL count COUNT = 1

Using the examples I can now do what those Cobol programmer were doing all those years ago, but in RPG.

IBM i 7.1 TR9 and 7.2 TR1 offer an easier way to use the ALIAS without using data structures. I am not able to test this as the IBM i servers I use do not have these PTFs. According to the documentation you can just use the ALIAS keyword in the file declaration like this to use the alias names:

01 dcl-f TESTFILE alias ; 02 read TESTFILER ; 03 if (%eof) ; . . 04 elseif (SECOND_FIELD = '222') ;

 

You can learn more about these on the IBM website:

• DDS keyword ALIAS
• RPG definition keyword LIKEREC

 

This article was written for IBM i 7.2, and it should work with 7.1 too.