Pulling Values from the 'system' procedure

[Fooch]

Is this possible? I am trying to pull the total number of records currently in a file. I ahve been using the QDBRTVFD API to get all the information I need but I can't find anything in there to get the number of records in the file. But I DID find a CL command to do it and I wanted to use the 'system' procedure to run the CL command, but all I seem to be able to get from it is a 1 or 0 depending on if it is an error or not, is there any way to receive returned variables from a CL command within an RPG?

 

[flapeyre]

Why not use a file feedback data structure instead?

DOPNFBK           DS
D ODP_TYPE               81     82                                         * ODP Type
D FILE_NAME              83     92                                         * File name
D LIBRARY                93    102                                         * Library name
D SPOOL_FILE            103    112                                         * Spool file name
D SPOOL_LIB             113    122                                         * Spool file lib
D SPOOL_NUM             123    124I 0                                      * Spool file num
D RCD_LEN               125    126I 0                                      * Max record len
D KEY_LEN               127    128I 0                                      * Max key len
D MEMBER                129    138                                         * Member name
D TYPE                  147    148I 0                                      * File type
D ROWS                  152    153I 0                                      * Num PRT/DSP rows
D COLUMNS               154    155I 0                                      * Num PRT/DSP cols
[COLOR=red]D NUM_RCDS              156    159I 0                                      * Num of records
[/color]D ACC_TYPE              160    161                                         * Access type
D DUP_KEY               162    162                                         * Duplicate key?
D SRC_FILE              163    163                                         * Source file?
D VOL_OFF               184    185I 0                                      * Vol label offset
D BLK_RCDS              186    187I 0                                      * Max rcds in blk
D OVERFLOW              188    189I 0                                      * Overflow line
D BLK_INCR              190    191I 0                                      * Blk increment
D FLAGS1                196    196                                         * Misc flags
D REQUESTER             197    206                                         * Requester name
D OPEN_COUNT            207    208I 0                                      * Open count
D BASED_MBRS            211    212I 0                                      * Num based mbrs
D FLAGS2                213    213                                         * Misc flags
D OPEN_ID               214    215                                         * Open identifier
D RCDFMT_LEN            216    217I 0                                      * Max rcd fmt len
D CCSID                 218    219I 0                                      * Database CCSID
D FLAGS3                220    220                                         * Misc flags
D NUM_DEVS              227    228I 0                                      * Num devs defined

Tibi gratias agimus quod nihil fumas.

 

[Fooch]

You don't have to define it in any special way?

Just:

DOPNFBK DS

and I should be able to pull values out of it? it isn't an external data structure or anything?

 

[flapeyre]

You also have to use the INFDS keyword on the F-specs of the file. You can call the DS anything you want, as long as the names match:

     FArcrem2   UF   E           K Disk    Infds(Creds)

      * Record format for ARCREM2
     D Creds           DS        
     D  Crem2_Fmt        *RECORD 

     C     Klist7        Setll     Arcrem2       
                                                 
     C                   Dou       %EOF(Arcrem2) 
                                                 
     C     Klist7        Reade     Arcrem2       
                                                 
     C                   If        %EOF(Arcrem2) 
     C                   Leave                   
     C                   Endif                   

     C                   Select                     
                                                    
      * Record found in  record format CRE. 
     C                   When      Crem2_Fmt = 'CRE'
      ** process a special way for format 'CRE'

      * Record found in  record format CRU. 
     C                   When      Crem2_Fmt = 'CRU'
      ** process a special way for format 'CRU'

     C                   EndSl

     C                   EndDo

This is using the INFDS to determine which format in a multi-format logical is being read, but you get the idea. You can just as easily see how many records are in the file if you define that subfield in the INFDS.

Tibi gratias agimus quod nihil fumas.

 

[Fooch]

That is the issue though, I don't know the file until the program is called. This is going to be a generic module that will be used to monitor files before jobs are run. If they are too full I will boost the size of the file. I need to pull the number of records from the file they pass in.

