The EXPLAIN_INSTANCE table is the main control table for all Explain information. Each row of data in the Explain tables is explicitly linked to one unique row in this table. The EXPLAIN_INSTANCE table gives basic information about the source of the SQL statements being explained as well as information about the environment in which the explanation took place.
For the definition of this table, see "EXPLAIN_INSTANCE Table Definition".
Table 123. EXPLAIN_INSTANCE Table
Column Name | Data Type | Nullable? | Key? | Description |
---|---|---|---|---|
EXPLAIN_REQUESTER | CHAR(8) | No | PK | Authorization ID of initiator of this Explain request. |
EXPLAIN_TIME | TIMESTAMP | No | PK | Time of initiation for Explain request. |
SOURCE_NAME | CHAR(8) | No | PK | 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 | CHAR(8) | No | PK | Schema, or qualifier, of source of Explain request. |
EXPLAIN_OPTION | CHAR(1) | No | No | Indicates what Explain Information was requested for this request.
Possible values are:
|
SNAPSHOT_TAKEN | CHAR(1) | No | No | Indicates whether an Explain Snapshot was taken for this request.
Possible values are:
|
DB2_VERSION | CHAR(7) | No | No | Product release number for DB2 Universal Database which processed this
explain request. Format is vv.rr.m, where:
|
SQL_TYPE | CHAR(1) | No | No | Indicates whether the Explain Instance was for static or dynamic
SQL.
Possible values are:
|
QUERYOPT | INTEGER | No | No | Indicates the query optimization class used by the SQL Compiler at the time of the Explain invocation. The value indicates what level of query optimization was performed by the SQL Compiler for the SQL statements being explained. |
BLOCK | CHAR(1) | No | No | Indicates what type of cursor blocking was used when compiling the SQL
statements. For more information, see the BLOCK column in
SYSCAT.PACKAGES.
Possible values are:
|
ISOLATION | CHAR(2) | No | No | Indicates what type of isolation was used when compiling the SQL
statements. For more information, see the ISOLATION column in
SYSCAT.PACKAGES.
Possible values are:
|
BUFFPAGE | INTEGER | No | No | Contains the value of the BUFFPAGE database configuration setting at the time of the Explain invocation. |
AVG_APPLS | INTEGER | No | No | Contains the value of the AVG_APPLS configuration parameter at the time of the Explain invocation. |
SORTHEAP | INTEGER | No | No | Contains the value of the SORTHEAP database configuration setting at the time of the Explain invocation. |
LOCKLIST | INTEGER | No | No | Contains the value of the LOCKLIST database configuration setting at the time of the Explain invocation. |
MAXLOCKS | SMALLINT | No | No | Contains the value of the MAXLOCKS database configuration setting at the time of the Explain invocation. |
LOCKS_AVAIL | INTEGER | No | No | Contains the number of locks assumed to be available by the optimizer for each user. (Derived from LOCKLIST and MAXLOCKS.) |
CPU_SPEED | DOUBLE | No | No | Contains the value of the CPUSPEED database manager configuration setting at the time of the Explain invocation. |
REMARKS | VARCHAR(254) | Yes | No | User-provided comment. |
DBHEAP | INTEGER | No | No | Contains the value of the DBHEAP database configuration setting at the time of Explain invocation. |
COMM_SPEED | DOUBLE | No | No | Contains the value of the COMM_BANDWIDTH database configuration setting at the time of Explain invocation. |
PARALLELISM | CHAR(2) | No | No |
Possible values are:
|