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