Correlating information across EXPLAIN tables

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.

To correlate information across the EXPLAIN tables:

1. Examine the STMT_TXT column of DSN_STATEMENT_CACHE_TABLE and find the corresponding values in the STMTID, CACHED_TS, and EXPANSION_REASON columns.
2. 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 AND
     DSN_STATEMENT_CACHE_TABLE.EXPANSION_REASON = PLAN_TABLE.EXPANSION_REASON

   • 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.BIND_TIME AND
     DSN_STATEMENT_CACHE_TABLE.EXPANSION_REASON = explain-table-name.EXPANSION_REASON

   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 AND
     DSN_STATEMENT_CACHE_TABLE.EXPANSION_REASON = PLAN_TABLE.EXPANSION_REASON

   • 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.BIND_TIME AND
     DSN_STATEMENT_CACHE_TABLE.EXPANSION_REASON = explain-table-name.EXPANSION_REASON

     DSN_STATEMENT_CACHE_TABLE.STMTID = explain-table-name.QUERYNO AND 
     DSN_STATEMENT_CACHE_TABLE.EXPLAIN_TS = explain-table-name.EXPLAIN_TIME