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?
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:
See IBM documentation, or contact IBM for more information. See the DB2 Universal Database for iSeries Database Performance and Query Optimization Manuals
Still have questions? We can help. Submit a case to Technical Support.