Display File Information for Physical Files

Does anyone know an easy way to list all physical files in a library programmatically?  Then for each of those physical files, I would like to show the Total Number of Records and the Total Number of Deleted Records for that Physical file.  I would also like to show the "Reuse Deleted Records" field.
Is there an easy way to do this? I would really like to do this in a CLP if possible.

Any help would be greatly appreciated.

Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hard to be sure what "easy way" means to you. Also, without knowing what your OS version is, it's hard to know what's useful to you.

In currently supported releases, something like this could be "easy":

   dcl   &myLib       *char    10     value( 'mylib' )

   dspfd       &myLib/*ALL  type( *MBR ) output( *OUTFILE ) +
                 fileatr( *PF ) outfile( QTEMP/PFRECS )
   dspfd       &myLib/*ALL  type( *ATR) output( *OUTFILE ) +
                 fileatr( *PF ) outfile( QTEMP/PFATRS )

              FROM qtemp/pfatrs join qtemp/pfrecs +
                   on PHFILE = MBFILE'

   runqry *n QTEMP/PFV




Open in new window

I made a couple minor edits after testing, especially be cause I couldn't know what library you needed. There might be one or two resulting syntax errors because of that.

Also, the &myLib variable needs to be set to a proper value. You might choose to make it a PGM PARM() variable instead or have some other way of getting a value set for it, rather than hard-coding the name. If so, you might want to put some simple validation code in to verify that it's a valid library name.

Also, if there are any PF source files in the library, you might want to exclude them from the view. The 'Reuse deleted' attribute won't apply to them.

Also, the 'Reuse deleted' attribute is an attribute of the *FILE object; but the various record counts are attributes of individual members. That's why there are two DSPFDs and their outputs are JOINed together. If any files have multiple members, you should see multiple rows for that file.

Post back if problems show up that you can't work through.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
Thanks for the help. This is EXACTLY what I was looking for.  

One final question: Is there a way to put the query output out to a file in the same format that it shows on the display? I attempted to put it to an OUTFILE - but couldn't get the output to look like the report that is displayed.

Thanks a lot for your help!
MurpheyApplication ConsultantCommented:
Sure you can,

Type RUNQRY on the commandline and press F4 for all options:

So at the end, the command will look like this:
       OUTFILE(MyLib/MyTabel *FIRST *ADDMBR)

instead of the *ADDMBR parameter you have the following options :

MurpheyApplication ConsultantCommented:
To list all files per library, you can use the QSYS/QADB*  files e.g.

Although QSYS/QADB* can be used, best is to use the various LFs built over the PFs rather than referencing PFs such as QADBIFLD directly. It's certainly rare, but DB2 is constantly using those files and establishing non-DB2 locks may bring some interference.

Ideally, use the SQL views in the system database catalog whenever possible. Those are provided for more general use.

As for putting to an *OUTFILE, using RUNQRY over the view is probably as good as anything. But since the info is already in a file in that format, it's not clear why it's necessary though. The use of QTEMP is just for an example. By creating the objects in a user library, the main file and the view can be kept and accessed as much as needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.