Questions for investigating data access

You can focus your investigation of access paths that Db2 uses to process SQL statements by using the PLAN_TABLE data to answer certain questions.

Tip: You can generate diagrams of the access paths used for your SQL statements by using the visual explain capability in tools such as IBM® Db2 Administration Foundation for z/OS® and IBM Db2 for z/OS Developer Extension.

Begin program-specific programming interface information.

You can use the following questions to guide your initial analysis of the access paths when you analyze PLAN_TABLE data:

How are indexes used to access the data?
The ACCESSTYPE and MATCHOLS values contain information about the use of indexes in an access path.
  1. Is an index used? For information about interpreting index access, see Index access (ACCESSTYPE is 'I', 'IN', 'I1', 'N', 'NR', 'MX', or 'DX').
  2. How many indexes are used? For information about interpreting access through multiple indexes, see Multiple index access (ACCESSTYPE='M', 'MX', 'MI', 'MU', 'DX', 'DI', or 'DU').
  3. How many index columns are used in matching? For more information about finding the number of matching index columns, see Matching index scan (MATCHCOLS>0).
  4. Is the query satisfied by the index alone? For more information about analyzing index-only access, see Index-only access (INDEXONLY='Y').
  5. How many index screening columns are used? For more information about analyzing index screening, see Index screening.
Is direct row access used?
Direct row access can only be used only when the table contains a column of the ROWID data type. For information about direct row access, see Direct row access (PRIMARY_ACCESSTYPE='D') and ROWID data type.
What possibly costly operations are used?
  1. Is a view or nested table expression materialized? For more information about analyzing materialization, see View and nested table expression access.
  2. Was a scan limited to certain partitions? For information about analyzing the use of page-range screening, see Prefetch access paths (PREFETCH='D', 'S', 'L', or 'U').
  3. What prefetch type is expected? For information about analyzing the use of prefetch, see Prefetch access paths (PREFETCH='D', 'S', 'L', or 'U').
  4. Is data accessed or processed in parallel? For information about analyzing the use of parallelism in the access path, see Parallel processing access (PARALLELISM_MODE='C').
  5. Is data sorted? For information about analyzing the use of sort operations, see Sort access.
  6. Is a subquery transformed to a join? For information about analyzing subquery access, see Subquery access.
  7. When are aggregate functions evaluated? For information about analyzing when Db2 evaluates aggregate functions, see Aggregate function access (COLUMN_FN_EVAL).
  8. Is a complex trigger WHEN clause used? For information about identifying the use of triggers in a WHEN clause, see Complex trigger WHEN clause access (QBLOCKTYPE='TRIGGR').
What are the object dependencies for the access path?
Access paths have dependencies on the objects that are identified in the TNAME and ACCESSNAME columns of the PLAN_TABLE. However, access paths might also depend on objects that Db2 does not actually use when it processes the selected access paths. Such dependencies are not be shown in EXPLAIN output, but they are recorded in the SYSPACKDEP catalog table or the SYSDYNQRYDEP catalog table.

End program-specific programming interface information.