Re: SQL Optimizer Issue

["Graap, Ken" <keg@xxxxxxxxxxxxx>]

I looked into this a little more ... 

IGNORE_DERIVED_INDEX  set to *YES is the default value. You shouldn't
have had to add this entry to the QAQQINI file at all... (?????)

The value of *YES means:

Allow the SQE optimizer to ignore the derived index and process the
query. The resulting query plan will be created without any regard to
the existence of the derived index(s). The index types that are ignored
include: 
Keyed logical files defined with select or omit criteria and with the
DYNSLT keyword omitted 

Keyed logical files built over multiple physical file members (V5R2
restriction, not a restriction for V5R3)
 
Keyed logical files where one or more keys reference an intermediate
derivation in the DDS. Exceptions to this are: 1. when the intermediate
definition is simply defining the field in the DDS so that shows up in
the logical's format and 2. RENAME of a field (these two exceptions do
not make the key derived) 

Keyed logical files with K *NONE specified. 

Keyed logical files with Alternate Collating Sequence (ACS) specified 

SQL indexes created when the sort sequence active at the time of
creation requires a weighting (translation) of the key to occur. This is
true when any of several non-US language IDs are specified. It also
occurs if language ID shared weight is specified, even for language US.

If *NO is specified instead of the DEFAULT value of *YES the:

Do not ignore the derived index. If a derived index exists, have CQE
process the query.

Kenneth



Subject: Re: SQL Optimizer Issue

After upgrading from V5R2 to V5R3, we experienced the same problem as
you
described. Query manger reports that use to take about a one minute to
run
were taking about 5hrs. After two months of going back and forth with
IBM,
we discovered the issues was on how Select/Omit logical files were
handed in
the new Query Optimizer engine (SQE vs CQE). Anyway, the fix was to add
a
new entry into the QAQQINI file so it would ignore select/omit logical
files. 

IGNORE_DERIVED_INDEX  set to *YES

<vhamberg-Wuw85uim5zDR7s880joybQ@xxxxxxxxxxxxxxxx> wrote in message
news:<101420052230.4281.4350318900091B02000010B92202888744099D0A0D030E08
90@c
omcast.net>...