Ever since the SQL Query Engine (SQE) was first introduced in V5R2, users have wanted to know how many of their SQL statements are being processed by this new engine as opposed to the Classic Query Engine (CQE).

The best tool for answering this question is the Detailed Database Monitor. You can initiate a database monitor collection by using the Start Database Monitor (STRDBMON) command or by using the Detailed SQL Performance Monitor in iSeries Navigator. The iSeries Navigator interface will invoke the STRDBMON command on your behalf. (For more information on the database monitor tool, see the new Redbook SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries.)

Once the Detailed Database Monitor data has been collected and written to an output file, you can run the following SQL statement from any interface to get a count of how many statements were processed by CQE versus SQE:

SELECT qqc16 as "N = CQE", COUNT(*)
FROM mylibname/mydbmon
WHERE qqrid = 3014
GROUP BY qqc16

Here's an example of what will be returned by this query. In this case, 1958 SQL statements were processed by the SQL Query Engine, and just 97 statements were executed by the Classic Query Engine.



After finding out that not all of your SQL statements are processed by SQE, the next step is determining why some SQL requests were routed to CQE. Run the following query against your Detailed Database Monitor collection to list the SQL statements processed by CQE and the reasons why CQE was used.

SELECT x.qqjnum, x.qqucnt, y.QVC43 as "Reason for CQE", x.qq1000
FROM mylibname/mydbmon x, mylibname/mydbmon y
WHERE x.qqjfld = y.qqjfld
AND x.qqrid = 1000
AND x.qqc21 IN ('SI','OP','IN','UP','DL')
AND x.qqucnt <> 0
AND y.qqrid = 3014
AND y.qqc16 = 'N'
ORDER BY qqjnum, qqucnt

Here's an example of the output that can be returned by this analysis query. The last column contains the SQL statement text, and the column next to that shows the reason code for CQE being used.




In this example, "XL" means that the SQL statement required data to be translated. Data translation can occur in several situations, such as running the UPPER and LOWER scalar functions or applying a sort sequence to the character comparisons and sorting operations done by SQL. SQE does not yet support data translation. For an explanation of all the CQE reason codes, please reference DB2 Universal Database for iSeries Database Performance and Query Optimization.

Kent Milligan is a DB2 Technology Specialist on IBM's ISV Enablement team for System i. He spent the first seven years at IBM as a member of the DB2 development team in Rochester. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

Kent Milligan is a Senior Db2 for i Consultant in the IBM Lab Services Power Systems Delivery Practice.  Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the DB2 for i Lab Services practice in 2020, Kent spent 5 years working on healthcare solutions powered by IBM Watson technologies. Kent is a sought-after speaker and author on Db2 for i & SQL topics.