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

create table dba.snapappl (
SNAPSHOT_TIMESTAMP                 TIMESTAMP not null,
DB_NAME                            VARCHAR(128) not null,
AGENT_ID                           BIGINT not null,
UOW_LOG_SPACE_USED                 BIGINT,
ROWS_READ                          BIGINT,
ROWS_WRITTEN                       BIGINT,
INACT_STMTHIST_SZ                  BIGINT,
POOL_DATA_L_READS                  BIGINT,
POOL_DATA_P_READS                  BIGINT,
POOL_DATA_WRITES                   BIGINT,
POOL_INDEX_L_READS                 BIGINT,
POOL_INDEX_P_READS                 BIGINT,
POOL_INDEX_WRITES                  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,
POOL_XDA_L_READS                   BIGINT,
POOL_XDA_P_READS                   BIGINT,
POOL_XDA_WRITES                    BIGINT,
POOL_READ_TIME                     BIGINT,
POOL_WRITE_TIME                    BIGINT,
DIRECT_READS                       BIGINT,
DIRECT_WRITES                      BIGINT,
DIRECT_READ_REQS                   BIGINT,
DIRECT_WRITE_REQS                  BIGINT,
DIRECT_READ_TIME                   BIGINT,
DIRECT_WRITE_TIME                  BIGINT,
UNREAD_PREFETCH_PAGES              BIGINT,
LOCKS_HELD                         BIGINT,
LOCK_WAITS                         BIGINT,
LOCK_WAIT_TIME                     BIGINT,
LOCK_ESCALS                        BIGINT,
X_LOCK_ESCALS                      BIGINT,
DEADLOCKS                          BIGINT,
TOTAL_SORTS                        BIGINT,
TOTAL_SORT_TIME                    BIGINT,
SORT_OVERFLOWS                     BIGINT,
COMMIT_SQL_STMTS                   BIGINT,
ROLLBACK_SQL_STMTS                 BIGINT,
DYNAMIC_SQL_STMTS                  BIGINT,
STATIC_SQL_STMTS                   BIGINT,
FAILED_SQL_STMTS                   BIGINT,
SELECT_SQL_STMTS                   BIGINT,
DDL_SQL_STMTS                      BIGINT,
UID_SQL_STMTS                      BIGINT,
INT_AUTO_REBINDS                   BIGINT,
INT_ROWS_DELETED                   BIGINT,
INT_ROWS_UPDATED                   BIGINT,
INT_COMMITS                        BIGINT,
INT_ROLLBACKS                      BIGINT,
INT_DEADLOCK_ROLLBACKS             BIGINT,
ROWS_DELETED                       BIGINT,
ROWS_INSERTED                      BIGINT,
ROWS_UPDATED                       BIGINT,
ROWS_SELECTED                      BIGINT,
BINDS_PRECOMPILES                  BIGINT,
OPEN_REM_CURS                      BIGINT,
OPEN_REM_CURS_BLK                  BIGINT,
REJ_CURS_BLK                       BIGINT,
ACC_CURS_BLK                       BIGINT,
SQL_REQS_SINCE_COMMIT              BIGINT,
LOCK_TIMEOUTS                      BIGINT,
INT_ROWS_INSERTED                  BIGINT,
OPEN_LOC_CURS                      BIGINT,
OPEN_LOC_CURS_BLK                  BIGINT,
PKG_CACHE_LOOKUPS                  BIGINT,
PKG_CACHE_INSERTS                  BIGINT,
CAT_CACHE_LOOKUPS                  BIGINT,
CAT_CACHE_INSERTS                  BIGINT,
CAT_CACHE_OVERFLOWS                BIGINT,
NUM_AGENTS                         BIGINT,
AGENTS_STOLEN                      BIGINT,
ASSOCIATED_AGENTS_TOP              BIGINT,
APPL_PRIORITY                      BIGINT,
APPL_PRIORITY_TYPE                 VARCHAR(16),
PREFETCH_WAIT_TIME                 BIGINT,
APPL_SECTION_LOOKUPS               BIGINT,
APPL_SECTION_INSERTS               BIGINT,
LOCKS_WAITING                      BIGINT,
TOTAL_HASH_JOINS                   BIGINT,
TOTAL_HASH_LOOPS                   BIGINT,
HASH_JOIN_OVERFLOWS                BIGINT,
HASH_JOIN_SMALL_OVERFLOWS          BIGINT,
APPL_IDLE_TIME                     BIGINT,
UOW_LOCK_WAIT_TIME                 BIGINT,
UOW_COMP_STATUS                    VARCHAR(14),
AGENT_USR_CPU_TIME_S               BIGINT,
AGENT_USR_CPU_TIME_MS              BIGINT,
AGENT_SYS_CPU_TIME_S               BIGINT,
AGENT_SYS_CPU_TIME_MS              BIGINT,
APPL_CON_TIME                      TIMESTAMP,
CONN_COMPLETE_TIME                 TIMESTAMP,
LAST_RESET                         TIMESTAMP,
UOW_START_TIME                     TIMESTAMP,
UOW_STOP_TIME                      TIMESTAMP,
PREV_UOW_STOP_TIME                 TIMESTAMP,
UOW_ELAPSED_TIME_S                 BIGINT,
UOW_ELAPSED_TIME_MS                BIGINT,
ELAPSED_EXEC_TIME_S                BIGINT,
ELAPSED_EXEC_TIME_MS               BIGINT,
INBOUND_COMM_ADDRESS               VARCHAR(32),
LOCK_TIMEOUT_VAL                   BIGINT,
PRIV_WORKSPACE_NUM_OVERFLOWS       BIGINT,
PRIV_WORKSPACE_SECTION_INSERTS     BIGINT,
PRIV_WORKSPACE_SECTION_LOOKUPS     BIGINT,
PRIV_WORKSPACE_SIZE_TOP            BIGINT,
SHR_WORKSPACE_NUM_OVERFLOWS        BIGINT,
SHR_WORKSPACE_SECTION_INSERTS      BIGINT,
SHR_WORKSPACE_SECTION_LOOKUPS      BIGINT,
SHR_WORKSPACE_SIZE_TOP             BIGINT,
DBPARTITIONNUM                     SMALLINT,
CAT_CACHE_SIZE_TOP                 BIGINT )
IN "some-data-tablespace" INDEX IN "some-index-tablespace" ;

commit;

CREATE UNIQUE INDEX DBA.PK_SNAPAPPL ON DBA.SNAPAPPL
                (SNAPSHOT_TIMESTAMP ASC, DB_NAME ASC, AGENT_ID ASC) ;
commit;

-- DDL Statements for primary key on Table

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

-- Other indexes I created for my performance report views, your 
-- mileage may vary so do EXPLAIN on the views before automatically 
-- creating the following performance indexes:

   CREATE INDEX DBA.IDX_ROWS_SELECTED ON DBA.SNAPAPPL
   ("ROWS_SELECTED" ASC, "ROWS_READ" ASC, "SNAPSHOT_TIMESTAMP"
   ASC, "AGENT_ID" ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;

   CREATE INDEX DBA.IDX_TIMESTAMP_ROWS_READ ON "DBA     "."SNAPAPPL"
   ("SNAPSHOT_TIMESTAMP" ASC, "ROWS_READ" ASC, "ROWS_SELECTED"
   ASC) ALLOW REVERSE SCANS ;
   COMMIT WORK ;
   RUNSTATS ON TABLE DBA.SNAPAPPL AND INDEXES ALL;
   COMMIT WORK ;