AS400 query using CQE instead of SQE 1

[ddiamond]

When I run my query through i-series navigator's explain tool, it says that the query will use the CQE because translation is required. I've searched, but the only examples I've found of translation functions are upper, lower, and CCSID conversions. My query includes none of these. What other functions are considered translations? Is coalesce a translation function? digits? substr? ...

 

[MarcLodge]

Dan,
Don't know if I'm teaching Granma to suck eggs, but...
I found a link here that gives the reasons why the despatcher might choose CQE over SQE. It says:

Query Dispatcher
The function of the Dispatcher is to route the query request to either CQE or SQE, depending on the attributes of the query. All queries are processed by the Dispatcher and you cannot bypass it.

Currently, the Dispatcher will route an SQL statement to CQE if it find that the statement references or contains any of the following:

INSERT WITH VALUES statement or the target of an INSERT with subselect statement
NLSS or CCSID translation between columns
Lateral correlation
Logical files
Datalink columns
Tables with Read Triggers
User-defined table functions
Read-only queries with more than 1000 dataspaces or updateable queries with more than 256 dataspaces.
DB2® Multisystem tables
non-SQL queries, for example the QQQQry API, Query/400, or OPNQRYF
The Dispatcher also has the built-in capability to re-route an SQL query to CQE that was initially routed to SQE. Unless the IGNORE_DERIVED_INDEX option with a parameter value of *YES is specified, a query will typically be reverted back to CQE from SQE whenever the Optimizer processes table objects that have any of the following logical files or indexes defined:

Logical files with the SELECT/OMIT DDS keyword specified
Non-standard indexes or derived keys, for example logical files specifying the DDS keywords RENAME or Alternate Collating Sequence (ACS) on any field referenced in the key
Sort Sequence NLSS specified for the index or logical file
As new functionality is added in the future, the Dispatcher will route more queries to SQE and increasingly fewer to CQE.

Don't know if this is of any help.

Marc

 

[ddiamond]

Thanks Marc. I'm not sure if this helps or not. What this tells me is that it is virtually impossible to right any complex query that will be processed by the SQE.

As I mentioned above, the specific reason explain tool gave me for using CQE is that I'm using translation functions. I just haven't been able to find any good definition or list of translation functions.

 

[MarcLodge]

Hi Dan, I just wandered whether the error message it was giving you was in fact incorrect and it was one of the above list that was causing it to go down the CQE route.
Marc

 

[MarcLodge]

Dan,
Having done a bit more digging I'm guessing you're getting a reason code of XL on the 'reason for CQE' column rather than an error message as I erroneously mentioned in my previous post. I found an article here that says:

Do you use *LANGSHRID sort sequence setting for your jobs to force the DB2 to perform case-insensitive string comparisons? Or maybe you use a country-specific sort sequence to sort character data based on your local character set?

So is it possible that that is your problem? Everything else I've read up says that the only functions that send it down the CQE route (and give an XL code) are LOWER, TRANSLATE and UPPER, plus "sort sequences and CCSID translation between columns".

There's a good powerpoint slide (17 & 18) here which may be of some use.

Sorry to not be able to come up with anything more useful

Marc

 

[ddiamond]

Marc,

Thanks for all of your help. Based on the above information, I believe I was getting an incorrect error code.

Logical files with the SELECT/OMIT DDS keyword specified
Non-standard indexes or derived keys, for example logical files specifying the DDS keywords

The database I'm working with has lots of complex logical files with joins and derived keys. My guess is that that is the problem. Unfortunately, the database was designed and is maintained by a 3rd party vendor, so I have no control over the logical files.

I've tried setting IGNORE_DERIVED_INDEX to *yes, but that generally causes a bunch of table scans which kill performance.

I think until we are ready to upgrade to V6R1, I'm stuck with the CQE. The only reason I was interested in the SQE is because the CQE does not support OLAP functions.