SQL Reference

SET CURRENT QUERY OPTIMIZATION

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. It 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

No authorization is required to execute this statement.

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 (for details refer to the Administration Guide ).

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 above, 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 summary 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 (SQLSTATE 01608). If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

Notes

Examples

Example 1:  This example shows how the highest degree of optimization can be selected.

  SET CURRENT QUERY OPTIMIZATION 9

Example 2:  The following example shows how the CURRENT QUERY OPTIMIZATION special register can be used within a query.

Using the SYSCAT.PACKAGES catalog view, find all plans that were bound with the same setting as the current value of the CURRENT QUERY OPTIMIZATION special register.

  EXEC SQL DECLARE C1 CURSOR FOR
    SELECT PKGNAME, PKGSCHEMA FROM SYSCAT.PACKAGES
    WHERE QUERYOPT = CURRENT QUERY OPTIMIZATION


[ Top of Page | Previous Page | Next Page ]