(Originally posted 2006-02-22.)
APAR PK15454 documents an extension to DB2’s EXPLAIN function which makes correlation of the various tables involved much easier. This enhancement is used by Visual Explain but can also be used by people querying the tables EXPLAIN produces, in particular DSN_FILTER_TABLE and DSN_PREDICAT_TABLE.</p.
When you EXPLAIN a statement it produces rows in the well-known PLAN_TABLE table, but also in these other two tables (and some other lesser-known tables).
To quote from the APAR description a number of problems were fixed…
- When an access path uses multiple index access, the output to DSN_FILTER_TABLE is ambiguous because the sequence number of a step in a multiple index operation is not available.
- For an INSERT within SELECT statement, in DSN_PREDICAT_TABLE the TABNO can be ambiguous between the select and insert part of the statement.
- The LITERALS column in DSN_PREDICAT_TABLE does not display SMALLINT properly.
- Slow performance when running Explain on a query and DSN_DETCOST_TABLE is defined.