DB2 10.5 for Linux, UNIX, and Windows

SET CURRENT QUERY OPTIMIZATION statement

The SET CURRENT QUERY OPTIMIZATION statement assigns a value to the CURRENT QUERY OPTIMIZATION special register. The value specifies the current class of optimization techniques enabled when preparing dynamic SQL statements.

This statement is not under transaction control.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagram
                                      .-=-.   
>>-SET--CURRENT--QUERY--OPTIMIZATION--+---+--------------------->

>--+-0-------------+-------------------------------------------><
   +-1-------------+   
   +-2-------------+   
   +-3-------------+   
   +-5-------------+   
   +-7-------------+   
   +-9-------------+   
   '-host-variable-'   

Description

optimization-class
optimization-class can be specified either as an integer constant or as the name of a host variable that will contain the appropriate value at run time. An overview of the classes follows.
0
Specifies that a minimal amount of optimization is performed to generate an access plan. This class is most suitable for simple dynamic SQL access to well-indexed tables.
1
Specifies that optimization roughly comparable to DB2® Version 1 is performed to generate an access plan.
2
Specifies a level of optimization higher than that of DB2 Version 1, but at significantly less optimization cost than levels 3 and higher, especially for very complex queries.
3
Specifies that a moderate amount of optimization is performed to generate an access plan.
5
Specifies a significant amount of optimization is performed to generate an access plan. For complex dynamic SQL queries, heuristic rules are used to limit the amount of time spent selecting an access plan. Where possible, queries will use materialized query tables instead of the underlying base tables.
7
Specifies a significant amount of optimization is performed to generate an access plan. Similar to 5 but without the heuristic rules.
9
Specifies a maximal amount of optimization is performed to generate an access plan. This can greatly expand the number of possible access plans that are evaluated. This class should be used to determine if a better access plan can be generated for very complex and very long-running queries using large tables. Explain and performance measurements can be used to verify that a better plan has been generated.
host-variable
The data type is INTEGER. The value must be in the range 0 to 9 (SQLSTATE 42815) but should be 0, 1, 2, 3, 5, 7, or 9. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

Notes

Examples