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 and/or enhanced with additional predicates as determined by the SQL Compiler.
For the definition of this table, see EXPLAIN_STATEMENT Table Definition.
Table 139. EXPLAIN_STATEMENT Table
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. |
EXPLAIN_LEVEL | CHAR(1) | No | PK | Level of Explain information for which this row is relevant.
Valid values are:
|
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:
|
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:
|
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:
|
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 (original text) since no access plan has been chosen at this time. |
STATEMENT_TEXT | CLOB(1M) | No | No | Text or portion of the text of the SQL statement being explained. The text shown for the Plan Selection level 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.
This column is intended for use with DB2 Visual Explain. Column is set to null if EXPLAIN_LEVEL is 0 (original statement) 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 intra-partition parallelism at the time of Explain invocation. For the original statement, this contains the directed degree of intra-partition parallelism. For the PLAN SELECTION, this contains the degree of intra-partition parallelism generated for the plan to use. |