RE: Impact of QAQQINI setting IGNORE_DERIVED_INDEX=*YES

Darren, 

I've recommended this setting to a number of our customers and they were all
thrilled with the performance jump (shops drive a lot of SQL).

You shouldn't worry about the case where derived index may be picked as SQE
may build sparse index on the fly out of existing index if it really needs
one (index-out-of-index), and it may be reusable by all jobs on the system
(V5R4). 
Rather, view it as which percentage of your queries will go down new SQE
path.  As you know SQE is the only engine being enhanced, both for function
and performance.  Accordingly, most of your queries will perform much
better.  I've seen an IBM benchmark comparing two engines and I'd say
performance increase resulted in high 90s percentile with SQE.

Yes, I am saying make it permanent :)

Elvis

-----Original Message-----
Subject: Impact of QAQQINI setting IGNORE_DERIVED_INDEX=*YES


I recently did a little fancy footwork with the QAQQINI file so that I
could temporarily set the IGNORE_DERIVED_INDEX setting to *YES.  This was
necessary to cause IBM's enhanced query engine (SQE) to be used where I had
derived logicals built against a physical file, and I wanted to do some SQE
only functions.  My question is, what if I make this IGNORE_DERIVED_INDEX
*YES permanent.  I was initially afraid of bad query performance, but
through some experiments, it seems rare that the query optimizer ever picks
a "derived index" type logical anyway.

I guess, my more specific question is, under what conditions will the query
optimizer pick a "derived index" since my initial experiments show it not
picking it, despite constructing a logical that should have chosen the
derived index.

The bulk of the offending logicals are DDS built for our BPCS ERP system.
They are often used to control soft-deleted records.
\