How to create a logical file with data from more than one member

I wrote an earlier post about how to define a file twice in a RPG program so that I can use two members in the file. This led to a further discussion about could I combine data from those multiple members into one using a DDS logical file. This post demonstrates the results of those subsequent discussions.

I am going to use the same file as I did in the earlier post. If you have questions about file layouts, etc., check that post.

I am going to add a third member to the file, which will be called THIRD:

ADDPFM FILE(TESTFILE) MBR(THIRD) TEXT('Just added')

I can list the members in TESTFILE using the SYSMEMBERSTAT SQL View:

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

The results show that the file now has three members:

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

An empty member will not do, therefore, I added three records to the new member. Rather than create a SQL Alias to the member I am just going to use the Run Query command, RUNQRY, to show the records in the new member.

01 RUNQRY QRY(*NONE) QRYFILE((TESTFILE THIRD))

Which returns:

FIELD ------------------------------ First record in Third member Second record in Third member Third record in Third member

I have the data in place, I created the DDS logical file, which I called TESTFILE0. This is the DDS code for this LF:

01 A R TESTFILER PFILE(TESTFILE) 02 A K FIELD

In this first example created the LF using the following command string:

CRTLF FILE(MYLIB/TESTFILE0) SRCFILE(DEVSRC) MBR(*FILE) DTAMBRS(*ALL)

The important parameter is the DTAMBRS, Physical file data members, parameter. The value *ALL will mean that all of the data from all of the members are included in the LF. The regular readers will notice that my source file is DEVSRC, as I only use one source file for all my source members regardless of type.

This is shown if I use the following SQL Select statement over the new LF:

01 SELECT * FROM TESTFILE0

Which returns the following results:

FIELD ------------------------------- First record in First member Second record in First member Third record in First member First record in Second member Second record in Second member Third record in Second record First record in Third member Second record in Third member Third record in Third member

If I add a fourth member to the file the LF does not include it. I have to delete the existing LF and recreate it using the Create statement I showed above.

What if I only want to include data from two of the three members? FIRST and THIRD.

I had to delete the existing LF and create it again:

01 DLTF TESTFLE0 02 CRTLF FILE(MYLIB/TESTFILE0) SRCFILE(DEVSRC) MBR(*FILE) DTAMBRS((TESTFILE (FIRST THIRD))

Line 2: Here in the DTAMBRS I gave the name of the file and the members I wish to include.

I can now check that the LF only contains data from the two members using:

01 SELECT * FROM MYLIB.TESTFILE0

This returns:

FIELD ------------------------------- First record in First member Second record in First member Third record in First member First record in Third member Second record in Third member Third record in Third member

Which shows that it does just contain data from member FIRST and THIRD.

When I use SQL or RUNQRY to return data from the LF the data is returned in "arrival sequence". If I use a RPG program to read the file the records are returned in keyed order. The source code for my very simple RPG program is:

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-f TESTFILE0 keyed ; 04 dow (*on) ; 05 read TESTFILER ; 06 if (%eof) ; 07 leave ; 08 endif ; 09 dsply FIELD ; 10 enddo ; 11 *inlr = *on ;

Line 3: The input file is defined as being keyed.

Lines 4 – 10: This Do loop will read every record in the file and use the DSPLY operation code to display the record read.

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

DSPLY First record in First member DSPLY First record in Third member DSPLY Second record in First member DSPLY Second record in Third member DSPLY Third record in First member DSPLY Third record in Third member

Which shows the results in keyed order.

Having a LF built over the members you desire offers an alternative to defining members as separate file definitions in your RPG program.

 

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