DB2 10.5 for Linux, UNIX, and Windows

Specifying which optimization profile the optimizer is to use

Use the OPTPROFILE bind option to specify that an optimization profile is to be used at the package level, or use the CURRENT OPTIMIZATION PROFILE special register to specify that an optimization profile is to be used at the statement level.

This special register contains the qualified name of the optimization profile used by statements that are dynamically prepared for optimization. For CLI applications, you can use the CURRENTOPTIMIZATIONPROFILE client configuration option to set this special register for each connection.

The OPTPROFILE bind option setting also specifies the default optimization profile for the CURRENT OPTIMIZATION PROFILE special register. The order of precedence for defaults is as follows:

The OPTPROFILE bind option applies to all static statements, regardless of any other settings.
For dynamic statements, the value of the CURRENT OPTIMIZATION PROFILE special register is determined by the following, in order of lowest to highest precedence:
- The OPTPROFILE bind option
- The CURRENTOPTIMIZATIONPROFILE client configuration option
- The most recent SET CURRENT OPTIMIZATION PROFILE statement in the application