How to define a file twice for two members?

I was asked this question about reading a two members from a DDS file in a RPG:

I'm not understanding what the definition of 'FILE1' and 'FILE2' would be if i am defining the same file in order to use 2 different members. I get the external definitions for file and member, but how do i define the same file in the dcl-f spec?

This post will describe how I would do it.

First I need a file with two members. I start with a DDS file, with one field FIELD.

01 A R TESTFILER 02 A FIELD 30A

I compile file, and then run the following CL commands:

01 RMVM FILE(MYLIB/TESTFILE) MBR(*ALL) 02 CHGPF FILE(MYLIB/TESTFILE) MAXMBRS(*NOMAX) 03 ADDPFM FILE(MYLIB/TESTFILE) MBR(FIRST) TEXT('First member in TESTFILE') 04 ADDPFM FILE(MYLIB/TESTFILE) MBR(SECOND) TEXT('Second member in TESTFILE')

Line 1: I remove the existing member from the file.

Line 2: The file was created to only allow one member, therefore, I change the file to allow multiple members.

Line 3: Add a new member, which I have called FIRST.

Line 4: Add a second member, which I have called SECOND.

I can get a list of members in a file using the SYSMEMBERSTAT SQL View, and using the following statement:

01 SELECT SYSTEM_TABLE_NAME,SYSTEM_TABLE_MEMBER,TEXT_DESCRIPTION 02 FROM QSYS2.SYSMEMBERSTAT 03 WHERE TABLE_NAME = 'TESTFILE' 04 AND TABLE_SCHEMA = 'MYLIB'

Line 1: I just want the table name, member name, and member description in my results.

Lines 3 and 4: I just want the results for my file, TESTFILE, which resides in my library, MYLIB.

The results are:

SYSTEM_ SYSTEM_TABLE TABLE_NAME _MEMBER TEXT_DESCRIPTION ---------- ------------ -------------------------- TESTFILE FIRST First member in TESTFILE TESTFILE SECOND Second member in TESTFILE

I need data in these members to show how my program works:

First member	Second member
First member, first row First member, second row First member, third row	Second member, first row Second member, second row Second member, third row

I could use the Over Database File command, OVRDBF, override the two members into a way I could use them in my RPG program:

01 OVRDBF FILE(FIRST) TOFILE(*LIBL/TESTFILE) MBR(FIRST) 02 OVRDBF FILE(SECOND) TOFILE(*LIBL/TESTFILE) MBR(SECOND)

I do not like this approach as most people create CL program perform with the above overrides in them, and then call a RPG program. I have seen on several occasions where another programmer finds the RPG program, does not bother to look if it is called by a CL program which has overrides in it, and calls the RPG program without the overrides.

I could use the overrides in a RPG program by using the QCMDEXC API, but what I am going to show is easier than that.

Below is my RPG:

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-f FIRST extfile('TESTFILE') 04 extdesc('MYLIB/TESTFILE') 05 extmbr('FIRST') 06 rename(TESTFILER : FIRSTR) 07 prefix(a_) ; 08 dcl-f SECOND extfile('TESTFILE') 09 extdesc('MYLIB/TESTFILE') 10 extmbr('SECOND') 11 rename(TESTFILER : SECONDR) 12 prefix(b_) ; 13 dow (*on) ; 14 read FIRSTR ; 15 if (%eof) ; 16 leave ; 17 else ; 18 dsply a_FIELD ; 19 endif ; 20 enddo ; 21 dow (*on) ; 22 read SECONDR ; 23 if (%eof) ; 24 leave ; 25 else ; 26 dsply b_FIELD ; 27 endif ; 28 enddo ; 29 *inlr = *on ;

Lines 3 – 7: This is the definition for the "file" for the first member. I could call this "file" anything I liked, but I decided to name it after the member it defines. What follows are the keywords to do the same as the overrides would do:

