Managing query access paths

The access paths that Db2 uses to process SQL statements are among most important aspects of query performance.

About this task

The access path for an SQL statement specifies how Db2 accesses the data that the query specifies. It specifies the indexes and tables that are accessed, the access methods that are used, and the order in which objects are accessed.

Db2 selects the access paths for most static SQL statements when application program is bound or rebound into a package. However, access paths for some statements, such as statements that contain variable values and parameter markers, must be selected at run time. Db2 selects the access paths for dynamic SQL statements when the statements are issued.

To select efficient access paths, Db2 relies on the following elements:

• Queries that use effective predicates.
• Indexes that support efficient data access.
• Statistics that describe the data sufficiently and accurately.

Theses elements are among the foundations good query performance. It is best to verify them before you try to apply special methods to influence access path selection.

Tip: Query tuning capabilities that can help you with this task, such as visual explain and statistics advisor, are available in IBM® Db2 Administration Foundation for z/OS® and IBM Db2 for z/OS Developer Extension.

To manage query access paths:

1. Code queries with predicates that support efficient access path selection.
   For more information, see Writing efficient SQL queries.
2. Ensure that accurate statistics exist to enforce the access paths for your queries.
   For more information, see Maintaining Db2 database statistics.
   In particular, you can use optimization statistics feedback to identify missing and conflicting statistics that might prevent efficient access path selection. For more information, see Applying statistics recommendations to statistics profiles automatically.
   Certain query optimization tools can also help you to identify statistics to support efficient access paths for your queries.
3. Create indexes that support efficient access paths for your queries.
   For more information, see Designing indexes for performance. Certain query optimization tools can also help you to identify indexes that support your queries.
4. For exception queries, consider applying methods to influence access path selection.
   Important: Use the following actions only after you ensure that the foundations of efficient access path selection, which are described in the previous steps, are applied.

   You might take the following actions to influence access paths, depending on the specific situation:
   • Enable queries to be reoptimized at run time, when literal values for parameter markers, host variables, and special registers might be known.
   • Specify optimization parameters at the statement level.
   • Override the selectivity used for predicates that are difficult or impossible to estimate by creating statement level predicate selectivity overrides.
   • Specify an access path at the statement level.
   • Specify an access path in a PLAN_TABLE instance.
   • Add special purpose predicates to the query and apply other special methods.