Access plan and query optimization profiles

Optimization profiles store optimization guidelines that you create to control the decisions made by the optimization stage of the query compiler.

Anatomy of an optimization profile
An optimization profile can contain global optimization guidelines, and it can contain specific optimization guidelines that apply to individual DML statements in a package. Global optimization guidelines apply to all data manipulation language (DML) statements that are executed while the profile is in effect.
Creating an optimization profile
An optimization profile is an XML document that contains optimization guidelines for one or more Data Manipulation Language (DML) statements.
SQL compiler registry variables in an optimization profile
Optimization profiles can have different registry variable values applied to a specific query statement or to many query statements used in an application.
Configuring the data server to use an optimization profile
After an optimization profile is created and its contents are validated against the current optimization profile schema (COPS), the contents must be associated with a unique schema-qualified name and stored in the SYSTOOLS.OPT_PROFILE table.
STMTKEY field in optimization profiles
Within a STMTPROFILE, the targeted statement is identified by the STMTKEY subelement. The statement that is defined in the STMTKEY field must match exactly to the statement being run by the application, allowing Db2® to unambiguously identify the targeted statement. However, 'white space' within the statement is tolerated.
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.
Modifying an optimization profile
When you make a change to an optimization profile there are certain steps that need to be taken in order for the changes in the optimization profiles to take effect.
Deleting an optimization profile
You can remove an optimization profile that is no longer needed by deleting it from the SYSTOOLS.OPT_PROFILE table. When an optimization profile is referenced, it is compiled and cached in memory; therefore, if the original profile has already been used, you must also flush the deleted optimization profile from the optimization profile cache.

Parent topic: Optimization profiles and guidelines