Re: Sql Table / View / index issue

The IGNORE_DERIVED_INDEXES refers to ignoring DDS created derived indexes, which are often incompatible with SQE. Incompatible due to their having originated with\for RLA versus SQL access. Beyond the compatibility issue, it is not sufficiently easy to translate all of the attributes of the DDS definition into SQL in a generic sense. The existing translations are consistent and compatible only with the CQE [in alignment with RLA concepts] rather than the SQE [in alignment with SQL concepts]; i.e. more compatible for OPNQRYF and QRY400. For example the CQE may have some requirements to honor special attributes like FIFO, but the SQL has no similar concept and so its performance should not be impacted just to do so. The SQL expects such decisions must be effected from the data itself; e.g. for FIFO, by using an ascending ORDER BY of a TIMESTAMP field that denotes when the row was inserted.

The request to ignore is really nothing more than a request of the DB engine to "please [do] not use the CQE path" just because if\when a DDS derived index is found over the data. Restated, it is a request to "Use the more efficient and faster SQL-aligned SQE to perform the query, if it is supported for this query, rather than deferring immediately to CQE to perform this query just because a DDS derived index exists."

Regards, Chuck

Lim Hock-Chai wrote:

What is the purpose of ignoring derive indexes? Shouldn't SQL try to use all possible access path to gain performance?

"Birgitta Hauser" wrote:

in release 6.1. SQL will check the derived indexes and know about
the where clauses or new fields you created. But the SQE optimizer
will not use DDS described logical files with select/omit clauses. BTW the default value for IGNORE_DERIVED_INDEXES in the
QAQQINI-File get changed in release 6.1. from *NO to *YES. That
means by default logical files with select/omit clauses now are ignored while optimizing the query.