Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Release Notes

SQL Termination

SQL Termination (Index Expert)

ClosedView the Batch Run Criteria Window (SQL Termination)

The SQL Termination page of the Batch Run Criteria window is used to set the termination criteria for the SQL statement while retrieving the run time. If the current run time for a particular Index Set exceeds the termination time, then it will be terminated automatically which will save time during testing.

Terminate test run if the elapsed time is greater than that of

Option

Description

Used Index

The Used Index option enables the retrieval of run time for Index Sets that run faster than the Used Index. If this option is chosen, It terminates the SQL statement if runs longer than the run time from the Used Index. If you choose this option, the Used Index will be automatically selected on the Selected Index Set tab and during the Batch Run, you cannot terminate the SQL statement when running with the Used Index because its run time is need to determine when to terminate the SQL statement with the index alternatives.

Best run time SQL

The Best run time SQL option allows the retrieval of the Index Set run time that runs faster than the current best run time. With this option, the SQL statement is run and use the time from the first run used as the termination time. When the SQL statement runs faster with another index set than this original time, the faster time is used as the new termination time. So you are always using the current fastest run time as the termination time for the next Index Set.

Note: The first Index Set is either the Used Index or the Index Set with the lowest DB2 LUW Cost. This depends on whether the Always run Used Index first option is checked on the Selected Index Set tab.

User-Defined Time

The User-Defined Time option retrieves the run time of the Index Sets that are less than the defined time.

Combining Criteria

You can also combine User Defined Time with Used Index or Best run time SQL by clicking the Or user-defined time checkbox next to each one. Therefore, whichever is smaller, the user defined time or the best run time, is used as the termination time.

Percentage Delay

The percentage delay calculation adds additional time to the termination time. It is used to account for the time it takes the SQL statement to travel from the local computer to the database server over the network.

Option

Description

Percentage delay (%)

(Default = 5)

The value entered into this box is used as a percentage to calculate the additional time that is added to the termination time. For example, if the termination time is 10 minutes and the percentage delay is 5%, then all SQL statements executed are terminated if the run time exceeds 10.5 minutes. (10+ (10 * 5%))

Minimum delay time (secs)

(Default = 5, Range 1 – 99)

This is minimum number of seconds that is added to the termination time. It is necessary to factor into the overall termination time the time needed for the SQL statement be sent to the database server from the local computer before it starts to run. This number is only used if the Percentage delay calculation is lower than this value.

Maximum delay time (mins)

(Default 60, Range 1 – 9999 )

This is the maximum number of minutes that can be added to the termination time. This number is only used if the Percentage delay calculation is higher than this value.

No Termination

Option

Description

Run without termination

Execute the SQL statement until it is complete.

 

Related Documents