Scenario:
A fairly complex query containing a LIKE condition runs a long time, but if the LIKE condition is removed, it runs significantly faster. After talking with IBM support about this, we learned the LIKE condition forces the query to run using the CQE query engine. Without the LIKE condition the query will run using the faster SQE engine.
So, how can we make this query run in the SQE engine, without removing the LIKE condition?
Solution:
With V5R2, IBM introduced a new query engine called SQE. The old query engine is called CQE, and this remains as part of the OS. The Query Optimizer automatically determines which query engine to use, based on the SQL to be run.
As of V5R4, certain SQL functions still required use of the CQE engine. If those functions weren't used in the query, then the Query Optimizer will use the SQE engine. IBM documentation states that this isn't controllable by thrid-party software vendors - it's strictly the domain of the i5/OS. Therefore, the only way to influence the selection of the query engine - for performance purposes - is to change the SQL being sent to the Optimizer.
Using a relatively new feature of iSeries Access called Visual Explain, it's possible to determine in advance which engine the Optimizer will use to execute the query.
In the example above, the LIKE condition was changed to an = condition and used the LEFT function to only compare the left 8 characters of the field with the literal. It decreased the run time from 3-1/2 hours to under one minute, because of which query engine was used.
This feature is available by logging in to the iSeries in iSeries Navigator:
- Go to Databases, and right click on the database name.
- Select Run SQL Scripts.
- From this screen, you can paste in a Select statement and then, from the VisualExplain menu, select the appropriate action. Visual Explain allows you to rewrite or alter SQL select statements, change the query attributes or environment settings, or create new indexes. It can estimate query performance without running the query.
See IBM documentation, or contact IBM for more information. See the DB2 Universal Database for iSeries Database Performance and Query Optimization Manuals
Ref#: 1478002