• EXTFILE('TESTFILE'):  The program will open TESTFILE when the "file" FIRST is opened
• EXTDESC('MYLIB/TESTFILE'):  Use this file as the description of the file when the program is created
• EXTMBR('FIRST'):  This is the member that is opened when the "file" is opened
• RENAME(TESTFILER : FIRSTR):  I need to rename the record format as all the members in the same file have the same record format name
• PREFIX(A_):  The two members' fields have the same names. If I use the prefix I can differentiate between the fields from each member

Lines 8 – 12: The "file" name I have used for the second member is SECOND. Its definition is the same as that for FIRST.

Lines 13 – 20: This Do group reads all of the records from the record format FIRSTR, which is really member FIRST in TESTFILE.

Lines 21 – 28: This Do group does the same for the record format SECONDR, which is member SECOND in TESTFILE.

After creating this program, when I call it the following is displayed:

DSPLY First member, first row DSPLY First member, second row DSPLY First member, third row DSPLY Second member, first row DSPLY Second member, second row DSPLY Second member, third row

This shows that all the records are read from member FIRST before the records from member SECOND are read.

In 2024 we should be looking to use SQL for our file I/O, therefore, I wrote a second program using SQL for retrieving the data from the members:

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-ds FirstData extname('TESTFILE') qualified dim(*auto : 10) ; 04 end-ds ; 05 dcl-ds SecondData likeds(FirstData) dim(*auto : 10) ; 06 dcl-ds Single likeds(FirstData) ; 07 dcl-s Elements uns(3) inz(%elem(FirstData : *max)) ; 08 exec sql CREATE OR REPLACE ALIAS QTEMP.FIRST 09 FOR TESTFILE (FIRST) ; 10 exec sql CREATE OR REPLACE ALIAS QTEMP.SECOND 11 FOR TESTFILE (SECOND) ; 12 exec sql DECLARE C0 CURSOR FOR 13 SELECT * FROM QTEMP.FIRST FOR READ ONLY ; 14 exec sql OPEN C0 ; 15 exec sql FETCH C0 FOR :Elements ROWS INTO :FirstData ; 16 exec sql CLOSE C0 ; 17 exec sql DECLARE C1 CURSOR FOR 18 SELECT * FROM QTEMP.SECOND FOR READ ONLY ; 19 exec sql OPEN C1 ; 20 exec sql FETCH C1 FOR :Elements ROWS INTO :SecondData ; 21 exec sql CLOSE C1 ; 22 for-each Single in FirstData ; 23 dsply Single ; 24 endfor ; 25 for-each Single in SecondData ; 26 dsply Single ; 27 endfor ; 28 *inlr = *on ;

Lines 3 and 4: As I am going to be using multiple row fetches I need a data structure array for my results. The EXTNAME('TESTFILE') ensures that the data structure is defined to be the same as TESTFILE. I have made this an auto-expanding array with the DIM containing *AUTO, and the array will have a maximum of ten elements.

Line 5: The LIKEDS allows me to define this array to have the same subfields as the other. This is also an auto-extending array.

Line 6: This is just a data structure, not an array, using the LIKEDS so that it has the same layout as the first data structure array.

Line 7: I am defining a variable to contain the maximum number of elements that the array FirstData can have.

Lines 8 and 9: SQL does not have the concept of members, therefore, I need to create an Alias "pointing" to the file and member. Here the Alias FIRST was defined "over" file TESTFILE and member FIRST.

Lines 10 and 11: The same as above to create Alias SECOND "over" TESTFILE's member SECOND.

Lines 12 and 13: SQL Cursor definition to use the FIRST Alias.

Line 14: Open the cursor.

Line 15: Fetch the rows from the Alias into the FirstData data structure array.

Line 16: Close the cursor.

Lines 17 – 21: Repeat the above for the Alias SECOND, using its own cursor, into its own data structure array SecondData.

Lines 22 – 24: Use the FOR-EACH operation code to read all the elements from the FirstData array and display them using the DSPLY operation code.

Lines 25 – 27: Do the same for the elements in SecondData.

After compiling this program, I call it, and the following is displayed:

DSPLY First member, first row DSPLY First member, second row DSPLY First member, third row DSPLY Second member, first row DSPLY Second member, second row DSPLY Second member, third row

This output is identical to that of the first program.

 

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