hongtaiyang/runtime/server/properties/db2explain.sql

CREATE TABLE EXPLAIN_INSTANCE ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                SOURCE_NAME       VARCHAR(128) NOT NULL,
                                SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                SOURCE_VERSION    VARCHAR(64)  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,
                                DATAJOINER        CHAR(1)   NOT NULL,
                                    PRIMARY KEY (EXPLAIN_REQUESTER,
                                                 EXPLAIN_TIME,
                                                 SOURCE_NAME,
                                                 SOURCE_SCHEMA,
                                                 SOURCE_VERSION));

CREATE TABLE EXPLAIN_STATEMENT ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                 EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                 SOURCE_NAME       VARCHAR(128) NOT NULL,
                                 SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                 SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                                 EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                                 STMTNO            INTEGER      NOT NULL,
                                 SECTNO            INTEGER      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(2M)     NOT NULL NOT LOGGED,
                                 SNAPSHOT          BLOB(10M)    NOT LOGGED,
                                 QUERY_DEGREE      INTEGER      NOT NULL,
                                    PRIMARY KEY (EXPLAIN_REQUESTER,
                                                 EXPLAIN_TIME,
                                                 SOURCE_NAME,
                                                 SOURCE_SCHEMA,
                                                 SOURCE_VERSION,
                                                 EXPLAIN_LEVEL,
                                                 STMTNO,
                                                 SECTNO),
                                     FOREIGN KEY (EXPLAIN_REQUESTER,
                                                  EXPLAIN_TIME,
                                                  SOURCE_NAME,
                                                  SOURCE_SCHEMA,
                                                  SOURCE_VERSION)
                                     REFERENCES EXPLAIN_INSTANCE
                                     ON DELETE CASCADE);

CREATE TABLE EXPLAIN_ARGUMENT ( EXPLAIN_REQUESTER   VARCHAR(128)  NOT NULL,
                                EXPLAIN_TIME        TIMESTAMP     NOT NULL,
                                SOURCE_NAME         VARCHAR(128)  NOT NULL,
                                SOURCE_SCHEMA       VARCHAR(128)  NOT NULL,
                                SOURCE_VERSION      VARCHAR(64)   NOT NULL,
                                EXPLAIN_LEVEL       CHAR(1)       NOT NULL,
                                STMTNO              INTEGER       NOT NULL,
                                SECTNO              INTEGER       NOT NULL,
                                OPERATOR_ID         INTEGER       NOT NULL,
                                ARGUMENT_TYPE       CHAR(8)       NOT NULL,
                                ARGUMENT_VALUE      VARCHAR(1024),
                                LONG_ARGUMENT_VALUE CLOB(2M)      NOT LOGGED,
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);
                                   
CREATE TABLE EXPLAIN_OBJECT ( EXPLAIN_REQUESTER    VARCHAR(128) NOT NULL,
                              EXPLAIN_TIME         TIMESTAMP    NOT NULL,
                              SOURCE_NAME          VARCHAR(128) NOT NULL,
                              SOURCE_SCHEMA        VARCHAR(128) NOT NULL,
                              SOURCE_VERSION       VARCHAR(64)  NOT NULL,
                              EXPLAIN_LEVEL        CHAR(1)      NOT NULL,
                              STMTNO               INTEGER      NOT NULL,
                              SECTNO               INTEGER      NOT NULL,
                              OBJECT_SCHEMA        VARCHAR(128) NOT NULL,
                              OBJECT_NAME          VARCHAR(128) NOT NULL,
                              OBJECT_TYPE          CHAR(2)      NOT NULL,
                              CREATE_TIME          TIMESTAMP,
                              STATISTICS_TIME      TIMESTAMP,
                              COLUMN_COUNT         SMALLINT     NOT NULL,
                              ROW_COUNT            BIGINT       NOT NULL,
                              WIDTH                INTEGER      NOT NULL,
                              PAGES                BIGINT       NOT NULL,
                              DISTINCT             CHAR(1)      NOT NULL,
                              TABLESPACE_NAME      VARCHAR(128),
                              OVERHEAD             DOUBLE       NOT NULL,
                              TRANSFER_RATE        DOUBLE       NOT NULL,
                              PREFETCHSIZE         INTEGER      NOT NULL,
                              EXTENTSIZE           INTEGER      NOT NULL,
                              CLUSTER              DOUBLE       NOT NULL,
                              NLEAF                BIGINT       NOT NULL,
                              NLEVELS              INTEGER      NOT NULL,
                              FULLKEYCARD          BIGINT       NOT NULL,
                              OVERFLOW             BIGINT       NOT NULL,
                              FIRSTKEYCARD         BIGINT       NOT NULL,
                              FIRST2KEYCARD        BIGINT       NOT NULL,
                              FIRST3KEYCARD        BIGINT       NOT NULL,
                              FIRST4KEYCARD        BIGINT       NOT NULL,
                              SEQUENTIAL_PAGES     BIGINT       NOT NULL,
                              DENSITY              INTEGER      NOT NULL,
                              STATS_SRC            CHAR(1)      NOT NULL,
                              AVERAGE_SEQUENCE_GAP          DOUBLE  NOT NULL,
                              AVERAGE_SEQUENCE_FETCH_GAP    DOUBLE  NOT NULL,
                              AVERAGE_SEQUENCE_PAGES        DOUBLE  NOT NULL,
                              AVERAGE_SEQUENCE_FETCH_PAGES  DOUBLE  NOT NULL,
                              AVERAGE_RANDOM_PAGES          DOUBLE  NOT NULL,
                              AVERAGE_RANDOM_FETCH_PAGES    DOUBLE  NOT NULL,
                              NUMRIDS                       BIGINT  NOT NULL,
                              NUMRIDS_DELETED               BIGINT  NOT NULL,
                              NUM_EMPTY_LEAFS               BIGINT  NOT NULL,
                              ACTIVE_BLOCKS                 BIGINT  NOT NULL,
                              NUM_DATA_PARTS                INTEGER  NOT NULL,
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);

CREATE TABLE EXPLAIN_OPERATOR ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                SOURCE_NAME       VARCHAR(128) NOT NULL,
                                SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                                EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                                STMTNO            INTEGER      NOT NULL,
                                SECTNO            INTEGER      NOT NULL,
                                OPERATOR_ID       INTEGER      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,
                                BUFFERS           DOUBLE       NOT NULL,
                                REMOTE_TOTAL_COST DOUBLE       NOT NULL,
                                REMOTE_COMM_COST  DOUBLE       NOT NULL,
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);

CREATE TABLE EXPLAIN_PREDICATE ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                 EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                 SOURCE_NAME       VARCHAR(128) NOT NULL,
                                 SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                 SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                                 EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                                 STMTNO            INTEGER      NOT NULL,
                                 SECTNO            INTEGER      NOT NULL,
                                 OPERATOR_ID       INTEGER      NOT NULL,
                                 PREDICATE_ID      INTEGER      NOT NULL,
                                 HOW_APPLIED       CHAR(10)     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(2M)     NOT LOGGED,
                                 RANGE_NUM         INTEGER,
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);

CREATE TABLE EXPLAIN_STREAM ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                              EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                              SOURCE_NAME       VARCHAR(128) NOT NULL,
                              SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                              SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                              EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                              STMTNO            INTEGER      NOT NULL,
                              SECTNO            INTEGER      NOT NULL,
                              STREAM_ID         INTEGER      NOT NULL,
                              SOURCE_TYPE       CHAR(1)      NOT NULL,
                              SOURCE_ID         INTEGER      NOT NULL,
                              TARGET_TYPE       CHAR(1)      NOT NULL,
                              TARGET_ID         INTEGER      NOT NULL,
                              OBJECT_SCHEMA     VARCHAR(128),
                              OBJECT_NAME       VARCHAR(128),
                              STREAM_COUNT      DOUBLE       NOT NULL,
                              COLUMN_COUNT      SMALLINT     NOT NULL,
                              PREDICATE_ID      INTEGER      NOT NULL,
                              COLUMN_NAMES      CLOB(2M)     NOT LOGGED,
                              PMID              SMALLINT     NOT NULL,
                              SINGLE_NODE       CHAR(5),
                              PARTITION_COLUMNS CLOB(2M)     NOT LOGGED,
                              SEQUENCE_SIZES    CLOB(2M)     NOT LOGGED,
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);

