Reading from multiple members in a CL program

It has been a while since I wrote a post about CL. I have been given an opportunity to do so again when I received this question:

If my file has 5 members and I want to read only first record of each member in CL how can we do that ?

Yes, this is entirely possible using CL, with no help from RPG.

Before I start showing CL code I am going to need a file, which you will not be surprised I called TESTFILE, and it is in my library, MYLIB. The DDS code for this file is:

01 A R TESTFILER 02 A TEXT 45A

I only need one field, which I have called TEXT, that I can then add some text into to show which member the data has come from.

I used the following CL commands to create the file and the five members:

01 CRTPF FILE(MYLIB/TESTFILE) 02 SRCFILE(MYLIB/DEVSRC) SRCMBR(TESTFILE) 03 MAXMBRS(*NOMAX) 04 SIZE(*NOMAX) REUSEDLT(*YES) 05 RMVM FILE(MYLIB/TESTFILE) MBR(*ALL) 06 ADDPFM FILE(MYLIB/TESTFILE) MBR(FIRST) 07 ADDPFM FILE(MYLIB/TESTFILE) MBR(SECOND) 08 ADDPFM FILE(MYLIB/TESTFILE) MBR(THIRD) 09 ADDPFM FILE(MYLIB/TESTFILE) MBR(FOURTH) 10 ADDPFM FILE(MYLIB/TESTFILE) MBR(FIFTH)

Line 3: I use the MAXMBRS parameter to allow the file to have more than one member.

Line 4: I have always used this parameters when I have created DDS files as I don't want to get message that the file is full, doesn't this message always happen in the middle of the night? And I want to reuse deleted records so I don't have to reorganize it to remove the deleted records.

Line 5: This command will remove all existing members from the file. In this scenario I did this so I could give the members the names I wanted.

Lines 6 – 10: Here I am adding new members to the file.

When the file and members were all created I added a record to each member. These records were:

Member name	Contents of TEXT field
FIRST	Data from the first member
SECOND	Data from the second member
THIRD	Data from the third member
FOURTH	Data from the fourth member
FIFTH	Data from the fifth member

The old fashioned way was to get a list of members in a file is to use the Display File Description command, DSPFD. I would use the following to generate an outfile with a list of the members in TESTFILE:

01 DSPFD FILE(MYLIB/TESTFILE) 02 TYPE(*MBRLIST) 03 OUTPUT(*OUTFILE) 04 FILEATR(*PF) 05 OUTFILE(QTEMP/OUTFILE)

I don't care about most of the fields in the DSPFD's output file. I just care about the following:

MLLIB MLFILE MLNAME MLNRCD MLCCEN MLCDAT ------ -------- ------ ------ ------ ------ MYLIB TESTFILE FIFTH 1 1 230822 MYLIB TESTFILE FIRST 1 1 230822 MYLIB TESTFILE FOURTH 1 1 230822 MYLIB TESTFILE SECOND 1 1 230822 MYLIB TESTFILE THIRD 1 1 230822

The output would include all members that are empty as well as those with records. This causes a wasted I/O for the empty members, and extra logic to check if there are records in the member. I know it is not much, but I like to minimize any unnecessary work in my programs.

If I wanted to sort the members by when they were created I have a problem. DSPFD's output only contains the create date, and not a create time. If I had more than one member created on the same date I would not be able to determine which was older.

The modern and better approach is to get the list of members using a SQL View, SYSPARTITIONSTAT. By using this View I can exclude any members that are empty and sort the results by the create timestamp, date and time. The only downside is that I cannot use a cursor in CL. I still need to create an output file from SYSPARTITIONSTAT, and then read it.

Now I am ready to show the CL codes for this program. Let me start with the definitions and "main part" of the program:

01 PGM 02 DCL VAR(&SQL) TYPE(*CHAR) LEN(320) 03 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1') 04 DCLF FILE(QTEMP/OUTFILE) OPNID(MBR) 05 DCLF FILE(MYLIB/TESTFILE) OPNID(DATA) 06 CALLSUBR SUBR(BLD_FILE) /*Make list of members*/ 07 CALLSUBR SUBR(READ_FILES) /*Read members in file*/

Line 4: This is the output file created from SYSPARTITIONSTAT's results. I need to use the OPNID as I will have more than one file open, and I need to distinguish which is which.

