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");