All explain information is organized around the concept of an explain instance. An explain instance represents one invocation of the explain facility for one or more SQL statements. An explain instance represents the explain information for:
The explain information captured within one explain instance includes the SQL Compilation environment as well as the access plan chosen to satisfy the SQL statement being compiled. Explain information is organized into 3 subsets:
Explain instance information is stored in the EXPLAIN_INSTANCE table. Additional specific information about each SQL statement explained within an explain instance is stored in the EXPLAIN_STATEMENT table.
Explain Instance Identification: You can uniquely identify each explain instance and correlate the information for the SQL statements to a given invocation of the facility with this information:
Environmental Settings: Environmental information concerning how the SQL compiler optimized your queries is captured. The environmental information includes the following:
SQL Statement Identification: For each explain instance, multiple SQL statements may have been explained. Along with information that uniquely identifies the explain instance, the following information helps identify each individual SQL statement.
Within the EXPLAIN_STATEMENT table, the QUERYTAG and QUERYNO fields contain identifiers and are set for you as part of the explain process.
For dynamic explain SQL statements submitted during a CLP or CLI session, when EXPLAIN MODE or EXPLAIN SNAPSHOT is active, the QUERYTAG is set to "CLP" or "CLI". When this happens, the QUERYNO is defaulted to a number that is incremented by one or more for each statement.
For all other dynamic explain SQL statements (not from CLP, CLI, or using the EXPLAIN SQL statement) the QUERYTAG is set to blanks, and the QUERYNO will always be "1".
Cost Estimation: For each statement explained, an estimate of the relative cost of executing the chosen access plan is recorded. This cost is given using a made-up, relative unit of measure called timerons. Estimates of elapsed times are not provided, for the following reasons:
Statement Text: For each statement explained, two versions of the text of the SQL statement are recorded. One version is the text as received by the SQL Compiler. The other is a version of the statement text that has been reverse-translated from the internal compiler representation of the query. This translation, while looking similar to other SQL statements, does not necessarily follow correct SQL syntax nor does it necessarily reflect the actual content of the internal representation as a whole. This translation is provided simply to allow an understanding of the SQL context from which the SQL optimizer chose the access plan. Comparing the user-written statement text to the internal representation of the SQL statement can help you to understand how the SQL compiler has rewritten your query for better optimization. (See "Query Rewrite by the SQL Compiler".) It also shows you other elements in the environment affecting your statement such as triggers and constraints. Some keywords used by this "optimized" text are:
When an explain snapshot is requested, additional explain information is recorded describing the access plan selected by the SQL optimizer. This information is stored in the SNAPSHOT column of the EXPLAIN_STATEMENT table in the format required by Visual Explain. This format is not usable by other applications.
Additional information on the contents of the explain snapshot information is available from Visual Explain itself and in:
When explain table information is requested, additional information is recorded describing the access plan selected by the SQL optimizer. This information is stored in the following explain tables:
Each rectangular object node of Visual Explain corresponds to a row in the EXPLAIN_OBJECT table. Each octagonal "operator" node of Visual Explain corresponds to a row in the EXPLAIN_OPERATOR table. Each link between operators or operator's objects corresponds to a row of the EXPLAIN_STREAM table.
The explain table information is similar in content to that recorded for an explain snapshot, however, this information is stored in ordinary relational tables which can be accessed using standard SQL statements.
Explain tables, like the Visual Explain access plan graph, are designed to reflect the relationships between operators and data objects within the access plan. The following diagram shows the relationships between these tables.
Figure 52. Overview of Explain Table Relationships (not all tables are shown).
It is possible to have explain tables that are common to more than one user. The explain tables can be defined for one user. Aliases can then be defined using the same name for each additional user pointing to the defined tables. Each user sharing the common explain tables must have insert permission on those tables.
See Appendix N. "SQL Explain Tools" for more information on the Explain tables and how to create the tables. Additional information on the contents of the explain table information is available in:
The db2exfmt tool provided in the misc subdirectory under the sqllib directory can be used to format the contents of the explain tables into a legible, organized output.
[ DB2 List of Books | Search the DB2 Books ]