CREATE TABLE EXPLAIN_DIAGNOSTIC ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                  EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                  SOURCE_NAME       VARCHAR(128) NOT NULL,
                                  SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                  SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                                  EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                                  STMTNO            INTEGER      NOT NULL,
                                  SECTNO            INTEGER      NOT NULL,
                                  DIAGNOSTIC_ID     INTEGER      NOT NULL,
                                  CODE              INTEGER      NOT NULL,
                                   PRIMARY KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO,
                                                DIAGNOSTIC_ID),
                                   FOREIGN KEY (EXPLAIN_REQUESTER,
                                                EXPLAIN_TIME,
                                                SOURCE_NAME,
                                                SOURCE_SCHEMA,
                                                SOURCE_VERSION,
                                                EXPLAIN_LEVEL,
                                                STMTNO,
                                                SECTNO)
                                   REFERENCES EXPLAIN_STATEMENT
                                   ON DELETE CASCADE);

CREATE TABLE EXPLAIN_DIAGNOSTIC_DATA ( EXPLAIN_REQUESTER VARCHAR(128) NOT NULL,
                                       EXPLAIN_TIME      TIMESTAMP    NOT NULL,
                                       SOURCE_NAME       VARCHAR(128) NOT NULL,
                                       SOURCE_SCHEMA     VARCHAR(128) NOT NULL,
                                       SOURCE_VERSION    VARCHAR(64)  NOT NULL,
                                       EXPLAIN_LEVEL     CHAR(1)      NOT NULL,
                                       STMTNO            INTEGER      NOT NULL,
                                       SECTNO            INTEGER      NOT NULL,
                                       DIAGNOSTIC_ID     INTEGER      NOT NULL,
                                       ORDINAL           INTEGER      NOT NULL,
                                       TOKEN             VARCHAR(1000),
                                       TOKEN_LONG        BLOB(3M)     NOT LOGGED,
                                          FOREIGN KEY (EXPLAIN_REQUESTER,
                                                       EXPLAIN_TIME,
                                                       SOURCE_NAME,
                                                       SOURCE_SCHEMA,
                                                       SOURCE_VERSION,
                                                       EXPLAIN_LEVEL,
                                                       STMTNO,
                                                       SECTNO,
                                                       DIAGNOSTIC_ID)
                                          REFERENCES EXPLAIN_DIAGNOSTIC
                                          ON DELETE CASCADE);

CREATE TABLE ADVISE_INSTANCE (
        START_TIME        TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        END_TIME          TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        MODE              VARCHAR(4)    NOT NULL WITH DEFAULT '',
        WKLD_COMPRESSION  CHAR(4)       NOT NULL WITH DEFAULT 'NONE',
        STATUS            CHAR(9)       NOT NULL WITH DEFAULT '',
                                    PRIMARY KEY (START_TIME));

CREATE TABLE ADVISE_INDEX(
        EXPLAIN_REQUESTER VARCHAR(128)  NOT NULL WITH DEFAULT '',
        EXPLAIN_TIME      TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        SOURCE_NAME       VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_SCHEMA     VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_VERSION    VARCHAR(64)   NOT NULL WITH DEFAULT '',
        EXPLAIN_LEVEL     CHAR(1)       NOT NULL WITH DEFAULT '',
        STMTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        SECTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        QUERYNO           INTEGER       NOT NULL WITH DEFAULT 0,
        QUERYTAG          CHAR(20)      NOT NULL WITH DEFAULT '',
        NAME              VARCHAR(128)  NOT NULL,
        CREATOR           VARCHAR(128)  NOT NULL WITH DEFAULT '',
        TBNAME            VARCHAR(128)  NOT NULL,
        TBCREATOR         VARCHAR(128)  NOT NULL WITH DEFAULT '',
        COLNAMES          CLOB(2M)      NOT NULL,
        UNIQUERULE        CHAR(1)       NOT NULL WITH DEFAULT '',
        COLCOUNT          SMALLINT      NOT NULL WITH DEFAULT 0,
        IID               SMALLINT      NOT NULL WITH DEFAULT 0,
        NLEAF             INTEGER       NOT NULL WITH DEFAULT 0,
        NLEVELS           SMALLINT      NOT NULL WITH DEFAULT 0,
        FIRSTKEYCARD      BIGINT        NOT NULL WITH DEFAULT 0,
        FULLKEYCARD       BIGINT        NOT NULL WITH DEFAULT 0,
        CLUSTERRATIO      SMALLINT      NOT NULL WITH DEFAULT 0,
        CLUSTERFACTOR     DOUBLE        NOT NULL WITH DEFAULT 0,
        USERDEFINED       SMALLINT      NOT NULL WITH DEFAULT 0,
        SYSTEM_REQUIRED   SMALLINT      NOT NULL WITH DEFAULT 0,
        CREATE_TIME       TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        STATS_TIME        TIMESTAMP              WITH DEFAULT CURRENT TIMESTAMP,
        PAGE_FETCH_PAIRS  VARCHAR(520)  NOT NULL WITH DEFAULT '',
        REMARKS           VARCHAR(254)           WITH DEFAULT '',
        DEFINER           VARCHAR(128)  NOT NULL WITH DEFAULT '',
        CONVERTED         CHAR(1)       NOT NULL WITH DEFAULT '',
        SEQUENTIAL_PAGES  INTEGER       NOT NULL WITH DEFAULT 0,
        DENSITY           INTEGER       NOT NULL WITH DEFAULT 0,
        FIRST2KEYCARD     BIGINT        NOT NULL WITH DEFAULT 0,
        FIRST3KEYCARD     BIGINT        NOT NULL WITH DEFAULT 0,
        FIRST4KEYCARD     BIGINT        NOT NULL WITH DEFAULT 0,
        PCTFREE           SMALLINT      NOT NULL WITH DEFAULT -1,
        UNIQUE_COLCOUNT   SMALLINT      NOT NULL WITH DEFAULT -1,
        MINPCTUSED        SMALLINT      NOT NULL WITH DEFAULT 0,
        REVERSE_SCANS     CHAR(1)       NOT NULL WITH DEFAULT 'N',
        USE_INDEX         CHAR(1),
        CREATION_TEXT     CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        PACKED_DESC       BLOB(1M)               NOT LOGGED,
        RUN_ID            TIMESTAMP,
        INDEXTYPE         VARCHAR(4)    NOT NULL WITH DEFAULT '',
        EXISTS            CHAR(1)       NOT NULL WITH DEFAULT 'N',
        RIDTOBLOCK        CHAR(1)       NOT NULL WITH DEFAULT 'N',
        AVGPARTITION_CLUSTERRATIO     SMALLINT     NOT NULL WITH DEFAULT 0,
        AVGPARTITION_CLUSTERFACTOR    DOUBLE       NOT NULL WITH DEFAULT 0,
        AVGPARTITION_PAGE_FETCH_PAIRS VARCHAR(520) NOT NULL WITH DEFAULT '',
        DATAPARTITION_CLUSTERFACTOR   DOUBLE       NOT NULL WITH DEFAULT 0,
                                    FOREIGN KEY (RUN_ID)
                                    REFERENCES ADVISE_INSTANCE (START_TIME)
                                    ON DELETE CASCADE);

