Explain information for instances

The following information helps you to identify a specific explain instance and to associate the information about certain statements with a specific invocation of the explain facility:

• The user who requested the explain information
• When the explain request began
• The name of the package that contains the explained statement
• The SQL schema of the package that contains the explained statement
• The version of the package that contains the statement
• Whether snapshot information was collected

Information about the database manager environment in which the query compiler optimized your queries is captured. The environmental information includes the following:

• The version and release number of the Db2® product
• The degree of parallelism under which the query was compiled

  The CURRENT DEGREE special register, the DEGREE bind option, the SET RUNTIME DEGREE command, and the dft_degree database configuration parameter determine the degree of parallelism under which a particular query is compiled.

• Whether the statement is dynamic or static
• The query optimization class used to compile the query
• The type of row blocking for cursors that occurs when compiling the query
• The isolation level under which the query runs
• The values of various configuration parameters when the query was compiled. Values for the following parameters are recorded when an explain snapshot is taken:
  • Sort heap size (sortheap)
  • Average number of active applications (avg_appls)
  • Database heap (dbheap)
  • Maximum storage for lock list (locklist)
  • Maximum percent of lock list before escalation (maxlocks)
  • CPU speed (cpuspeed)
  • Communications bandwidth (comm_bandwidth)

More than one statement might have been explained for each explain instance. In addition to information that uniquely identifies the explain instance, the following information helps to identify individual query statements:

• The type of statement: SELECT, DELETE, INSERT, UPDATE, positioned DELETE, positioned UPDATE, or SET INTEGRITY
• The statement and section number of the package issuing the statement, as recorded in the SYSCAT.STATEMENTS catalog view

For each explained statement, the optimizer records an estimate of the relative cost of executing the chosen access plan. This cost is stated in an invented relative unit of measure called a timeron. No estimate of elapsed times is provided, for the following reasons:

• The query optimizer does not estimate elapsed time but only resource consumption.
• The optimizer does not model all factors that can affect elapsed time. It ignores factors that do not affect the efficiency of the access plan. A number of runtime factors affect the elapsed time, including the system workload, the amount of resource contention, the amount of parallel processing and I/O, the cost of returning rows to the user, and the communication time between the client and server.

Two versions of the statement text are recorded for each explained statement. One version is the code that the query compiler receives from the application. The other version is reverse-translated from the internal (compiler) representation of the query. Although this translation looks similar to other query statements, it does not necessarily follow correct query language syntax, nor does it necessarily reflect the actual content of the internal representation as a whole. This translation is provided only to enable you to understand the context in which the optimizer chose the access plan. To understand how the compiler has rewritten your query for better optimization, compare the user-written statement text to the internal representation of the query statement. The rewritten statement also shows you other factors that affect your statement, such as triggers or constraints. Some keywords that are used in this optimized text include the following:

$Cn

The name of a derived column, where n represents an integer value.

$CONSTRAINT$

This tag identifies a constraint that was added to the original statement during compilation, and is seen in conjunction with the $WITH_CONTEXT$ prefix.

$DERIVED.Tn

The name of a derived table, where n represents an integer value.

$INTERNAL_FUNC$

This tag indicates the presence of a function that is used by the compiler for the explained query but that is not available for general use.

$INTERNAL_PRED$

This tag indicates the presence of a predicate that was added during compilation of the explained query but that is not available for general use. An internal predicate is used by the compiler to satisfy additional context that is added to the original statement because of triggers or constraints.

$INTERNAL_XPATH$

This tag indicates the presence of an internal table function that takes a single annotated XPath pattern as an input parameter and returns a table with one or more columns that match that pattern.

$RID$

This tag identifies the row identifier (RID) column for a particular row.

$TRIGGER$

This tag identifies a trigger that was added to the original statement during compilation, and is seen in conjunction with the $WITH_CONTEXT$ prefix.

$WITH_CONTEXT$(...)

This prefix appears at the beginning of the text when additional triggers or constraints have been added to the original query statement. A list of the names of any triggers or constraints that affect the compilation and resolution of the statement appears after this prefix.