You can specify whether a DB2® subsystem
applies optimization hints and other methods for influencing the selection
of access paths for SQL statements.
Procedure
To enable management of access
paths on the
DB2 subsystem:
- Set the value of the OPTHINTS subsystem parameter to 'YES'. This value is set by the OPTIMIZATION HINTS field on the performance
and optimization installation panel. When you specify
'YES', DB2 enables
the following actions:
- SET CURRENT OPTIMIZATION HINT statements.
- The OPTHINT bind option.
- Statement-level matching for rows
in the following catalog tables:
- SYSIBM.SYSQUERY
- SYSIBM.SYSQUERYPLAN
- SYSIBM.SYSQUERYOPTS
Otherwise, those actions are blocked by DB2.
- Create a required index on instances of PLAN_TABLE that
contain the access paths. The index improves the prepare performance when access path hints are used. The following example statement creates the
index:
CREATE INDEX userid.PLAN_TABLE_HINT_IX
ON userid.PLAN_TABLE
( "QUERYNO",
"APPLNAME",
"PROGNAME",
"VERSION",
"COLLID",
"OPTHINT" )
USING STOGROUP stogroup-name
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
The statement that creates the index is also included as part of the DSNTESC member of
the SDSNSAMP library.