SQL Optimizer for DB2 LUW 4.3.1 - User Guide

Determine Best Performing Index Sets

After you have generated or created Index Sets for your original SQL statement in the Index Expert window, you can determine which Index Set gives you the best performance. The Batch Run function is used to retrieve the run time of the original SQL statement under different Index Sets. In the Batch Run, the indexes are physically created, the run time for the SQL statement is retrieved and then the indexes are dropped.

Note: When the indexes are created, the COLLECT STATISTICS clause is included in the CREATE INDEX statement to provide more accurate results when the SQL statement is executed with the index.

To open the Batch Run Criteria window

1. Click .

2. Select the Batch Run criteria from the following pages:

Selected Index Set

SQL Termination

Batch Termination

Run Time Mode/ Repeat Test

Batch Run Schedule

3. The settings you choose can be saved for next test run by selecting the Save settings for next batch run checkbox. By default, this checkbox is unselected.

The Batch Run window displays the run time criteria and the current run time of the SQL statement as it is retrieved for each Index Set The actual access plan can also be retrieved if the Retrieve access plan upon run time setting in the Options is selected.  

To terminate the running of the SQL statement under the current Index set

Select SQL | Stop Current.

To terminate the batch run process

Click .