How to write data to a multiple member file

Often I get the same question asked by several people, this time I have been asked the same question five times just this week, and I have been told that this is a question people have been asked in interviews:

How do you write data to different members in the same file using RPGLE?
The file has ten members, and you need to write data to the fifth and sixth members in one RPGLE program.

Before I get started I want to state my thoughts on multiple member files. Just because you can do something is not always the reason why you should. In my opinion data files should contain just one member. If you have multiple companies, branches, entities, etc they can easily be identified by company, branch, and entity code fields. I have worked with applications where companies' data was separated into different members, while it was not impossible to share data across all companies it was more difficult than it needs to be. So, one member per data file.

Here is my example file, TESTFILE, with its ten members, MBR01 - MBR10:

Work with Members Using PDM File . . . . . . TESTFILE Library . . . . MYLIB Opt Member MBR01 MBR02 MBR03 MBR04 MBR05 MBR06 MBR07 MBR08 MBR09 MBR10

I quickly came up with three ways to only write data to the fifth and sixth members. There may be others ways, but I am going to describe:

1. OVRDBF the file to a member, and then call RPGLE program
2. Define the member in the files' definition
3. Use SQL, the question did not say I could not use SQL in the RPGLE program

 

OVRDBF the file to a member

This is the most common way I have seen people coping with multiple members. They start with a CL program that uses the Override Database File command, OVRDBF, to override to specific member names.

01 PGM 02 OVRDBF FILE(TESTFILEM5) TOFILE(TESTFILE) + MBR(MBR05) OVRSCOPE(*CALLLVL) 03 OVRDBF FILE(TESTFILEM6) TOFILE(TESTFILE) + MBR(MBR06) OVRSCOPE(*CALLLVL) 04 CALL PGM(TESTRPG) 05 ENDPGM

Line 2: I am overriding the file name TESTFILEM5 to file TESTFILE's MBR05. I have used the override scope *CALLLVL, which means that only programs called by this one will have the override.

Line 3: As I cannot have two files in a RPG program with the same name I am overriding the file name TESTFILEM6 to the sixth member in TESTFILE.

Line 4: I call my RPGLE program.

01 **free 02 dcl-f TESTFILEM5 usage(*output) extdesc('TESTFILE') rename(TESTFILER:MBR05R) prefix(m5_) ; 03 dcl-f TESTFILEM6 usage(*output) extdesc('TESTFILE') rename(TESTFILER:MBR06R) prefix(m6_) ; 04 m5_F1 = '1' ; 05 m5_F2 = '5th member' ; 06 write MBR05R ; 07 m6_F1 = '1' ; 08 m6_F2 = '6th member' ; 09 write MBR06R ; 10 *inlr = *on ;

Line 1: If am writing RPG you know it is going to be totally free format.

Lines 2 and 3: Here I am defining my two "files" that were "created" by the OVRDBF commands in the CL program. They are both defined for output only using the USAGE keyword. The EXTDESC tells the RPG compiler to use the file TESTFILE for the file's definition when compiling, I do this as we know that TESTFILEM5 and TESTFILEM6 only "exist" after the OVRDBF commands have been executed. I use the RENAME keyword record formats of the two files as they cannot have the same name, and to make the member names more descriptive of the file they belong to. I am using the PREFIX keyword to make the fields from each file have unique names, if I did not the fields from both of the files would have the same names which could lead to confusion. If you want to learn more about these keywords see the post Useful keywords for file definitions.

Lines 4 – 6: This section of code is very simple, move values to the fields and write to the record format for the fifth member.

Lines 7 – 9: Does the same, but to the record format for the sixth member.

If I had to define the files in fixed format RPG they would look like:

02 FTESTFILEM5O E DISK extdesc('TESTFILE') F rename(TESTFILER:MBR05R) F prefix(m5_) 03 FTESTFILEM6O E DISK extdesc('TESTFILE') 04 F rename(TESTFILER:MBR06R) 05 F prefix(m6_) /free

The keywords are all the same, just the format of the definition is different.

 

Define the member in the files' definition

There is a keyword EXTMBR that allows me to define the member name to open when the file is opened. If I use this approach there is no need for a CL program, the RPG program can be called directly.

This program is similar to the previous one, except…

01 **free 02 dcl-f TESTFILEM5 usage(*output) extfile('TESTFILE') extmbr('MBR05') extdesc('TESTFILE') rename(TESTFILER:MBR05R) prefix(m5_) ; 03 dcl-f TESTFILEM6 usage(*output) extfile('TESTFILE') extmbr('MBR06') extdesc('TESTFILE') rename(TESTFILER:MBR06R) prefix(m6_) ; 04 m5_F1 = '2' ; 05 m5_F2 = '5th member' ; 06 write MBR05R ; 07 m6_F1 = '2' ; 08 m6_F2 = '6th member' ; 09 write MBR06R ; 10 *inlr = *on ;

Lines 2 and 3: If you notice that are two keywords in the definition that were not in the previous example:

• EXTFILE - External file, this is the external (system) name of the file to open. As I cannot have the same file open more than once in the RPG program I have used the file names TESTFILEM5 and TESTFILEM6 to be the internal names of the files, even though they are really the same file, TESTFILE.
• EXTMBR - Name of the member to open when the file is opened.

Lines 4 – 10: Same as previous example program.

If I had to define the files in fixed format they would look like:

02 FTESTFILEM5O E DISK extdesc('TESTFILE' F extfile('TESTFILE') F extmbr('MBR05') F rename(TESTFILER:MBR05R) F prefix(m5_) 03 FTESTFILEM6O E DISK extdesc('TESTFILE') F extfile('TESTFILE') F extmbr('MBR06') F rename(TESTFILER:MBR06R) F prefix(m6_) /free

 

Use SQL

The question did not say that I could not use SQL within my RPGLE program. I cannot stipulate the member I want to use in the common SQL statements. If I have a multi member file DB2 of i allows me to create an Alias for that member using the CREATE ALIAS statement. Once the Alias is created I can use it as I would any other table or file.

01 exec sql SET OPTION COMMIT = *NONE ; 02 exec sql CREATE ALIAS QTEMP.TESTFILE_MBR05 FOR TESTFILE(MBR05) ; 03 exec sql INSERT INTO QTEMP.TESTFILE_MBR05 VALUES('3','5th member') ; 04 exec sql DROP ALIAS QTEMP.TESTFILE_MBR05 ; 05 exec sql CREATE ALIAS QTEMP.TESTFILE_MBR06 FOR TESTFILE(MBR06) ; 06 exec sql INSERT INTO QTEMP.TESTFILE_MBR06 VALUES('3','6th member') ; 07 exec sql DROP ALIAS QTEMP.TESTFILE_MBR06 ; 08 *inlr = *on ;

Line 1: I am using the SET OPTION statement to ensure that this program does not use commitment control.

Line 2: Here I define my Alias. I always define my Aliases as <file name>_<member name> as I think it is leaves no confusion which file and member this Alias is built for. I also define them in QTEMP, so that this Alias is only for this job.

Line 3: This is just a regular SQL Insert statement using the Alias I just created.

Line 4: Once I am done with the Alias I always drop (delete) them.

Lines 5 – 7: Are just the same as the lines 2 – 4 but for the sixth member.

 

After all three programs have run if I look in MBR05 and MBR06 I see the records I wrote/inserted into them:

F1 F2 000001 1 5th member 000002 2 5th member 000003 3 5th member

F1 F2 000001 1 6th member 000002 2 6th member 000003 3 6th member

 

If you are on an interview and you asked this question now you can offer these three solutions to the interviewer to show that you know more than one way to handle multi member files.

 

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