Differences between section explain and EXPLAIN statement output

The results obtained after issuing a section explain are similar to those collected after running the EXPLAIN statement. There are slight differences which are described per affected explain table and by the implications, if any, to the output generated by the db2exfmt utility.

The stored procedure output parameters EXPLAIN_REQUESTER, EXPLAIN_TIME, SOURCE_NAME, SOURCE_SCHEMA, and SOURCE_VERSION comprise the key used to look up the information for the section in the explain tables. Use these parameters with any existing explain tools (for example, db2exfmt) to format the explain information retrieved from the section.

EXPLAIN_INSTANCE table

The following columns are set differently for the row generated by a section explain:
  • EXPLAIN_OPTION is set to value S
  • SNAPSHOT_TAKEN is always set to N
  • REMARKS is always NULL

EXPLAIN_STATEMENT table

When a section explain has generated an explain output, the EXPLAIN_LEVEL column is set to value S. It is important to note that the EXPLAIN_LEVEL column is part of the primary key of the table and part of the foreign key of most other EXPLAIN tables; hence, this EXPLAIN_LEVEL value will also be present in those other tables.

In the EXPLAIN_STATEMENT table, the remaining column values that are usually associated with a row with EXPLAIN_LEVEL = P, are instead present when EXPLAIN_LEVEL = S, with the exception of SNAPSHOT. SNAPSHOT is always NULL when EXPLAIN_LEVEL is S.

If the original statement was not available at the time the section explain was generated (for example, if the statement text was not provided to the EXPLAIN_FROM_DATA procedure), STATEMENT_TEXT is set to the string UNKNOWN when EXPLAIN_LEVEL is set to O.

In the db2exfmt output for a section explain, the following extra line is shown after the optimized statement:
Explain level:    Explain from section

EXPLAIN_OPERATOR table

Considering all of the columns recording a cost, only the TOTAL_COST and FIRST_ROW_COST columns are populated with a value after a section explain. All the other columns recording cost have a value of -1.

In the db2exfmt output for a section explain, the following differences are obtained:
  • In the access plan graph, the I/O cost is shown as NA
  • In the details for each operator, the only costs shown are Cumulative Total Cost and Cumulative First Row Cost

EXPLAIN_PREDICATE table

No differences.

EXPLAIN_ARGUMENT table

A small number of argument types are not written to the EXPLAIN_ARGUMENT table when a section explain is issued.

EXPLAIN_STREAM table

The following columns do not have values after a section explain:
  • SINGLE_NODE
  • PARTITION_COLUMNS
  • SEQUENCE_SIZES
The following column always has a value of -1 after a section explain:
  • PREDICATE_ID
The following columns will have values only for streams originating from a base table object or default to no value and -1 respectively after a section explain:
  • COLUMN_NAMES
  • COLUMN_COUNT

In the db2exfmt output for a section explain, the information from these listed columns is omitted from the Input Streams and Output Streams section for each operator when they do not have values, or have a value of -1.

EXPLAIN_OBJECT table

After issuing a section explain, the STATS_SRC column is always set to an empty string and the CREATE_TIME column is set to NULL.

The following columns always have values of -1 after a section explain:
  • COLUMN_COUNT
  • WIDTH
  • FIRSTKEYCARD
  • FIRST2KEYCARD
  • FIRST3KEYCARD
  • FIRST4KEYCARD
  • SEQUENTIAL_PAGES
  • DENSITY
  • AVERAGE_SEQUENCE_GAP
  • AVERAGE_SEQUENCE_FETCH_GAP
  • AVERAGE_SEQUENCE_PAGES
  • AVERAGE_SEQUENCE_FETCH_PAGES
  • AVERAGE_RANDOM_PAGES
  • AVERAGE_RANDOM_FETCH_PAGES
  • NUMRIDS
  • NUMRIDS_DELETED
  • NUM_EMPTY_LEAFS
  • ACTIVE_BLOCKS
  • NUM_DATA_PART
The following columns will also have values of -1 after a section explain for partitioned objects:
  • OVERHEAD
  • TRANSFER_RATE
  • PREFETCHSIZE

In the db2exfmt output for a section explain, the information from these listed columns is omitted from the per-table and per-index statistical information found near the end of the output.

Section explain does not include compiler-referenced objects in its output (that is, rows where OBJECT_TYPE starts with a +). These objects are not shown in the db2exfmt output.