EXPLAIN_STATEMENT table

The EXPLAIN_STATEMENT table contains the text of the SQL statement as it exists for the different levels of Explain information.

The original SQL statement as entered by the user is stored in this table along with the version used (by the optimizer) to choose an access plan to satisfy the SQL statement. The latter version may bear little resemblance to the original as it may have been rewritten or enhanced with additional predicates as determined by the SQL Compiler. In addition, if statement concentrator is enabled and the statement was changed as a result of statement concentrator, the effective SQL statement will also be stored in this table. This statement will resemble the original statement except that the literal values will be replaced with system generated named parameter markers. The plan information will be based on the effective statement in this case.

Table 1. EXPLAIN_STATEMENT Table. PK means that the column is part of a primary key; FK means that the column is part of a foreign key.
Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER VARCHAR(128) No PK, FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No PK, FK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) No PK, FK Name of the package running when the dynamic statement was explained or name of the source file when the static SQL was explained.
SOURCE_SCHEMA VARCHAR(128) No PK, FK Schema, or qualifier, of source of Explain request.
SOURCE_VERSION VARCHAR(64) No PK, FK Version of the source of the Explain request.
EXPLAIN_LEVEL CHAR(1) No PK Level of Explain information for which this row is relevant.
Valid values are:
E
Effective SQL text
F
Statement with row and column access control applied, before optimization
O
Original Text (as entered by user)
P
PLAN SELECTION
S
Section Explain
STMTNO INTEGER No PK Statement number within package to which this explain information is related. Set to 1 for dynamic Explain SQL statements. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view.
SECTNO INTEGER No PK Section number within package that contains this SQL statement. For dynamic Explain SQL statements, this is the section number used to hold the section for this statement at runtime. For static SQL statements, this value is the same as the value used for the SYSCAT.STATEMENTS catalog view.
QUERYNO INTEGER No No Numeric identifier for explained SQL statement. For dynamic SQL statements (excluding the EXPLAIN SQL statement) issued through CLP or CLI, the default value is a sequentially incremented value. Otherwise, the default value is the value of STMTNO for static SQL statements and 1 for dynamic SQL statements.
QUERYTAG CHAR(20) No No Identifier tag for each explained SQL statement. For dynamic SQL statements issued through CLP (excluding the EXPLAIN SQL statement), the default value is CLP. For dynamic SQL statements issued through CLI (excluding the EXPLAIN SQL statement), the default value is CLI. Otherwise, the default value used is blanks.
STATEMENT_TYPE CHAR(2) No No Descriptive label for type of query being explained.
Possible values are:
CL
Call
CP
Compound SQL (Dynamic)
D
Delete
DC
Delete where current of cursor
I
Insert
M
Merge
S
Select
SI
Set Integrity or Refresh Table
U
Update
UC
Update where current of cursor
UPDATABLE CHAR(1) No No Indicates if this statement is considered updatable. This is particularly relevant to SELECT statements which may be determined to be potentially updatable.
Possible values are:
' '
Not applicable (blank)
N
No
Y
Yes
DELETABLE CHAR(1) No No Indicates if this statement is considered deletable. This is particularly relevant to SELECT statements which may be determined to be potentially deletable.
Possible values are:
' '
Not applicable (blank)
N
No
Y
Yes
TOTAL_COST DOUBLE No No Estimated total cost (in timerons) of executing the chosen access plan for this statement; set to 0 (zero) if EXPLAIN_LEVEL is O or E (original or effective text) since no access plan has been chosen at this time.
STATEMENT_TEXT CLOB(2M) No No Text or portion of the text of the SQL statement being explained. The text shown for the Plan Selection or Section Explain levels of Explain has been reconstructed from the internal representation and is SQL-like in nature; that is, the reconstructed statement is not guaranteed to follow correct SQL syntax.
SNAPSHOT BLOB(10M) Yes No Snapshot of internal representation for this SQL statement at the Explain_Level shown.

Column is set to NULL if EXPLAIN_LEVEL is not P (Plan Selection) since no access plan has been chosen at the time that this specific version of the statement is captured.

QUERY_DEGREE INTEGER No No Indicates the degree of intrapartition parallelism at the time of Explain invocation. For the original statement, this contains the directed degree of intrapartition parallelism. Otherwise, this contains the degree of intrapartition parallelism generated for the plan to use.
EXPLAIN_FORMAT_TEXT CLOB(2G) Yes No Contains the formatted data of the explain tables that can be populated by using the SYSPROC.EXPLAIN_FORMAT stored procedure.