Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4 - Installation Guide

SQL Termination

ClosedView the Batch Run Criteria Window—SQL Termination

The SQL Termination tab of the Batch Run Criteria window is used to set the SQL termination criteria for each SQL and index-set alternative for which you are retrieving run times. If the current run time for a particular alternative exceeds the termination time, then the execution of the alternative terminates automatically. This will save time during testing.

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

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

Option

Description

Original SQL

Retrieves the run time of alternatives that run faster than the original SQL statement. It terminates all alternatives that run longer than the run time from the original SQL. If you choose this option, the original SQL statement is automatically selected on the Selected SQL/Index Set tab. During the Batch Run, you cannot terminate the original SQL because its run time is needed to determine when to terminate the alternatives.

Best run time SQL/Index Set

Retrieves the run time of alternatives that run faster than the current best run time. With this option, the first alternative is run and the time from that statement is used as the termination time. When an alternative runs faster than this 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 alternative.

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

User-defined time

Retrieves the run time of alternatives that are less than the defined time. If your original SQL statement takes a long time to execute and there are many alternatives, executing all alternatives can take considerable time. In that event, consider setting an aggressive user-defined termination time. If the original SQL takes 1 hour, try a 5-minute termination time. If no alternative executes under that period, raise the termination time to 10 minutes, etc.

Combining Criteria

You can also combine User-defined time with Original SQL or Best run time SQL/Index Set by clicking the Or user-defined time checkbox next to each one.

Percentage Delay

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

Option

Description

Percentage delay (%)

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 alternatives 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 alternative 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.

Run without Termination

Option

Description

Run without termination

Executes all the alternatives until they are completed.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating