SET CURRENT OPTIMIZATION HINT statement

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

Invocation for SET CURRENT OPTIMIZATION HINT

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

Authorization for SET CURRENT OPTIMIZATION HINT

None required.

Syntax for SET CURRENT OPTIMIZATION HINT

Read syntax diagramSkip visual syntax diagramSET CURRENT OPTIMIZATION HINT= string-constanthost-variable

Description for SET CURRENT OPTIMIZATION HINT

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 128 bytes.

Notes for SET CURRENT OPTIMIZATION HINT

Using the OPTIMIZATION HINT special register: The CURRENT OPTIMIZATION HINT special register specifies whether optimization hints are used in determining the access path of dynamic statements. An empty string or all blanks indicates that Db2 uses normal optimization techniques and ignores optimization hints.

Example for SET CURRENT OPTIMIZATION HINT

Example 1: Assume that string constant 'NOHYB' identifies a user-defined optimization hint in owner.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';
If you set the register this way, Db2 validates and considers information in the rows in owner.PLAN_TABLE where the value in the OPTHINT column matches 'NOHYB' for dynamic SQL statements.
Example 2: Clear the CURRENT OPTIMIZATION HINT special register by specifying an empty string. 
   SET CURRENT OPTIMIZATION HINT = '';