SET CURRENT OPTIMIZATION HINT

The SET CURRENT OPTIMIZATION HINT statement assigns a value to the CURRENT OPTIMIZATION HINT special register.

Restriction: This topic applies to Windows environments only.

This command is supported only syntactically by the XDB Server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

SET CURRENT OPTIMIZATION HINT = {string-constant | host-variable}

Description

The value of special register CURRENT OPTIMIZATION HINT is replaced by the value of the string constant or host variable. The value must be a character string that is not longer than 8 bytes.

The initial value of the CURRENT OPTIMIZATION HINT special register is set to the value that was used for the OPTHINT bind option. The OPTHINT bind option specifies whether optimization hints are used in determining the access path of static statements and identifies which user-defined hint (rows in the authid.PLAN_TABLE) is used. Therefore, if the SET CURRENT OPTIMIZATION HINT statement is not executed to change the value of the special register, DB2 uses the same optimization hint for dynamic statements that it uses for static statements. The default of OPTHINT for BIND PLAN and BIND PACKAGE is all blanks. All blanks indicate that DB2 uses normal optimization techniques and ignores optimization hints.

Example:

Assume that delimited identifier 'NOHYB' identifies a user-defined optimization hint in authid.PLAN_TABLE. Set the CURRENT OPTIMIZATION HINT special register so that DB2 uses this optimization hint to generate the access path for dynamic statements.

SET CURRENT OPTIMIZATION HINT = 'NOHYB'