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

create table DBA.SNAPDB (
SNAPSHOT_TIMESTAMP TIMESTAMP not null,
DB_NAME VARCHAR (128) not null,
DB_PATH VARCHAR (1024),
INPUT_DB_ALIAS VARCHAR (128),
DB_STATUS VARCHAR (12),
CATALOG_PARTITION SMALLINT ,
CATALOG_PARTITION_NAME VARCHAR (128),
SERVER_PLATFORM VARCHAR (12),
DB_LOCATION VARCHAR (12),
DB_CONN_TIME TIMESTAMP ,
LAST_RESET TIMESTAMP ,
LAST_BACKUP TIMESTAMP ,
CONNECTIONS_TOP BIGINT ,
TOTAL_CONS BIGINT ,
TOTAL_SEC_CONS BIGINT ,
APPLS_CUR_CONS BIGINT ,
APPLS_IN_DB2 BIGINT ,
NUM_ASSOC_AGENTS BIGINT ,
AGENTS_TOP BIGINT ,
COORD_AGENTS_TOP BIGINT ,
LOCKS_HELD BIGINT ,
LOCK_WAITS BIGINT ,
LOCK_WAIT_TIME BIGINT ,
LOCK_LIST_IN_USE BIGINT ,
DEADLOCKS BIGINT ,
LOCK_ESCALS BIGINT ,
X_LOCK_ESCALS BIGINT ,
LOCKS_WAITING BIGINT ,
LOCK_TIMEOUTS BIGINT ,
NUM_INDOUBT_TRANS BIGINT ,
SORT_HEAP_ALLOCATED BIGINT ,
SORT_SHRHEAP_ALLOCATED BIGINT ,
SORT_SHRHEAP_TOP BIGINT ,
POST_SHRTHRESHOLD_SORTS BIGINT ,
TOTAL_SORTS BIGINT ,
TOTAL_SORT_TIME BIGINT ,
SORT_OVERFLOWS BIGINT ,
ACTIVE_SORTS BIGINT ,
POOL_DATA_L_READS BIGINT ,
POOL_DATA_P_READS BIGINT ,
POOL_TEMP_DATA_L_READS BIGINT ,
POOL_TEMP_DATA_P_READS BIGINT ,
POOL_ASYNC_DATA_READS BIGINT ,
POOL_DATA_WRITES BIGINT ,
POOL_ASYNC_DATA_WRITES BIGINT ,
POOL_INDEX_L_READS BIGINT ,
POOL_INDEX_P_READS BIGINT ,
POOL_TEMP_INDEX_L_READS BIGINT ,
POOL_TEMP_INDEX_P_READS BIGINT ,
POOL_ASYNC_INDEX_READS BIGINT ,
POOL_INDEX_WRITES BIGINT ,
POOL_ASYNC_INDEX_WRITES BIGINT ,
POOL_XDA_P_READS BIGINT,
POOL_XDA_L_READS BIGINT,
POOL_XDA_WRITES BIGINT,
POOL_ASYNC_XDA_READS BIGINT,
POOL_ASYNC_XDA_WRITES BIGINT,
POOL_TEMP_XDA_P_READS BIGINT,
POOL_TEMP_XDA_L_READS BIGINT,
POOL_READ_TIME BIGINT,
POOL_WRITE_TIME BIGINT,
POOL_ASYNC_READ_TIME BIGINT,
POOL_ASYNC_WRITE_TIME BIGINT,
POOL_ASYNC_DATA_READ_REQS BIGINT,
POOL_ASYNC_INDEX_READ_REQS BIGINT,
POOL_ASYNC_XDA_READ_REQS BIGINT,
POOL_NO_VICTIM_BUFFER BIGINT,
POOL_LSN_GAP_CLNS BIGINT,
POOL_DRTY_PG_STEAL_CLNS BIGINT,
POOL_DRTY_PG_THRSH_CLNS BIGINT,
PREFETCH_WAIT_TIME BIGINT,
UNREAD_PREFETCH_PAGES BIGINT,
DIRECT_READS BIGINT,
DIRECT_WRITES BIGINT,
DIRECT_READ_REQS BIGINT,
DIRECT_WRITE_REQS BIGINT,
DIRECT_READ_TIME BIGINT,
DIRECT_WRITE_TIME BIGINT,
FILES_CLOSED BIGINT,
ELAPSED_EXEC_TIME_S BIGINT,
ELAPSED_EXEC_TIME_MS 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,
UID_SQL_STMTS BIGINT,
DDL_SQL_STMTS BIGINT,
INT_AUTO_REBINDS BIGINT,
INT_ROWS_DELETED BIGINT,
INT_ROWS_INSERTED 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,
ROWS_READ BIGINT,
BINDS_PRECOMPILES BIGINT,
TOTAL_LOG_AVAILABLE BIGINT,
TOTAL_LOG_USED BIGINT,
SEC_LOG_USED_TOP BIGINT,
TOT_LOG_USED_TOP BIGINT,
SEC_LOGS_ALLOCATED BIGINT,
LOG_READS BIGINT,
LOG_READ_TIME_S BIGINT,
LOG_READ_TIME_NS BIGINT,
LOG_WRITES BIGINT,
LOG_WRITE_TIME_S BIGINT,
LOG_WRITE_TIME_NS BIGINT,
NUM_LOG_WRITE_IO BIGINT,
NUM_LOG_READ_IO BIGINT,
NUM_LOG_PART_PAGE_IO BIGINT,
NUM_LOG_BUFFER_FULL BIGINT,
NUM_LOG_DATA_FOUND_IN_BUFFER BIGINT,
APPL_ID_OLDEST_XACT BIGINT,
LOG_TO_REDO_FOR_RECOVERY BIGINT,
LOG_HELD_BY_DIRTY_PAGES BIGINT,
PKG_CACHE_LOOKUPS BIGINT,
PKG_CACHE_INSERTS BIGINT,
PKG_CACHE_NUM_OVERFLOWS BIGINT,
PKG_CACHE_SIZE_TOP BIGINT,
APPL_SECTION_LOOKUPS BIGINT,
APPL_SECTION_INSERTS BIGINT,
CAT_CACHE_LOOKUPS BIGINT,
CAT_CACHE_INSERTS BIGINT,
CAT_CACHE_OVERFLOWS BIGINT,
CAT_CACHE_SIZE_TOP BIGINT,
PRIV_WORKSPACE_SIZE_TOP BIGINT,
PRIV_WORKSPACE_NUM_OVERFLOWS BIGINT,
PRIV_WORKSPACE_SECTION_INSERTS BIGINT,
PRIV_WORKSPACE_SECTION_LOOKUPS BIGINT,
SHR_WORKSPACE_SIZE_TOP BIGINT,
SHR_WORKSPACE_NUM_OVERFLOWS BIGINT,
SHR_WORKSPACE_SECTION_INSERTS BIGINT,
SHR_WORKSPACE_SECTION_LOOKUPS BIGINT,
TOTAL_HASH_JOINS BIGINT,
TOTAL_HASH_LOOPS BIGINT,
HASH_JOIN_OVERFLOWS BIGINT,
HASH_JOIN_SMALL_OVERFLOWS BIGINT,
POST_SHRTHRESHOLD_HASH_JOINS BIGINT,
ACTIVE_HASH_JOINS BIGINT,
NUM_DB_STORAGE_PATHS BIGINT,
DBPARTITIONNUM SMALLINT ,
SMALLEST_LOG_AVAIL_NODE INTEGER  )
IN "some-data-tablespace" INDEX IN "some-index-tablespace" ;
commit;

CREATE UNIQUE INDEX DBA.PK_SNAPDB ON DBA.SNAPDB
                (SNAPSHOT_TIMESTAMP ASC, DB_NAME ASC) ;
commit;

-- DDL Statements for primary key on Table 

ALTER TABLE DBA.SNAPDB
        ADD CONSTRAINT PK_SNAPDB PRIMARY KEY
                (SNAPSHOT_TIMESTAMP, DB_NAME);
commit;