The Explain tables must be created before Explain can be invoked. The following definitions specify how to create the necessary Explain tables:
Alternately, create them by using the sample command line processor input script provided in the EXPLAIN.DDL file located in the 'misc' subdirectory of the 'sqllib' directory. Connect to the database where the Explain tables are required. Then issue the command: db2 -tf EXPLAIN.DDL and the tables will be created.
CREATE TABLE EXPLAIN_ARGUMENT ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
OPERATOR_ID SMALLINT NOT NULL,
ARGUMENT_TYPE CHAR(8) NOT NULL,
ARGUMENT_VALUE VARCHAR(30) NOT NULL,
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO)
REFERENCES EXPLAIN_STATEMENT
ON DELETE CASCADE )
CREATE TABLE EXPLAIN_INSTANCE ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_OPTION CHAR(1) NOT NULL,
SNAPSHOT_TAKEN CHAR(1) NOT NULL,
DB2_VERSION CHAR(7) NOT NULL,
SQL_TYPE CHAR(1) NOT NULL,
QUERYOPT INTEGER NOT NULL,
BLOCK CHAR(1) NOT NULL,
ISOLATION CHAR(2) NOT NULL,
BUFFPAGE INTEGER NOT NULL,
AVG_APPLS INTEGER NOT NULL,
SORTHEAP INTEGER NOT NULL,
LOCKLIST INTEGER NOT NULL,
MAXLOCKS SMALLINT NOT NULL,
LOCKS_AVAIL INTEGER NOT NULL,
CPU_SPEED DOUBLE NOT NULL,
REMARKS VARCHAR(254),
DBHEAP INTEGER NOT NULL,
COMM_SPEED DOUBLE NOT NULL,
PARALLELISM CHAR(2) NOT NULL,
PRIMARY KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA))
CREATE TABLE EXPLAIN_OBJECT ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
OBJECT_SCHEMA CHAR(8) NOT NULL,
OBJECT_NAME VARCHAR(18) NOT NULL,
OBJECT_TYPE CHAR(2) NOT NULL,
CREATE_TIME TIMESTAMP,
STATISTICS_TIME TIMESTAMP,
COLUMN_COUNT SMALLINT NOT NULL,
ROW_COUNT INTEGER NOT NULL,
WIDTH INTEGER NOT NULL,
PAGES INTEGER NOT NULL,
DISTINCT CHAR(1) NOT NULL,
TABLESPACE_NAME VARCHAR(18),
OVERHEAD DOUBLE NOT NULL,
TRANSFER_RATE DOUBLE NOT NULL,
PREFETCHSIZE INTEGER NOT NULL,
EXTENTSIZE INTEGER NOT NULL,
CLUSTER DOUBLE NOT NULL,
NLEAF INTEGER NOT NULL,
NLEVELS INTEGER NOT NULL,
FULLKEYCARD INTEGER NOT NULL,
OVERFLOW INTEGER NOT NULL,
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO)
REFERENCES EXPLAIN_STATEMENT
ON DELETE CASCADE )
CREATE TABLE EXPLAIN_OPERATOR ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
OPERATOR_ID SMALLINT NOT NULL,
OPERATOR_TYPE CHAR(6) NOT NULL,
TOTAL_COST DOUBLE NOT NULL,
IO_COST DOUBLE NOT NULL,
CPU_COST DOUBLE NOT NULL,
FIRST_ROW_COST DOUBLE NOT NULL,
RE_TOTAL_COST DOUBLE NOT NULL,
RE_IO_COST DOUBLE NOT NULL,
RE_CPU_COST DOUBLE NOT NULL,
COMM_COST DOUBLE NOT NULL,
FIRST_COMM_COST DOUBLE NOT NULL,
NODES_USED CLOB(64K) NOT LOGGED,
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO)
REFERENCES EXPLAIN_STATEMENT
ON DELETE CASCADE )
CREATE TABLE EXPLAIN_PREDICATE ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
OPERATOR_ID SMALLINT NOT NULL,
PREDICATE_ID SMALLINT NOT NULL,
HOW_APPLIED CHAR(5) NOT NULL,
WHEN_EVALUATED CHAR(3) NOT NULL,
RELOP_TYPE CHAR(2) NOT NULL,
SUBQUERY CHAR(1) NOT NULL,
FILTER_FACTOR DOUBLE NOT NULL,
PREDICATE_TEXT CLOB(64K) NOT LOGGED,
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO)
REFERENCES EXPLAIN_STATEMENT
ON DELETE CASCADE )
CREATE TABLE EXPLAIN_STATEMENT ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
QUERYNO INTEGER NOT NULL,
QUERYTAG CHAR(20) NOT NULL,
STATEMENT_TYPE CHAR(2) NOT NULL,
UPDATABLE CHAR(1) NOT NULL,
DELETABLE CHAR(1) NOT NULL
TOTAL_COST DOUBLE NOT NULL,
STATEMENT_TEXT CLOB(64K) NOT NULL NOT LOGGED,
SNAPSHOT BLOB(10M) NOT LOGGED,
QUERY_DEGREE INTEGER NOT NULL,
PRIMARY KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO),
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA)
REFERENCES EXPLAIN_INSTANCE
ON DELETE CASCADE )
CREATE TABLE EXPLAIN_STREAM ( EXPLAIN_REQUESTER CHAR(8) NOT NULL,
EXPLAIN_TIME TIMESTAMP NOT NULL,
SOURCE_NAME CHAR(8) NOT NULL,
SOURCE_SCHEMA CHAR(8) NOT NULL,
EXPLAIN_LEVEL CHAR(1) NOT NULL,
STMTNO SMALLINT NOT NULL,
SECTNO SMALLINT NOT NULL,
STREAM_ID SMALLINT NOT NULL,
SOURCE_TYPE CHAR(1) NOT NULL,
SOURCE_ID SMALLINT NOT NULL,
TARGET_TYPE CHAR(1) NOT NULL,
TARGET_ID SMALLINT NOT NULL,
OBJECT_SCHEMA CHAR(8),
OBJECT_NAME VARCHAR(18),
STREAM_COUNT DOUBLE NOT NULL,
COLUMN_COUNT SMALLINT NOT NULL,
PREDICATE_ID SMALLINT NOT NULL,
COLUMN_NAMES CLOB(64K) NOT LOGGED,
PMID SMALLINT NOT NULL,
SINGLE_NODE CHAR(5),
PARTITION_COLUMNS CLOB(64K) NOT LOGGED,
FOREIGN KEY (EXPLAIN_REQUESTER,
EXPLAIN_TIME,
SOURCE_NAME,
SOURCE_SCHEMA,
EXPLAIN_LEVEL,
STMTNO,
SECTNO)
REFERENCES EXPLAIN_STATEMENT
ON DELETE CASCADE )