EXPLAIN_INSTANCE table

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.

Table 1. EXPLAIN_INSTANCE 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 Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No PK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) 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 VARCHAR(128) No PK Schema, or qualifier, of source of Explain request.
SOURCE_VERSION VARCHAR(64) No PK Version of the source of the Explain request.
EXPLAIN_OPTION CHAR(1) No No Indicates what Explain Information was requested for this request.
Possible values are:
P
PLAN SELECTION
S
Section Explain
SNAPSHOT_TAKEN CHAR(1) No No Indicates whether an Explain Snapshot was taken for this request.
Possible values are:
Y
Yes, an Explain Snapshot(s) was taken and stored in the EXPLAIN_STATEMENT table. Regular Explain information was also captured.
N
No Explain Snapshot was taken. Regular Explain information was captured.
O
Only an Explain Snapshot was taken. Regular Explain information was not captured.
DB2_VERSION CHAR(7) No No Release number for the Db2® product that processed this explain request. Format is: VV.RR.M., where:
vv
Version number
rr
Release number
m
Modification pack
To see the complete product signature information for an EXPLAIN instance, review BLDLEVEL argument row recorded in the EXPLAIN_ARGUMENT table.
SQL_TYPE CHAR(1) No No Indicates whether the Explain Instance was for static or dynamic SQL.
Possible values are:
S
Static SQL
D
Dynamic SQL
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.
Possible values are:
N
No Blocking
U
Block Unambiguous Cursors
B
Block All Cursors
ISOLATION CHAR(2) No No Indicates what type of isolation was used when compiling the SQL statements.
Possible values are:
RR
Repeatable Read
RS
Read Stability
CS
Cursor Stability
UR
Uncommitted Read
BUFFPAGE INTEGER No No Contains the value of the buffpage database configuration setting at the time of the Explain invocation.
Important: The buffpage database configuration is deprecated and might be remove in a future release.
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 parameter at the time of the Explain invocation.
LOCKLIST INTEGER No No Contains the value of the locklist database configuration parameter at the time of the Explain invocation.
MAXLOCKS SMALLINT No No Contains the value of the maxlocks database configuration parameter 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 parameter 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 parameter at the time of Explain invocation.
COMM_SPEED DOUBLE No No Contains the value of the comm_bandwidth database configuration parameter at the time of Explain invocation.
PARALLELISM CHAR(2) No No
Possible values are:
  • N = No parallelism
  • P = Intrapartition parallelism
  • IP = Interpartition parallelism
  • BP = Intrapartition parallelism and interpartition parallelism
DATAJOINER CHAR(1) No No
Possible values are:
  • N = Non-federated systems plan
  • Y = Federated systems plan
EXECUTABLE_ID VARCHAR(32) FOR BIT DATA Yes No A binary token generated on the data server that uniquely identifies the SQL statement section that was executed.
EXECUTION_TIME TIMESTAMP Yes No Time the section started execution.