How to get the count of records in all files in all libraries in an environment in AS400?

70 pts.
Tags:
AS/400
jde
Record Count
How to get the count of records in all files in all libraries in an environment in AS400? Is there any command that could be used instead of writing SQL queries over each file to get its record count?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.
run a command like this

DSPFD FILE(*ALL/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
OUTFILE(QTEMP/FILEINFO)                                                             

the query the resulting file “FILEINFO” and sum the field “MLNRCD” to get total of all records in all PF members you have authority to under your user profile.

select sum(mlnrcd) from qtemp/fileinfo2

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Jailakshmi
    Thanks a lot!!! This really helped!!
    70 pointsBadges:
    report
  • Jailakshmi
    When I run the command DSPFD FILE(*ALL/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO), I am getting a message "Waiting for reply to message on message queue QSYSOPR". How do I clear this message?    
    70 pointsBadges:
    report
  • TheRealRaven
    @Jailakshmi : It's a bad idea to use DSPFD FILE(*ALL/*ALL). Use FILE(*ALLUSR/*ALL) instead. Some system files in some libraries, e.g., in library QSPL, should be left alone.

    You can possibly run DSPMSG QSYSOPR to access the QSYSOPR *MSGQ. However, if you're not familiar with how the system uses and relies upon QSYSOPR (or if you're not authorized to it), you should ask an operator at your site.
    34,320 pointsBadges:
    report
  • TheRealRaven
    Also, for "how to clear" the message, we need to know the message ID to be sure.
    34,320 pointsBadges:
    report
  • Jailakshmi
    Thanks for the response. I am trying to get the count of all records in all files in all libraries in an environment in AS400. For this I am using the command DSPFD FILE(*ALL/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO). When I run this command, I am getting the message "Waiting for reply to message on message queue QSYSOPR". I want the DSPFD command to run and give me the output in an outfile.
    70 pointsBadges:
    report
  • ToddN2000
    Like raven said, you may want to avoid the system libraries.

    If you only have a few user libraries as well as a production and maybe a test try this.
    If it is something you will be doing a few times, set it up as a CL program. 

    By only select know user libraries this will speed up the process

    PGM

    DSPFD FILE(USER1/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO)  

    DSPFD FILE(USER2/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO) OUTMBR(*FIRST *ADD)

    DSPFD FILE(USER3/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO) OUTMBR(*FIRST *ADD)

    DSPFD FILE(USER4/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO) OUTMBR(*FIRST *ADD)

    DSPFD FILE(USER5/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) 
    OUTFILE(QTEMP/FILEINFO) OUTMBR(*FIRST *ADD)

    ENDPGM

    then qry the file as mentioned above

    select sum(mlnrcd) from qtemp/fileinfo2
    131,380 pointsBadges:
    report
  • Splat
    Jailakshmi, what is the message on the QSYSOPR message queue?  

    If past experience is any guide I would suspect it has something to do with the maximum number of records allowed in the outfile.
    12,865 pointsBadges:
    report
  • Splat
    ToddN2000, you could also use DSPFD  FILE(*ALLUSR/*ALL) TYPE(*MBRLIST) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(QTEMP/FILEINFO).
    12,865 pointsBadges:
    report
  • Jailakshmi
    Looks like the message is due to file size.

    I was able to download the data to an outfile for one of the systems. What is the date format in the outfile? I am seeing the "Last Change Date" (MLCHGD) as 140128.
    70 pointsBadges:
    report
  • ToddN2000
    @Jailakshmi: it appears to be in YY/MM/DD format 
    131,380 pointsBadges:
    report
  • TheRealRaven
    @Jailakshmi : You should NOT be accessing files in library QSPL. That's a major example of a system library that can cause significant problems system wide. Fortunately, most systems don't have enough spooling activity to see real problems.

    For most system files that have data that is useful to you, IBM provides logical files (and/or SQL VIEWs) that are available for your use. You should always use LFs or VIEWs for those and leave the physical files alone.

    In your case, you aren't accessing data records; your accessing member headers to extract record counts. The member headers in the QSPL database files can be particularly sensitive if you happen to apply a lock at the wrong time. Further, the "record count" for those files would be completely meaningless to you, potentially completely unrelated to the amount of "data" in the files.

    QSPL files are primarily related to spooling activity. But many files in libraries such as QSYS are directly related to DB2 activity, and you don't to interfere with it. For a lot of systems, improper access of those files leads to corruption of the system's database catalog. That leads to a need to run the RCLDBXREF command to straighten it out.
    34,320 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: