CURRENT OPTIMIZATION HINT special register

CURRENT OPTIMIZATION HINT specifies the user-defined optimization hint that Db2 should use to generate the access path for dynamic statements.

The data type is VARCHAR(128).

The value of the register identifies the rows in owner.PLAN_TABLE that Db2 uses to generate the access path. Db2 uses information in the rows in owner.PLAN_TABLE for which the value of the OPTHINT column matches the value of the CURRENT OPTIMIZATION special register. If the value of the register is an empty string or all blanks, Db2 uses normal optimization and ignores optimization hints. If the value of the register includes any non-blank characters and Db2 was installed without optimization hints enabled (field OPTIMIZATION HINTS on installation panel DSNTIP8), a warning occurs.

The initial value of CURRENT OPTIMIZATION HINT is the value of the OPTHINT bind option or of the native SQL procedure option. The initial value of CURRENT OPTIMIZATION HINT in a user-defined function or stored procedure is inherited according to the rules in Table 1. You can change the value of the special register by executing the statement SET CURRENT OPTIMIZATION HINT.

Example: Set the CURRENT OPTIMIZATION HINT special register so that Db2 uses the optimization plan hint that is identified by host variable NOHYB when generating the access path for dynamic statements.

   SET CURRENT OPTIMIZATION HINT = :NOHYB