CREATE TABLE ADVISE_WORKLOAD (
        WORKLOAD_NAME     CHAR(128)    NOT NULL WITH DEFAULT 'WK0',
        STATEMENT_NO      INTEGER      NOT NULL WITH DEFAULT 1,
        STATEMENT_TEXT    CLOB(2M)     NOT NULL NOT LOGGED,
        STATEMENT_TAG     VARCHAR(256) NOT NULL WITH DEFAULT '' ,
        FREQUENCY         INTEGER      NOT NULL WITH DEFAULT 1,
        IMPORTANCE        DOUBLE       NOT NULL WITH DEFAULT 1,
        WEIGHT            DOUBLE       NOT NULL WITH DEFAULT 1,
        COST_BEFORE       DOUBLE,
        COST_AFTER        DOUBLE,
        COMPILABLE        CHAR(17));

CREATE TABLE ADVISE_MQT (
        EXPLAIN_REQUESTER VARCHAR(128)  NOT NULL WITH DEFAULT '',
        EXPLAIN_TIME      TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        SOURCE_NAME       VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_SCHEMA     VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_VERSION    VARCHAR(64)   NOT NULL WITH DEFAULT '',
        EXPLAIN_LEVEL     CHAR(1)       NOT NULL WITH DEFAULT '',
        STMTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        SECTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        NAME              VARCHAR(128)  NOT NULL,
        CREATOR           VARCHAR(128)  NOT NULL WITH DEFAULT '',
        IID               SMALLINT      NOT NULL WITH DEFAULT 0,
        CREATE_TIME       TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        STATS_TIME        TIMESTAMP              WITH DEFAULT CURRENT TIMESTAMP,
        NUMROWS           DOUBLE        NOT NULL WITH DEFAULT 0,
        NUMCOLS           SMALLINT      NOT NULL WITH DEFAULT 0,
        ROWSIZE           DOUBLE        NOT NULL WITH DEFAULT 0,
        BENEFIT           FLOAT         NOT NULL WITH DEFAULT 0.0,
        USE_MQT           CHAR(1),
        MQT_SOURCE        CHAR(1),
        QUERY_TEXT        CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        CREATION_TEXT     CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        SAMPLE_TEXT       CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        COLSTATS          CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        EXTRA_INFO        BLOB(2M)      NOT NULL NOT LOGGED with default BLOB(''),
        TBSPACE           VARCHAR(128)  NOT NULL WITH DEFAULT '',
        RUN_ID            TIMESTAMP,
        REFRESH_TYPE      CHAR(1)       NOT NULL WITH DEFAULT '',
        EXISTS            CHAR(1)       NOT NULL WITH DEFAULT 'N',
                                    FOREIGN KEY (RUN_ID)
                                    REFERENCES ADVISE_INSTANCE (START_TIME)
                                    ON DELETE CASCADE);

CREATE TABLE ADVISE_PARTITION (
        EXPLAIN_REQUESTER VARCHAR(128)  NOT NULL WITH DEFAULT '',
        EXPLAIN_TIME      TIMESTAMP     NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
        SOURCE_NAME       VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_SCHEMA     VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SOURCE_VERSION    VARCHAR(64)   NOT NULL WITH DEFAULT '',
        EXPLAIN_LEVEL     CHAR(1)       NOT NULL WITH DEFAULT '',
        STMTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        SECTNO            INTEGER       NOT NULL WITH DEFAULT 0,
        QUERYNO           INTEGER       NOT NULL WITH DEFAULT 0,
        QUERYTAG          CHAR(20)      NOT NULL WITH DEFAULT '',
        TBNAME            VARCHAR(128)  NOT NULL,
        TBCREATOR         VARCHAR(128)  NOT NULL WITH DEFAULT '',
        PMID              SMALLINT      NOT NULL,
        TBSPACE           VARCHAR(128)  NOT NULL WITH DEFAULT '',
        COLNAMES          CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        COLCOUNT          SMALLINT      NOT NULL WITH DEFAULT 0,
        REPLICATE         CHAR(1)       NOT NULL WITH DEFAULT 'N',
        COST              DOUBLE        NOT NULL,
        USEIT             CHAR(1),
        RUN_ID            TIMESTAMP,
                                    FOREIGN KEY(RUN_ID)
                                    REFERENCES ADVISE_INSTANCE (START_TIME)
                                    ON DELETE CASCADE);

