Skip to main content

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.

Comments

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i