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
Ways to display data of multiple members of multimember physical file in AS400,DSPPFM, Display physical file member,OVRDBF and SQL SELECT, override database file member
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, Display physical file member in as400, ibmi, multimember physical file in as400
DSPPFM-*FIRST member
The first member is empty.

DSPPFM, Display physical file member in AS400, multimember physical file in as400,ibmi
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, display physical file member, multimember physical file in AS400, multimember PF in as400, ibmi,iseries,systemi
DSPPFM - access MBR2

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

DSPPFM, Display physical file member in as400, multimember physical file in as400, multimember pf in as400, ibmi, iseries,systemi
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
SQL SQLECT, SQL, DB2, DB2 for i SQL, ibmi, as400,iseries,systemi
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, multimember physical file in as400, multimember pf in as400,ibmi
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, db2, SQL, slelect statement in SQL,ibmi,as400, multimember pf ,multimember physical file member in as400
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

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain