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 use tools such as IBM® Data Server Manager and related Db2 Tools for z/OS® solutions to simplify this task.
Tip: You can use the visual explain feature of tools such as IBM Data Server Manager to create visual diagrams of the access paths for your SQL statements.

Begin program-specific programming interface information.

However, if you do analyze PLAN_TABLE data directly, you can use the following questions to guide your initial analysis of the access paths:

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', '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) and The number of index columns used for matching (MATCHCOLS=n).
  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='I', or '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.

End program-specific programming interface information.