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.
Procedure
To configure the data server to use an optimization profile:
- Create the optimization profile table (systools.opt_profile). Each row of the optimization profile table can contain one
optimization profile: the SCHEMA and NAME columns identify the optimization
profile, and the PROFILE column contains the text of the optimization
profile. The following example calls the SYSINSTALLOBJECTS procedure
to create the optimization profile table:
call sysinstallobjects('opt_profiles','c','','')
- Optional: You can grant any authority or privilege
on the systools.opt_profile table that satisfies
your database security requirements. Granting authority
or privilege on the systools.opt_profile table has
no effect on the optimizer's ability to read the table.
- Create an input data file that contains the three comma-separated
string values that are enclosed in double quotation marks. The first
string value is the profile schema name. The second string value is
the profile name. The third string value is the optimization profile
file name. For example, you can create an input data
file named PROFILEDATA that contains the following
three string values:
"DBUSER", "PROFILE1", "inventory_db.xml"
- Populate the SYSTOOLS.OPT_PROFILE table
with the optimization profile. The following IMPORT command
example populates the SYSTOOLS.OPT_PROFILE table
with the PROFILEDATA input data file, which contains
the profile schema name, profile name and the optimization profile
name.
db2 import from profiledata of del modified by lobsinfile insert into systools.opt_profile
- Enable the optimization profile with the CURRENT
OPTIMIZATION PROFILE special register. For
example, you can incorporate SET CURRENT OPTIMIZATION PROFILE statement
in your application:
stmt.execute( "set current optimization profile = DBUSER.PROFILE1");