Line 5: This is the multi member file I created above.

Lines 6 and 7: IMHO one of the biggest improvements added to CL in the last decade has been subroutines! I have two here, each one will do "half" of the processing.

The first "half" is the part to make the output file of the members:

08 SUBR SUBR(BLD_FILE) 09 CHGVAR VAR(&SQL) VALUE('+ 10 CREATE OR REPLACE TABLE QTEMP.OUTFILE + 11 (LIBRARY,FILE,MEMBER) + 13 AS + 14 (SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,+ 15 SYSTEM_TABLE_MEMBER + 16 FROM QSYS2.SYSPARTITIONSTAT + 17 WHERE SYSTEM_TABLE_SCHEMA = ''MYLIB'' + 18 AND SYSTEM_TABLE_NAME = ''TESTFILE'' + 19 AND NUMBER_ROWS > 0 + 20 ORDER BY CREATE_TIMESTAMP) + 21 WITH DATA + 22 ON REPLACE DELETE ROWS') 23 RUNSQL SQL(&SQL) COMMIT(*NC) 24 ENDSUBR

When I use CL's Run SQL Statement command, RUNSQL, it is better for problem determination if I use a variable in the RUNSQL. If the program fails I can dump it and see the SQL statement I wanted it to execute.

Lines 9 – 22: This is me building the SQL statement I want to perform. It is a CREATE TABLE command that creates a new table from an existing one. I have used the CREATE OR REPLACE as if I need to run this more than once the existing Table is replaced with a new one.

Line 11: These are the names of the columns that the new file will contain.

Lines 14 – 20: The SQL statement that retrieves the results that are used to create the new table.

Lines 17 and 18: I have hard coded the name of the file and library that has the multiple members.

Line 19: I am only including any member that has more than zero records. This excludes all empty members.

Line 20: And I can sort the results by the create timestamp, therefore, the oldest member comes first.

Line 21: If I do not do this an empty file is created, and I want data.

Line 22: As I have used the OR REPLACE this line tells the SQL "compiler" what to do with any existing data in the output table. In this scenario I want any existing data deleted.

Line 23: I can now use the RUNSQL with the variable I created above. I also tell it not to use commitment control with this file.

When this subroutine has completed the table OUTFILE in QTEMP contains:

LIBRARY FILE MEMBER ------- -------- ------ MYLIB TESTFILE FIRST MYLIB TESTFILE SECOND MYLIB TESTFILE THIRD MYLIB TESTFILE FOURTH MYLIB TESTFILE FIFTH

Onto the second "half", the subroutine that uses the data created above to retrieve the first record from each of the members of the multi member file:

25 SUBR SUBR(READ_FILES) 26 DOWHILE COND(&LOOP) 27 RCVF OPNID(MBR) 28 MONMSG MSGID(CPF0864) EXEC(LEAVE) 29 OVRDBF FILE(TESTFILE) MBR(&MBR_MEMBER) + 30 POSITION(*START) OVRSCOPE(*CALLLVL) 31 RCVF OPNID(DATA) 32 MONMSG MSGID(CPF0864) 33 CLOSE OPNID(DATA) 34 SNDPGMMSG MSG(&DATA_TEXT) 35 ENDDO 36 ENDSUBR

Lines 26 – 35: I am using a Do while group to read all the rows from the list of members table.

Line 27: I am "reading" the next record from OUTFILE

Line 28: If end of file is encountered I leave the Do group.

Lines 29 and 30: I use the Override Database File command, OVRDBF, to override to the member I just got from OUTFILE. I want to position the file pointer to the start of that member's data, and I am overriding at the call (this program's) level.

Line 31: I then "read" the member.

Line 32: This line is to prevent a scenario that probably will never happen. If the member has been cleared in the interval between when I generated the output table and when it is read here I do not want the program to fail with an end of file error.

Line 33: The CLOSE command soft closes the file, which allows me to reopen it again. If I had used the CLOF I would not have been able to read any subsequent members in the manner I have done here.

Line 34: This line's sole purpose is to write a message to the program's message queue. It writes the contents of the field TEXT from the various members.

When the program has completed I look in the job log and I see:

Data from the first member Data from the second member Data from the third member Data from the fourth member Data from the fifth member End of file detected for file OUTFILE in QTEMP.

Mission accomplished, I have answered the original question.

 

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