RE: Logical File or OPNQRYF or anyother way ? -- MIDRANGE-L

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.