When information about an SQL statement is captured into
EXPLAIN tables, you can find relevant statistics and access path information
from across the set of EXPLAIN tables. To find this information for
a specific statement, use certain join predicates.
Procedure
To correlate information across the EXPLAIN tables:
- Examine the STMT_TXT column of DSN_STATEMENT_CACHE_TABLE
and find the corresponding values in the STMTID and CACHED_TS columns.
- Use join predicates to correlate the various EXPLAIN tables. For EXPLAIN information that is generated by the EXPLAIN STMTCACHE
statements:
- Use the following predicates to join the DSN_STATEMENT_CACHE_TABLE
and the PLAN_TABLE:
DSN_STATEMENT_CACHE_TABLE.STMTID = PLAN_TABLE.QUERYNO AND
DSN_STATEMENT_CACHE_TABLE.CACHED_TS = PLAN_TABLE.BIND_TIME
- Use the following predicates to join the DSN_STATEMENT_CACHE_TABLE
and other EXPLAIN tables, such a DSN_FUNCTION_TABLE or DSN_STATEMNT_TABLE:
DSN_STATEMENT_CACHE_TABLE.STMTID = explain-table-name.QUERYNO AND
DSN_STATEMENT_CACHE_TABLE.CACHED_TS = explain-table-name.EXPLAIN_TIME
For EXPLAIN information that is generated by the EXPLAIN MODE
special register:- Use the following predicate, which uses the EXPLAIN_TS column
of DSN_STATEMENT_CACHE_TABLE instead of the CACHED_TS column, to join
the DSN_STATEMENT_CACHE_TABLE and the PLAN_TABLE:
DSN_STATEMENT_CACHE_TABLE.STMTID = PLAN_TABLE.QUERYNO AND
DSN_STATEMENT_CACHE_TABLE.EXPLAIN_TS = PLAN_TABLE.BIND_TIME
- Use the following predicate to join the DSN_STATEMENT_CACHE_TABLE
and other EXPLAIN tables, such as DSN_FUNCTION_TABLE or DSN_STATEMNT_TABLE.
DSN_STATEMENT_CACHE_TABLE.STMTID = explain-table-name.QUERYNO AND
DSN_STATEMENT_CACHE_TABLE.EXPLAIN_TS = explain-table-name.EXPLAIN_TIME