SQL Reference

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.

For the definition of this table, see EXPLAIN_INSTANCE Table Definition.

Table 130. EXPLAIN_INSTANCE Table

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.
EXPLAIN_OPTION CHAR(1) No No Indicates what Explain Information was requested for this request.

Possible values are:

P
PLAN SELECTION

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 Product release number for DB2 Universal Database which processed this explain request. Format is vv.rr.m, where:

vv
Version Number
rr
Release Number
m
Maintenance Release Number

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. For more information, see the BLOCK column in SYSCAT.PACKAGES.
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. For more information, see the ISOLATION column in SYSCAT.PACKAGES.
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.
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:

N = No parallelism
P = Intra-partition parallelism
IP = Inter-partition parallelism
BP = Intra-partition parallelism and inter-partition parallelism

DATAJOINER CHAR(1) No No
Possible values are:

N = Non-federated systems plan
Y = Federated systems plan

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.
EXPLAIN_OPTION	CHAR(1)	No	No	Indicates what Explain Information was requested for this request. Possible values are: P PLAN SELECTION
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	Product release number for DB2 Universal Database which processed this explain request. Format is vv.rr.m, where: vv Version Number rr Release Number m Maintenance Release Number
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. For more information, see the BLOCK column in SYSCAT.PACKAGES. 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. For more information, see the ISOLATION column in SYSCAT.PACKAGES. 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.
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: N = No parallelism P = Intra-partition parallelism IP = Inter-partition parallelism BP = Intra-partition parallelism and inter-partition parallelism
DATAJOINER	CHAR(1)	No	No	Possible values are: N = Non-federated systems plan Y = Federated systems plan

[ Top of Page | Previous Page | Next Page ]