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