Reading multiple records by key

[Guest.Visitor]

I would like to post this question and start some discussion on everyone's view of the best way to accomplish the following: I have file A that is a "header" file (contains invoice header). I have file B that is a "detail" file (contains invoice line items). The key on file B is Invoice Number and Sequence Number When I read a record from file A, I then need to get all the detail records. This is the way I typically do it: I define a key list for file B as INVCNBR and SEQNBR. Then I set the INVCNBR and set the SEQNBR to *LOVAL. Then I perform a SETLL followed by a READ. Then I start a loop while the indicator from the READ is *OFF. Inside the loop I then check to see if the INVCNBR has gone out of key sequence, in which case I can drop out of the loop. If not, I process the record, perform a read and loop to the top. What are thoughts of doing a READE instead, or can you define a key list on just partial fields in the file's key list? (In this case, I mean defining a key list on just INVCNBR and performing a READE in my loop so I don't have to keep checking to see if the INVCNBR is different than the header file's INVCNBR field.) Any thoughts everyone? ==Scott==

[Guest.Visitor]

I think doing a SETLL and a READE would be the easist and you don't even need to create a KLIST, just use the invoice number field from the header file. When the key is no longer equal your DO loop should kick you out to the next C spec following your ENDDO.

[Guest.Visitor]

If you cannot use RPG record matching, you probably should consider a join logical file to select the information you need. This gets the finagling into DB2 and out of the RPG thing, which is always a good thing.

[David Abramowitz]

Scott Cargill wrote: can you define a key list on just partial fields in the file's key list? Yes! Define another key list with only partial fields. Dave

[Guest.Visitor]

...you probably should consider a join logical file to select the information you need... Good idea! I never thought of a logical file. I'll try that! Thanks everyone for all the good responses. I also like the idea of being able to define a key list on just some of the file's key fields. I notice the compiler didn't mind, but was worried that something would go wrong later on... Thanks again... ==Scott==

[Guest.Visitor]

Hello David, I too could not find an appropriate API. The only way that comes to my mind at the moment to retrieve this information is the cumbersome procedure to do a DSPJOB JOB(999999/YYYYYYYYYY/XXXXXXXXXX) OUTPUT(*PRINT) OPTION(*JOBLOG) (where 999999 is your jobnumber, YYYYYYYYYY your user and XXXXXXXXXX your jobname). Then copy the resulting SPLF to a PF etc. etc. etc. The desired information starts in row 6, column 58. But you can not be sure that this will still be true tomorow ... Not a very straightforward way as I have to admit. Best Regards Bernd

[flensburg@novasol.dk]

Hi David, You should be able to retrieve the initial request using the QMHRTVRQ (Retrieve Request Message) API. Please let me know if you need an RPG/IV example. Best regards, Carsten Flensburg

[Guest.Visitor]

You could also add a Validity Checking Program to the SBMJOB command with the CHGCMD command. The command string is passed to the program specified on the VLDCKR parameter. HTH

[B.Myrick]

Scott, I refrain from using join logicals because of the key overhead. I know it's not much, but I try to avoid it when possible. I prefer: c inv# setll invdetail c readc invdetail c dow not %eof(invdetail) c put your favourite process here c readc invdetail c enddo Using the DOW only performs the logic if the criteria is met, whereas the DOU will perform at least once (even if the keys don't match). This is a simple built in function of RPG and does not require any system overhead in key reference when records are added to one of the joined files. Hope this helps, Bret Myrick

[Guest.Visitor]

Carsten, Unfortunately The Retrieve Request Message (QMHRTVRQ) API retrieves request messages from the current job's job message queue. Alan, The validity checking program is probably too intrusive. As the requirement is to install the software on systems we dont own. We have a component of our solution that presents a single console image for a hetorgenous networked environment. Ideally we want to be able to display the CMD set by the SBMJOB for all AS/400 jobs. Thanks David