CREATE TABLE ADVISE_TABLE (
        RUN_ID            TIMESTAMP,
        TABLE_NAME        VARCHAR(128)  NOT NULL,
        TABLE_SCHEMA      VARCHAR(128)  NOT NULL WITH DEFAULT '',
        TABLESPACE        VARCHAR(128)  NOT NULL WITH DEFAULT '',
        SELECTION_FLAG    VARCHAR(8)    NOT NULL WITH DEFAULT '',
        TABLE_EXISTS      CHAR(1)       NOT NULL WITH DEFAULT '',
        USE_TABLE         CHAR(1)       NOT NULL WITH DEFAULT '',
        GEN_COLUMNS       CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        ORGANIZE_BY       CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        CREATION_TEXT     CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        ALTER_COMMAND     CLOB(2M)      NOT NULL NOT LOGGED WITH DEFAULT '',
        DISKUSE           DOUBLE        NOT NULL WITH DEFAULT 0 ,
                                    FOREIGN KEY (RUN_ID)
                                    REFERENCES ADVISE_INSTANCE (START_TIME)
                                    ON DELETE CASCADE);
                                    
COMMIT WORK;

CREATE INDEX STMT_I1 on
   EXPLAIN_STATEMENT(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO);
CREATE INDEX ARG_I1 on
   EXPLAIN_ARGUMENT(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, OPERATOR_ID);
CREATE INDEX PRD_I1 on
   EXPLAIN_PREDICATE(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, OPERATOR_ID);
CREATE INDEX OPR_I1 on
   EXPLAIN_OPERATOR(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, OPERATOR_ID);
CREATE INDEX STM_I1 on
   EXPLAIN_STREAM(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO);
CREATE INDEX OBJ_I1 on
   EXPLAIN_OBJECT(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO);
CREATE INDEX EXP_DIAG_DAT_I1 on
   EXPLAIN_DIAGNOSTIC_DATA(EXPLAIN_TIME, EXPLAIN_LEVEL, STMTNO, SECTNO, DIAGNOSTIC_ID, ORDINAL);

CREATE INDEX IDX_I1 on
   ADVISE_INDEX (EXPLAIN_TIME);
CREATE INDEX IDX_I2 on
   ADVISE_INDEX (NAME, EXPLAIN_TIME);
CREATE INDEX MQT_I1 on
   ADVISE_MQT (EXPLAIN_TIME);
CREATE INDEX MQT_I2 on
   ADVISE_MQT (NAME,EXPLAIN_TIME);
CREATE INDEX PRT_I1 on
   ADVISE_PARTITION (EXPLAIN_TIME);

COMMIT WORK;

