Influencing access path selection

You can influence the access paths that DB2® uses to process SQL statements.

About this task

When you apply any of the following methods to influence access path selection, DB2 uses information that you provide during access path selection. For static SQL statements, DB2 validates and uses the information when you rebind the package that contains the statements. For dynamic SQL statements, DB2 applies, validates, and uses the information when the statements are prepared.

You can use the BIND QUERY command to influence access path selection at the statement level. When you use these methods, DB2 applies the information for SQL statements that match the statement text you specify, in any of the following contexts:

• System-wide
• From any version of particular collection and package
• From a particular version of a collection and package

You can also insert values in a PLAN_TABLE instance to specify access paths.

To influence the access path selection for SQL statements, use any of the following approaches:

• Specify optimization parameters for matching statements. You can use statement-level optimization parameters to specify the values that DB2 uses for the following options or parameters during access path selection. The parameter values apply to all matching statements in the specified context.
  • REOPT bind option
  • STARJOIN subsystem parameter
  • PARAMDEG subsystem parameter (MAX_PAR_DEGREE column)
  • CDSSRDEF subsystem parameter (DEF_CURR_DEGREE column)
  • SJTABLES subsystem parameter
  
• Specify access paths for matching statements. You can use statement-level access paths to specify that DB2 uses PLAN_TABLE rows to apply a particular access path for matching statements. Statement-level access paths are similar to PLAN_TABLE access path hints, except that they can apply to all instances of the statement that have matching query text, at a statement or package level. DB2 stores information for matching the SQL statements and the access path information in a set of catalog tables, instead of in a PLAN_TABLE instance.
• Specify an access path in a PLAN_TABLE instance. PLAN_TABLE access paths try to enforce particular access paths for SQL statements that are issued by the owner of PLAN_TABLE. They use PLAN_TABLE rows to apply hints that are specified by the OPTHINT bind option, or the CURRENT OPTIMIZATION HINT special register.