RE: Logical File or OPNQRYF or anyother way ?

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

I'm going to say that, generally, a logical file is a good thing to have, 
even if you use SQL or OPNQRYF. The latter uses the old query engine in 
V5R2 and above. If you don't have indexes, the optimizer will either choose 
to create a temporary index or to read the entire table (known as a table 
scan). Both are not good ideas with a really large file.

You can have, as Charles suggests, indexes over single columns. These can 
be used most effectively for selection, as in bitmap selection, where a 
bitmaps are created from each index based on selection criteria, then these 
bitmaps are combined bitwise to get the composite. I don't believe this 
works for sorting, only for selection. But I could be wrong. Or maybe 
indexes bult from indexes can use the simgle-column logicals. Not sure.

If you always use the same fields for selection, build a logical over those 
fields. This will let you use the index directly in RPG, as well as giving 
the best support for optimizer-based processes like SQL and OPNQRYF.

So I think I'm saying that the question is not whether to use a logical or 
to use OPNQRYF. Logicals take some space but provide performance benefits. 
OPNQRYFs (and SQL) make use of appropriate logical files, so they often 
work together. The downsides to logicals are space used and the need to 
maintain them when writes/updates/deletes occur in the underlying physical 
file. If the key fields you are considering are changed a lot, then maybe a 
logical is not recommended - although MAINT(*REBLD) can minimize the cost 
in performance - but first use will be slower, as the logical needs to 
rebuilt or maintained before it can be used. But if these values are fairly 
static, go ahead and create the new logical.

Question - are there any logicals that have these fields first in the key, 
with more fields to follow? Then you already have a suitable logical file.

HTH
Vern

At 03:38 PM 3/4/2005, you wrote:
> While others have pointed out that embedded SQL is an option (and one I 
> and others prefer over OPNQRYF), let me point a reason not to use a logical.
>
> Each set of sort/select criteria needs its own LF.
>
> In contrast, SQL (and OPNQRYF?) can make use of composite keys when they run.
>
> For example, lets say you have three fields: key1, key2, key3.  To provide 
> for all possible sorts, you'd need 9 separate logicals with the overhead 
> or performance penalties associated with maintaining the access paths.  On 
> the other hand, you could simply use 3 indexes, one over each key.  As 
> needed the DB2 query engine make composite keys from the separate indexes.
>
> Does this make sense?
>
> Additionally, one plus for embedded SQL is it's higher performance vs. 
> native RPG I/O.  _IF_ you use it properly.  This means, only select the 
> fields of interest to you and fetch more than one row at a time.
>
>
> HTH,
>
> Charles Wilt
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Malini Rao
> > Sent: Friday, March 04, 2005 10:41 AM
> > To: MIDRANGE-L@xxxxxxxxxxxx
> > Subject: Logical File or OPNQRYF or anyother way ?
> >
> >
> > Here is a basic question as I am new to AS/400
> >
> > I have a physical file which has millions of records, and
> > this physical file gets updated quite frequently by various
> > programs in the application.
> >
> > I have to develop 2 programs pertaining to an enhancement request.
> >
> > Program 1
> >
> > Needs to read the file selectively based on two fields of the
> > physical file , perform calculations on the selected records
> > and update them. There is no existing logical file on the
> > physical with these 2 fields as a key
> >
> > Program 2
> >
> > Needs to read (only)the file selectively. File is opened in
> > input mode only.
> >
> > Which is the more efficient method in each case - creating a
> > logical file with the 2 fields as key or using an openquery
> > with select on the 2 fields before each program is called.
> > Any other efficient way ?
> >
> > Also :- What are the scenarios where a logical file is
> > preferred over an open query file and vice-versa ?
> >
> > Thanks in advance
> > Malini
> > --
> > This is the Midrange Systems Technical Discussion
> > (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.