Re: OPNQRYF Vs Logical Files

["Joe Teff" <jteff19@xxxxxxx>]

> > Venu wrote:
> >
> >Hi All,
> >
> >     In one of my jobs, I will be processing data from a physical file say
> >File1. File1 has 3 members. I will select data from the 2 out of 3 members.
> >Also I have a select criteria (select where File1.field1 = xxxx) to process
> >the records.
> >
> >     At present I've created a Logical file over the two members of the PF
> >with the Select criteria specified in DDS of the LF. This LF I am using in
> >my RPG. But the job processes 1.3 million records from both the members.
> >This job runs in batch.
> >
> >     Our people are suggesting to replace the LF with a OPNQRYF in a driver
> >CL. So after discussing the pros and cons, I have 2 alternate ways :
>
> Vern Hamberg wrote:
> 
> Venu,
> 
> I don't think that OPNQRYF is automatically a better solution than LFs.
> First of all, every time it runs, it has to go through a query optimization
> process that adds overhead to the job and will probably slow it done. In
> any case, you need logical files (access paths, or indexes, that is) that
> can _support_ the optimization process. And you can't always guarantee that
> the optimizer will use the logicals you've set up.
> 
> It sounds like you don't need to relate or combine the 2 members, or do you
> combine them in the DDS into a single member of the logical file? In either
> case, I think the better bet is to keep doing what you are, reading the
> logical in key sequence in the RPG, with overrides to separate members (if
> any) in the logical file.
> 
> A more effective way to speed up the process might be to consider
> preloading records in another process, to minimize the physical IO needed
> for this program. There's been some discussion of this. Can someone else
> provide the details--I'd like to know, too.

It depends on how often the job is run. I use logicals when it is run 
frequently (daily, weekly, etc.) and other methods if less frequent. The size 
of the file and number of records selected also impact my choice. Logicals, 
OPNQRYF, Sort (FMTDTA) and even a temp file created during the process are 
options. The temp file is preferable in cases where the same file (or subset of 
that file) is used in multiple programs during the procedure and it is made up 
of multiple files (codes with descriptions, header info with detail records, 
etc.) and keys from multiple files. Temp files can be re-orged so you get 
blocking performance as well. It really depends upon the specific needs for 
function you are performing.

Joe Teff
QDS
Bloomington, MN
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---