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
.-=-.
>>-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
- When the CURRENT QUERY OPTIMIZATION register is set to a particular
value, a set of query rewrite rules are enabled, and certain optimization
variables take on particular values. This class of optimization techniques
is then used during preparation of dynamic SQL statements.
- In general, changing the optimization class impacts the execution
time of the application, the compilation time, and resources required.
Most statements will be adequately optimized using the default query
optimization class. Lower query optimization classes, especially classes
1 and 2, may be appropriate for dynamic SQL statements for which the
resources consumed by the dynamic PREPARE are
a significant portion of those required to execute the query. Higher
optimization classes should be chosen only after considering the additional
resources that may be consumed and verifying that a better access
plan has been generated.
- Query optimization classes must be in the range 0 to 9. Classes
outside this range will return an error (SQLSTATE 42815). Unsupported
classes within this range will return a warning (SQLSTATE 01608) and
will be replaced with the next lowest query optimization class. For
example, a query optimization class of 6 will be replaced by 5.
- Dynamically prepared statements use the class of optimization
that was set by the most recently executed SET CURRENT QUERY OPTIMIZATION
statement. In cases where a SET CURRENT QUERY OPTIMIZATION statement
has not yet been executed, the query optimization class is determined
by the value of the dft_queryopt database configuration
parameter.
- Statically bound statements do not use the CURRENT QUERY OPTIMIZATION
special register; therefore this statement has no effect on them.
The QUERYOPT option is used during preprocessing or binding to specify
the required class of optimization for statically bound statements.
If QUERYOPT is not specified then, the default value specified by
the dft_queryopt database configuration parameter
is used.
- The results of executing the SET CURRENT QUERY OPTIMIZATION statement
are not rolled back if the unit of work in which it is executed is
rolled back.