RE: Should we still be using select/omit in logical files?

Some rules about mixing SQL and S/O LFs:
- don't query an LF directly (any LF, regardless if it's S/O or not), if you
want to avoid being routed to CQE (old) query engine. If CQE is not slower,
then you probably would not care.
- even when querying PFs, mere existence of an S/O LF may cause a query to
be routed to CQE. That is, unless there is an IGNORE_DERIVED_INDEX setting
in QUSRSYS/QAQQINI set to *YES, or you're on V6R1 (that's the default
setting for that version of the OS), or your S/O LF is defined with the
DYNSLT keyword

Basically, SQE prefers full indexes while CQE could leverage an S/O LF. By
putting DYNSLT in your DDS, system will build a full index and at the file
open time run (sort of) query to effect the S/O filter you've defined. Based
on this, DYNSLT S/O LF behaves very much like an SQL view, so you could
resort to either one.

All that said, there's nothing stopping IBM for changing all of these
'rules' in the future version of the OS since changing them won't function,
only performance.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Should we still be using select/omit in logical files?

Hi all,

There doesn't seem to be any sharing of access paths when select/omit is
used in a LF.

I have :

PF1 with currently a little over 1 million client records.
7 LF on this PF. LF1 to LF7

Client records that are no longer active get flagged in the PF.

To avoid having to constantly check each record to see if it was active,
SELECT has been used in each of the logicals.

The problem : it would seem that it is not possible to share access paths.
I'm creating an LF8 with key K1

In LF2, I have K1, K2, K3, K4, K5, K6
In LF4, I have K1, K3

I created LF8 following the advice from SYSIXADV. The performance on the SQL
request in question is greatly enhanced. However, if I understand correctly,
SQE would not use LF8 if I used SELECT as in the other LF.

Should we still be using select/omit in logical files?

Thanks.