Working with Multi-Member Physical Files - IBM i

Multi-member Physical Files

At times, Multi-member physical files are very helpful in IBM i.

A physical file is a file that stores data in a specific format. A physical file can have multiple members, each of which is act as a separate data set within the file. 

This allows us to store different types of data (format of the file remains same) or data for different purposes within the same file.

Creating a Multi-member Physical File

Create a physical file (PF) with single member (default) and add an addition member after PF is created.

A physical file is created by using CRTPF command.

CRTPF FILE(<Library>/<File>) MBR(<Member Name>)

By default, Member is created with the same name as the file name. If a specific name is required, member name can be added as well. 

CRTPF FILE(LIBRARY/FILE) RCDLEN(10) MBR(MEMBER1) TEXT('Temporary File')

For the simplicity, I have created the flat file by directly mentioning the record length (RCDLEN) as 10. We would usually be creating physical file based on the DDS source. 

Once the physical file is created, additional members can be added to the PF by using ADDPFM command. 

ADDPFM FILE(LIBRARY/FILE) MBR(MEMBER2) TEXT('Additional Member')

One thing to note here is that, by default PF is created with maximum members as '1'. 

We should either set this to the number of members required or set to *NOMAX to allow adding any number of members. 

This can be done either at the time of PF creation (i.e., using CRTPF) or after the PF creation (using CHGPF). 

CHGPF FILE(LIBRARY/FILE) MAXMBRS(2)

DSPFD (Display File Description) command can be used to view the maximum number of members allowed and the current members of the file. 

Accessing data from Multi-member Physical File

There are different ways of accessing the data from multi-member physical file depending upon from where we are accessing the data. 

From Command line/CL Program

Data in multi-member PF can be viewed by running the commands like DSPPFM (Display Physical File Member) and RUNQRY (Run Query). 

• DSPPFM gives an option to enter the member name from which we need to view the data. 
• By default data from the first member is displayed (*FIRST against MBR keyword). 
• All records are displayed by default. We could specify the record number if we only need records from specific record number. 

• On the other hand RUNQRY gives more flexibility for the record selection by the values of the fields with in the PF. 
• By default data from the first member is displayed and can enter the member name as required. 
• Field names would be prompted on selecting '*YES' against Record selection. 

Above two commands are mostly used to view the data from command line and not for processing the data programmatically. 

Other way to access the data in a multi-member PF is we will need to Override the file to a specific member and use the Open Query File (OPNQRYF) command or the Open Data Path (ODP) API. 

This would allow specifying the member of the physical file that we need to access and the conditions for selecting the data.

E.g.: 

We could use the following OVRDBF & OPNQRYF commands to override and select all records from the MEMBER1 member of the MYFILE physical file. 

OVRDBF FILE(MYFILE) TOFILE(QTEMP/MYFILE) MBR(MEMBER1)

There are few other important keywords to OVRDBF allowing us to specify the override scope, if the open data path to be shared and few other options. 

OPNQRYF FILE((MYFILE MEMBER1))

This would allow the user to access the data from the specific file member using the file name directly. 

There are few other important keywords to OPNQRYF allowing the user to query the data and if the file is opening for read only or allows update and delete. 

If we need to filter the records where the value of the MYFIELD field is greater than 10,

OPNQRYF FILE((MYFILE MEMBER1)) QRYSLT('MYFIELD > 10')

This file could then be used in the further CL and/or RPGLE programs to read, update and/or delete the data from the specific member overridden. 

From RPGLE Program

To work with multi-member physical files in RPGLE, We can use the EXTMBR keyword against the file definition in F-Spec. 

This would open the specific member of the file for processing (i.e., read, write, update and/or delete) the data from the specific member through out the program.

FMYFILE     UF   E             K DISK   EXTMBR('MEMBER1')

These operations can be performed by using the RPGLE Op-codes READ, WRITE, UPDATE and/or DELETE just by specifying the file name next to the op-codes and no need to specify the member name every time. 

If we need to open multiple members of the file in the same program, there are couple of ways based on how we need to access the data. 

• By using the variable with EXTMBR keyword and defining the file in USROPN.
• This would require the member name to be populated in the variable and open the file. 
• When the processing with the member is completed, close the file, populate the next member and open the file. 
• If data from all the members is to be read, *ALL can be used with EXTMBR.
• If the member names are unknown, we could probably use DSPFD to dump the member list to OUTFILE and read the file in the program, use the member name from the OUTFILE in EXTMBR and open the file. 

• If we need to access the data from two different members at the same time.
• Define files with two different names in F-spec with EXTFILE pointing to the same file.
• Using EXTMBR to specify the corresponding member name. 
• Renaming the record format using RENAME keyword (as all the members of the PF would have same record format). 
• Prefix the field names using PREFIX keyword to identify the field names belonging to the corresponding member in the program. 

Another way is to override the file in CL program and declare the file directly. 

From SQL

Be it an interactive SQL session or Embedded SQL query in SQLRPGLE, SELECT doesn't allow defining the member name. 

If we need to query the specific member of the file, we could create an alias and query on the alias instead. 

CREATE ALIAS QTEMP/ALIAS1 FOR QTEMP/MYFILE (MEMBER1)

This alias can be used to run SELECT, INSERT, UPDATE and/or DELETE queries. 

SELECT * FROM QTEMP/ALIAS1

I hope this have given you an insight on how to work with multi-member physical files in IBM i.

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.