I could probably use a generic file name and specify it with an ExtFile() as a variable and populate that and manually open it...can you do an INFODS on an external file like that? I just thought there would be an easier way.

 

[flapeyre]

I don't see why Extfile() won't work with Infds(). There probably is an API, but for me, the simpler, the better.

On a broader issue, why would you need to "boost the size of the file"? Why not just CHGPF to MAXRCDS(*NOMAX) and be done with it? Who needs to deal with those messages that the file has reached its maximum size?

I would recommend that you make all PF's *NOMAX and also set the files to reuse deleted records (assuming you are not processing anything by RRN). The downside is you this way you can't use any utility to retrieve deleted records (on the other hand, you should have backups?) This way, the files only grow when the have to, and without user intervention.

Tibi gratias agimus quod nihil fumas.

 

[arrow483]

Another option might be to use embedded SQL

Select count(*) into :recCount from myfile

This would tell you how many records exist, but not the max file size.

I agree with flapeyre, just change the max records value.

 

[Fooch]

The company I work for will NOT allow me to create files with *NOMAX for the fear of runaway jobs. And if I use ExtFile I have to create liek a Shell file so I can compile the RPG over it. Taht kinda defetes the purpose of trying to do it all in RPG, I would just use a CL instead. There has to be a way to retrieve the number of records in the file within the RPG.

 

[Fooch]

I like the SQL Idea. I may have to do that one. I already have the max number of records possible by using the QDBRTVFD API. I am surprised that API can't get this value as well.

 

[Fooch]

One concern though, what if the file has multiple members? How can I count all of them with SQL?

 

[flapeyre]

The company I work for will NOT allow me to create files with *NOMAX for the fear of runaway jobs.

The correct approach is to identify the programs that "run away" and re-write them so that they don't. If, for example, you have a program that writes to an output file, you need to have a unique key declared on the output physical file (or one of its logicals) to prevent duplicates. And anything that writes to a file needs to check to see if the record exists before writing a record.

I realize that manpower issues/mangagement inertia play a role here. Just consider that it's easier to fix it once and for all than to spend all of your time putting out fires.

As for SQL and multiple members, you need to create an alias over the PF and member and then you can use SQL as arrow483 described.

Tibi gratias agimus quod nihil fumas.

 

[flapeyre]

Hmm. I have not tried it, but see if you can use EXTMBR('*ALL'). Maybe that will work with the INFDS and give you the total count.

Tibi gratias agimus quod nihil fumas.

 

[Fooch]

Yeah but to use an INFDS I need a file...I can't hardcode a file in there because I don't know a file until the user passes one in. I could put a dummy file there but then I would have to create that object on the system and that would defeat the purpose of doing everything in the RPG. I am trying to do this with only creating one object.

 

[Mercury2]

Fooch,
You should use the Retrieve Database File Description QUSRMBRD API instead to get the current number of records in the file from the RPG program.
The Retrieve Database File Description (QDBRTVFD) API allows you to get complete and specific information about a file not a member where the number of records resides and a file can have many members.

 

[Fooch]

Yeah, I saw that. but you need a list of the members...to get that you are going to have to use a DSPFD FILE(pass ing file) TYPE(*MBRLIST) OUTPUT(*OUTFILE) OUTFILE(QTEMP/dummyfile). Then read through each one of those records. And since when you do that it already puts the number of records in each member I can just add them up and not use the QUSRMBRD API. I can't just use the QUSRMBRD API without knowing the member list. And unless passed, the only way I can see to get the member list is to use the DSPFD through the system() procedure. I was trying to get around this without creating ANY other objects, even if it is volitale in QTEMP...but this seems to be the only way. I think IBM even loops through the members to show the max number of records when you do a DSPFD on a file. So it looks like there is no way around it. Unless someone else has a suggestion? lol But thanks a lot for all the suggestions, this topic got me thinking, I like that lol