Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


AS400 query using CQE instead of SQE

AS400 query using CQE instead of SQE

AS400 query using CQE instead of SQE

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? ...

RE: AS400 query using CQE instead of SQE

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.


RE: AS400 query using CQE instead of SQE

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.

RE: AS400 query using CQE instead of SQE

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.

RE: AS400 query using CQE instead of SQE

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


RE: AS400 query using CQE instead of SQE


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close