Preparing to influence access paths

You can specify whether a Db2 subsystem applies optimization hints and other methods for influencing the selection of access paths for SQL statements.

To enable management of access paths on the Db2 subsystem:

1. 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
     • SYSIBM.SYSQUERYPREDICATE
     • SYSIBM.SYSQUERYSEL

   Otherwise, those actions are blocked by Db2.
2. 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 PLAN_TABLE instance and the related index are required only for access path hints. These objects are not required for other types of hints, such statement-level optimization parameters or statement-level selectivity overrides.

   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.