How to retrieve a file’s record count
John needs your help. He recently wrote the editors at Search400.com and asked, "Is there another way of retrieving a file's record count besides reading the file and counting? Can you use RTVMBRD?"
What do you think?
Michelle Davidson
Editor
Search400.com
Software/Hardware used:
Software/Hardware used:
ASKED:
August 23, 2005 10:30 AM
UPDATED:
November 2, 2010 1:15 PM
Answer Wiki:
I routinely use the CL command DSPFD command and direct the output to the system-supplied outfile QAFDMBR.
The CL code is:
DCLF FILE(QAFDMBR)
DSPFD FILE(MyLib/MyFile) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*ALL) OUTFILE(QTEMP/QAFDMBR)
OVRDBF FILE(QAFDMBR) TOFILE(QTEMP/QAFDMBR) POSITION(*START)
RCVF
Then check the DB field &MBNRCD to determine the number of records.
If you are executing this code in batch then the QTEMP file will disappear when the batch job ends. If you execute this code interactively then the QTEMP file will not disappear when your session ends. You should include additional code to handle the existence of the QTEMP file if you plkn to run your code multiple times within the same interactive session.
===========================================================
Retrieving record counts is a major purpose of RTVMBRD. The NBRCURRCD() parameter returns the number of <i>current</i> records. 'Current' are ones that are not deleted. This is meaningful because RTVMBRD can also return the number of deleted records in the NBRDLTRCD() parameter. Adding the two values together results in the total amount of space taken for records by the member.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
We routinely use the following in our CL’s to get the number of records in a file.
DCL VAR(&RCDNBR) TYPE(*DEC) LEN(10)
RTVMBRD FILE(QS36F/CHP.3601) NBRCURRCD(&RCDNBR)
You may then place &RCDNBR into the LDA and pass it to any RPG programs in a UDS.
If you are looking for a record count in an rpg program you can use the INFDS the field FIRECS contains the record count.
Here’s one example:
DCL VAR(&RECORDCNT) TYPE(*DEC) LEN(10 0) VALUE(0) DCLF FILE(DSPPGMRFPF) CLRPFM FILE(WTI0001PF) CLRPFM FILE(DSPOBJDPF) DSPOBJD OBJ(*ALL/&WHPNAM) OBJTYPE(*PGM) + OUTPUT(*OUTFILE) OUTFILE(*LIBL/DSPOBJDPF) MONMSG MSGID(CPF2123) RTVMBRD FILE(DSPOBJDPF) NBRCURRCD(&RECORDCNT) IF COND(&RECORDCNT *GT 0) THEN(DO) CALL PGM(WTI0001R)The record count from the INFDS in RPG works great, it even works without opening the file.
The record count from the INFDS in RPG works great, it even works without opening the file.
The Open Feedback Information portion of the INFDS contains ‘number of records’ at the time the file was opened, as the name implies. Note that it only returns the count of active records. Deleted records are not included. Also, an INFDS and an open would have to be coded for each individual file.
RTVMBRD can be called for any file without concern for level checks nor other complications.
Tom
Plenty of options here already, but here’s a simple RPG/SQL option:
(assuming :FUNCOUNT is the declared variable to which you want to save the record count, of course…)