|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 - 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.
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.
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|
After this one must delete the overrides on file using the command
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