SQL Optimizer for DB2 LUW 4.4 - Installation Guide

How to select the Optimal SQL Statement?

SQL Optimizer provides two different measurements of performance; run time for all records and the run time the first record. Both measurements will provide an indication on the fastest running SQL statement–but with two different aims. It is best to understand the intention of the SQL statement and in what type of source code it will be embedded. Generally, if the SQL statement is used for reports, then select the SQL statement with the run time for all records. If the SQL statement is used for on-line query, then select the SQL statement with the best run time for the first record.

We recommend the use of the actual run time of the SQL or index-set alternative rather than its DB2 LUW cost to determine which alternative is the most suitable. The DB2 LUW cost is just an estimate of the alternative's performance in relation to the other alternatives. Actual performance may be much different.

After selecting the alternative with the desired results, copy and paste it into the source program (or use the displayed index DDL to create indexes as needed). We recommend that the alternative be verified and tested before pasting it into the source program.

Caution: Do not select the SQL or index-set alternative with the lowest cost value without retrieving the run time because experience has shown that alternative with the lowest cost is not necessary the SQL with the best performance.

Note: If the aim of the SQL statement is unknown, then we recommend the use of run time for all records as a performance indication.