Ways to display data of multiple members of multimember physical file in AS400

Ways to display data of multiple members of multimember physical file in AS400

Let's take a look at how to display or view data of multiple members of a file

We can view the data in multiple members of a physical file either using CL command DSPPFM(Display physical file member) or using the SQL approach

.

DSPPFM(Display Physical File member)

The DSPPFM (Display physical file member) displays the records for a particular member of a database physical file. Records are displayed in the sequence they inserted even if the physical file has the keyed access path specified

Display *FIRST member of a physical file using DSPPFM

The below CL command will access the *FIRST member of a file WEB_3

DSPPFM FILE(EASYCLASS1/WEB_3)

DSPPFM-*FIRST member

The first member is empty.

DSPPFM - Records

Display any member of a physical file using DSPPFM

In this blog above we inserted records in MBR2 of file WEB_3 in library EASYCLASS1. So let's display records from second member MBR2.

The below CL command will display the records from member MBR2 of file WEB_3

DSPPFM FILE(EASYCLASS1/WEB_3) MBR(MBR2)

DSPPFM - access MBR2

The member MBR2 has 1 record which can be seen in the below screenshot.

DSPPFM -records

Using OVRDBF and SQL SELECT to display records in multiple members of a physical file

If you run the below SQL SELECT statement from the STRSQL session. You will see no data displayed since by default *FIRST member(WEB_3) records displayed while doing SQL SELECT

SELECT * FROM WEB_3

SELECT - result

OVRDBF (Override database file)

Run the OVRDBF command to override file WEB_3 to its file member MBR2

OVRDBF FILE(WEB_3) TOFILE(WEB_3) MBR(MBR2) OVRSCOPE(*CALLLVL)

Just run CL command WRKJOB from the command line and take option 15 (File overrides). You will notice override on file WEB_3 to member MBR2.

OVRDBF-file overrides

After the file is overridden perform SQL SELECT from the STRSQL session to see the overridden member data.

SQL SELECT from STRSQL

Run the STRSQL command to start the SQL session and run the below SQL statement

SELECT * FROM WEB_3

SQL SELECT from STRSQL

The record displayed is from file WEB_3 and second member MBR2

Warning!
After this one must delete the overrides on file using the commandDLTOVR FILE(WEB_3)

Using SQL to add records in multiple members of a physical file

You will find the blog here for Using SQL with Multimember physical file in AS400. Working with multimember physical files using SQL

Related Post

Read also :

• Multimember physical file in AS400
• Ways to insert records in multiple members of a multimember physical file in AS400
• Copy, Clear and Remove member in multimember physical file in AS400