CREATE FUNCTION EXPLAIN_GET_MSGS( EXPLAIN_REQUESTER VARCHAR(128),
                                  EXPLAIN_TIME      TIMESTAMP,
                                  SOURCE_NAME       VARCHAR(128),
                                  SOURCE_SCHEMA     VARCHAR(128),
                                  SOURCE_VERSION    VARCHAR(64),
                                  EXPLAIN_LEVEL     CHAR(1),
                                  STMTNO            INTEGER,
                                  SECTNO            INTEGER,
                                  INLOCALE          VARCHAR(33) )
  RETURNS TABLE ( EXPLAIN_REQUESTER VARCHAR(128),
                  EXPLAIN_TIME      TIMESTAMP,
                  SOURCE_NAME       VARCHAR(128),
                  SOURCE_SCHEMA     VARCHAR(128),
                  SOURCE_VERSION    VARCHAR(64),
                  EXPLAIN_LEVEL     CHAR(1),
                  STMTNO            INTEGER,
                  SECTNO            INTEGER,
                  DIAGNOSTIC_ID     INTEGER,
                  LOCALE            VARCHAR(33),
                  MSG               VARCHAR(4096) )
  SPECIFIC EXPLAIN_GET_MSGS
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
  RETURN SELECT A.A_EXPLAIN_REQUESTER,
                A.A_EXPLAIN_TIME,
                A.A_SOURCE_NAME,
                A.A_SOURCE_SCHEMA,
                A.A_SOURCE_VERSION,
                A.A_EXPLAIN_LEVEL,
                A.A_STMTNO,
                A.A_SECTNO,
                A.A_DIAGNOSTIC_ID,
                F.LOCALE,
                F.MSG
         FROM EXPLAIN_DIAGNOSTIC A( A_EXPLAIN_REQUESTER,
                                    A_EXPLAIN_TIME,
                                    A_SOURCE_NAME,
                                    A_SOURCE_SCHEMA,
                                    A_SOURCE_VERSION,
                                    A_EXPLAIN_LEVEL,
                                    A_STMTNO,
                                    A_SECTNO,
                                    A_DIAGNOSTIC_ID,
                                    A_CODE ),
              TABLE( SYSPROC.EXPLAIN_GET_MSG2(
                       INLOCALE,
                       A.A_CODE,
                       ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B
                         WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                           AND A.A_EXPLAIN_TIME      = B.EXPLAIN_TIME
                           AND A.A_SOURCE_NAME       = B.SOURCE_NAME
                           AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA
                           AND A.A_SOURCE_VERSION    = B.SOURCE_VERSION
                           AND A.A_EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL
                           AND A.A_STMTNO            = B.STMTNO
                           AND A.A_SECTNO            = B.SECTNO
                           AND A.A_DIAGNOSTIC_ID     = B.DIAGNOSTIC_ID
                           AND B.ORDINAL=1 ),
                       ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B
                         WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                           AND A.A_EXPLAIN_TIME      = B.EXPLAIN_TIME
                           AND A.A_SOURCE_NAME       = B.SOURCE_NAME
                           AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA

                           AND A.A_SOURCE_VERSION    = B.SOURCE_VERSION
                           AND A.A_EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL
                           AND A.A_STMTNO            = B.STMTNO
                           AND A.A_SECTNO            = B.SECTNO
                           AND A.A_DIAGNOSTIC_ID     = B.DIAGNOSTIC_ID
                           AND B.ORDINAL=2 ),
                       ( SELECT TOKEN FROM EXPLAIN_DIAGNOSTIC_DATA B
                         WHERE A.A_EXPLAIN_REQUESTER = B.EXPLAIN_REQUESTER
                           AND A.A_EXPLAIN_TIME      = B.EXPLAIN_TIME
                           AND A.A_SOURCE_NAME       = B.SOURCE_NAME
                           AND A.A_SOURCE_SCHEMA     = B.SOURCE_SCHEMA
                           AND A.A_SOURCE_VERSION    = B.SOURCE_VERSION
                           AND A.A_EXPLAIN_LEVEL     = B.EXPLAIN_LEVEL
                           AND A.A_STMTNO            = B.STMTNO
                           AND A.A_SECTNO            = B.SECTNO
                           AND A.A_DIAGNOSTIC_ID     = B.DIAGNOSTIC_ID
                           AND B.ORDINAL=3 ) ) ) F
         WHERE ( EXPLAIN_REQUESTER IS NULL OR
                 EXPLAIN_REQUESTER = A.A_EXPLAIN_REQUESTER )
           AND ( EXPLAIN_TIME      IS NULL OR
                 EXPLAIN_TIME      = A.A_EXPLAIN_TIME      )
           AND ( SOURCE_NAME       IS NULL OR
                 SOURCE_NAME       = A.A_SOURCE_NAME       )
           AND ( SOURCE_SCHEMA     IS NULL OR
                 SOURCE_SCHEMA     = A.A_SOURCE_SCHEMA     )
           AND ( SOURCE_VERSION    IS NULL OR
                 SOURCE_VERSION    = A.A_SOURCE_VERSION    )
           AND ( EXPLAIN_LEVEL     IS NULL OR
                 EXPLAIN_LEVEL     = A.A_EXPLAIN_LEVEL     )
           AND ( STMTNO            IS NULL OR
                 STMTNO            = A.A_STMTNO            )
           AND ( SECTNO            IS NULL OR
                 SECTNO            = A.A_SECTNO            );

COMMIT WORK;