-- Performance view to store historical performance data from
-- snapshot administrative view sysibmadm.snapstmt
-- Downloaded from http://www.ebenner.com/db2luwdba-howto
-- Please direct any questions to consulting@ebenner.com

create table dba.snapstmt (
SNAPSHOT_TIMESTAMP                TIMESTAMP NOT NULL,
DB_NAME                            VARCHAR(128) not null,
AGENT_ID                           BIGINT NOT NULL,
ROWS_READ                          BIGINT,
ROWS_WRITTEN                       BIGINT,
NUM_AGENTS                         BIGINT,
AGENTS_TOP                         BIGINT,
STMT_TYPE                          VARCHAR(20),
STMT_OPERATION                     VARCHAR(20),
SECTION_NUMBER                     BIGINT,
QUERY_COST_ESTIMATE                BIGINT,
QUERY_CARD_ESTIMATE                BIGINT,
DEGREE_PARALLELISM                 BIGINT,
STMT_SORTS                         BIGINT,
TOTAL_SORT_TIME                    BIGINT,
SORT_OVERFLOWS                     BIGINT,
INT_ROWS_DELETED                   BIGINT,
INT_ROWS_UPDATED                   BIGINT,
INT_ROWS_INSERTED                  BIGINT,
FETCH_COUNT                        BIGINT,
STMT_START                        TIMESTAMP,
STMT_STOP                         TIMESTAMP,
STMT_USR_CPU_TIME_S                BIGINT,
STMT_USR_CPU_TIME_MS               BIGINT,
STMT_SYS_CPU_TIME_S                BIGINT,
STMT_SYS_CPU_TIME_MS               BIGINT,
STMT_ELAPSED_TIME_S                BIGINT,
STMT_ELAPSED_TIME_MS               BIGINT,
BLOCKING_CURSOR                    SMALLINT,
STMT_NODE_NUMBER                   SMALLINT,
CURSOR_NAME                        VARCHAR(128),
CREATOR                            VARCHAR(128),
PACKAGE_NAME                       VARCHAR(128 ),
STMT_TEXT                          CLOB(16777216),
CONSISTENCY_TOKEN                  VARCHAR(128 ),
PACKAGE_VERSION_ID                 VARCHAR(128),
POOL_DATA_L_READS                  BIGINT,
POOL_DATA_P_READS                  BIGINT,
POOL_INDEX_L_READS                 BIGINT,
POOL_INDEX_P_READS                 BIGINT,
POOL_XDA_L_READS                   BIGINT,
POOL_XDA_P_READS                   BIGINT,
POOL_TEMP_DATA_L_READS             BIGINT,
POOL_TEMP_DATA_P_READS             BIGINT,
POOL_TEMP_INDEX_L_READS            BIGINT,
POOL_TEMP_INDEX_P_READS            BIGINT,
POOL_TEMP_XDA_L_READS              BIGINT,
POOL_TEMP_XDA_P_READS              BIGINT,
DBPARTITIONNUM                     SMALLINT )
IN "some-data-tablespace" INDEX IN "some-index-tablespace"  
LONG IN "some-lob-tablespace";

commit;

CREATE unique INDEX DBA.PK_SNAPSTMT ON DBA.SNAPSTMT
                (SNAPSHOT_TIMESTAMP ASC, DB_NAME ASC, AGENT_ID ASC) ;
commit;

-- DDL Statements for primary key on Table

ALTER TABLE DBA.SNAPSTMT
        ADD CONSTRAINT PK_SNAPSTMT PRIMARY KEY
                (SNAPSHOT_TIMESTAMP, DB_NAME, AGENT_ID);
commit;