SQL Optimizer for DB2 LUW 4.3.1 - User 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.

View Batch Run Details

View the Batch Run Details window

The Batch Run Details window displays a summary of the run time information for all SQL and index-set alternatives executed. The Batch Run Details window will appear after the batch run process is completed unless the Show details on next batch run checkbox in the Batch Run Details window is unchecked.

To review the Batch Run Details window

After the Batch Run is complete and the SQL Optimizer window is active, select View | Show Batch Run Details.

Batch Run Criteria Window

Quest SQL Optimizer for IBM® DB2® LUWmaximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.

SQL Optimizer provides you with the following main modules.

SQL Optimizer (including SQL Rewrite and Generate Indexes functions)

SQL Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer

Selected SQL

View the Batch Run Criteria Window—Selected SQL/Index Set

The Selected SQL/Index Set tab of the Batch Run Criteria window is used to select the SQL and index-set alternatives you want to execute. By default, all alternatives are initially selected. 

Tip: To save all settings on the Batch Run Criteria dialog for subsequent batch runs, select Save setting for the next batch run.

Selecting SQL statements to test

To select or deselect an alternative

Double-click an SQL or index-set alternative.

To select or deselect all alternatives 

Right-click and select Select All or Unselect All.

To select alternatives according to the cost values

1. Select the Apply SQL/Index Sets selection filter checkbox.

2. Click SQL/Index Sets selection filter .

3. Review the DB2 LUW cost retrieved from the virtual plans.

4. Check the Exclude SQL/Index Set with DB2 LUW cost greater than checkbox and enter a DB2 LUW cost.

Note: The original SQL statement can be de-selected only if the Original SQL option is not selected in the SQL Termination tab of the Batch Run Criteria window and the Always run Original SQL first option is not selected.

Changing the execution order of alternatives

The alternatives are ranked by DB2 LUW Cost by default, with the exception of the original SQL, which is placed at the top.

To change the execution order using the DB2 LUW cost

Click either the DB2 LUW or the SQL/Index Set column heading to reorder the alternatives for execution.

Note: High DB2 LUW Cost does not necessarily mean slower performance. If possible, it is recommended that all alternatives be tested.

To change the execution order for individual alternatives

Select the SQL or index-alternative; then click Move Up or Move Down .

Always run Original SQL first

The original SQL statement can always be run first even if it does not have the lowest DB2 LUW cost.

To always run the original SQL statement first

Select the Always run Original SQL first checkbox.

This checkbox is disabled if Original SQL is selected as a termination criterion on the SQL Termination tab or the Batch Termination tab since the run time from the original SQL is going to be used to terminate the alternatives or to terminate the Batch Run itself. In this case, you must run the original SQL statement first to obtain the termination time.