Blog
DB2utor

Categories

July 26, 2011

Runtime Optimization Hints

In writing about changes to SQL optimization hints, I neglected to note that there are actually two types of statement-level hints. I only covered access-level optimization hints last week. The other type of statement level optimization hint is to set the runtime options for a given SQL statement text.

The setup for runtime options hints is similar to that of optimization access path hints. Insert a row into DSN_USERQUERY_TABLE with a unique QUERYNO. In doing this, make sure that no row is found in the user PLAN_TABLE for the given statement, because if there's a row in the PLAN_TABLE when the BIND QUERY LOOKUP(NO) runs, the SYSIBM.SYSQUERYOPTS table won't contain a row. That said, if you do make this mistake, it's a simple fix. Just delete the rows from the user PLAN_TABLE and rerun the BIND QUERY command to populate SYSIBM.SYSQUERYOPTS.

Hints are an either-or proposition. It's not possible for one hint to be both an optimization access path hint and a optimization options hint. Remember: If you're creating an options hint, you must not have rows in the PLAN_TABLE for the given statement. I always delete the rows in DSN_USERQUERY_TABLE and insert new rows with the settings I want to be populated into the access path repository.

The runtime options that can be set using DSN_USERQUERY_TABLE are:

REOPT:    Set bind options that is in effect for the plan
(A:AUTO, 1:ONCE, N:NONE, Y:ALWAYS)
STARJOIN:    Whether Starjoin is Enabled
 (Y:Enabled, N:Disabled, blank:Starjoin not specified)
MAX_PAR_DEGREE:    The maximum parallel degree. 
Valid values between 0 and 254, -1:Not Specified
DEF_CURR_DEGREE:    Whether query parallelism is enabled
            (ONE: Disabled, ANY:Enabled, blank: Disabled
SJTABLES:        The number of tables specified in a query to qualify for star joins processing.
-1: Not Specified
GROUP_MEMBER:    The group member name to which the parameters are to be applied. This column is
 blank if no group member name is specified.

Sample Insert into user DSN_USERQUERY_TABLE to define a system-level option hint for a statement to be executed with REOPT(ALWAYS). The HINT_SCOPE is set to 0 and REOPT is set to ‘Y’:

INSERT INTO DSN_USERQUERY_TABLE          
( QUERYNO, SCHEMA, HINT_SCOPE            
 , QUERY_TEXT, USERFILTER, OTHER_OPTIONS 
 , COLLECTION, PACKAGE, VERSION          
 , REOPT, STARJOIN, MAX_PAR_DEGREE       
 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS
)                                        
SELECT 1                                 
     , '' , 0                            
     , PS.STATEMENT, '',''               
     , '', '', ''                        
     , 'Y','', -1                        
     , '', -1, ''                        
FROM SYSIBM.SYSPACKSTMT PS               
WHERE PS.COLLID = 'COLTR05_COLLID'       
  AND PS.NAME = 'ROWSET'                 
  AND PS.VERSION = 'MYVERSION_1'         
  AND PS.STMTNO = 264;