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

David FOXWELL wrote:

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

The access paths can be shared, even if less restrictive when using Dynamic Select attribute (DYNSLT kwd) [esp. in the LFs which should be shared]. Without DYNSLT on the existing access path that might be shared, the S/O of the AccPth being created must be identical to the existing.
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/dbp/rbafoshrap.htm

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.

That scenario closely matches an example in some documentation about access path sharing, which recommends an 80/20 rule for use of DYNSLT.
http://publib.boulder.ibm.com/html/as400/v4r5/ic2931/info/db2/rbafomst55.htm

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

Given matching attributes and compatible selection between the new LF and either of LF2 or LF4, an LF8 with just K1 as its defined key should be able to share with either of those.

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.

I believe any query of the based-on physical will default to use the CQE, without the QAQQINI option to /ignore derived indexes/ being established. The query would perform with CQE regardless of any INI options, if the LF8 were the file named on the FROM clause of the SELECT.

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

There is nothing inherently wrong with using S/O LF. They are function for both non-SQL programming, utilities, and CQE. Just use what makes sense [provides the function & performance level] for the application(s). In fact, with the DB2 for IBM i 6.1 SQL, there is the ability to create an INDEX with a WHERE clause, which is effectively the same as a S/O LF. To use the SQE however, use the SQL INDEX instead, but if S\O logicals will remain then also establish the ignore derived index option of the query engine .ini file.

Regards, Chuck