Influencing access path selection

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

Before you begin

Prepare to influence access paths.
Create any input tables that are required.
The following methods for influencing access path selection are best applied in exception cases, when normal access path selection results in efficient access paths. Before applying any of these methods, first take the following actions:

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.

Tip: Enhanced query tuning capabilities that can help you with this task are available in IBM® Db2 Query Workload Tuner for z/OS® and IBM Db2 Administration Foundation for z/OS.

Procedure

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
Override the selectivity of predicates for matching statements.
You can use predicate selectivity overrides to specify that selectivity values other than default filter factors during access path selection. The selectivity values apply to all matching statements in the specified context. Overriding predicate selectivities is usually the best approach for statements that contain predicates whose selectivities are difficult or impossible for Db2 to estimate. Db2 can use the improved selectivity information to choose an optimal access path for the SQL statement. Some other methods try to enforce a particular access path and remove Db2 query optimization from the process.
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.