#!/usr/bin/env ksh
db2 -v "CONNECT TO SPECDB"


db2 -v "DROP TABLESPACE JMS_SPACE"
db2 -v "DROP BUFFERPOOL JMS_BP"
db2 -v "DROP TABLESPACE JMS_IDX_SPACE"

db2 -v "DROP TABLESPACE JMS_IDX_SPACE_BP"
db2 -v "DROP BUFFERPOOL JMS_IDX_BP"


db2 -v "CREATE BUFFERPOOL JMS_BP PAGESIZE 32K"
db2 -v "CREATE TABLESPACE JMS_SPACE PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL JMS_BP INITIALSIZE 500M EXTENTSIZE 1024K PREFETCHSIZE 8K"
db2 -v "CREATE BUFFERPOOL JMS_IDX_BP PAGESIZE 32K"
db2 -v "CREATE TABLESPACE JMS_IDX_SPACE PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL JMS_IDX_BP INITIALSIZE 500M EXTENTSIZE 1024K PREFETCHSIZE 8K"


set -A schemaName smith4Node00 smith4Node01 smith4Node02 smith4Node03 smith4Node04 smith4Node05 smith4Node06 smith4Node07 smith4Node08 smith4Node09 smith4Node10 smith4Node11 smith4Node12 smith4Node13 smith4Node14 smith4Node15 smith4Node16 smith4Node17 smith4Node18 smith4Node19 smith4Node20
#set -A schemaName smith02Node01 smith02Node02 smith02Node03 smith02Node04 smith02Node05 smith02Node06 smith02Node07 smith02Node08 smith02Node09 smith02Node10 smith02Node11 smith02Node12 smith02Node13 smith02Node14 smith02Node15 smith02Node16 smith02Node17 smith02Node18 smith02Node19 smith02Node20
#set -A schemaName SANDY01NODE01 SANDY01NODE02 SANDY01NODE03 SANDY01NODE04 SANDY01NODE05 SANDY01NODE06 SANDY01NODE07 SANDY01NODE08 SANDY01NODE09 SANDY01NODE10 SANDY01NODE11 SANDY01NODE12 SANDY01NODE13 SANDY01NODE14 SANDY01NODE15 SANDY01NODE16 SANDY01NODE17 SANDY01NODE18 SANDY01NODE19

for schema in ${schemaName[*]}
do
        db2 -v "drop table $schema.sib000"
        db2 -v "drop table $schema.sib001"
        db2 -v "drop table $schema.sib002"
        db2 -v "drop table $schema.SIBCLASSMAP"
        db2 -v "drop table $schema.SIBKEYS"
        db2 -v "drop table $schema.SIBLISTING"
        db2 -v "drop table $schema.SIBOWNER"
        db2 -v "drop table $schema.SIBOWNERO"
        db2 -v "drop table $schema.SIBXACTS"
        db2 -v "DROP SCHEMA $schema RESTRICT"
        db2 -v "CREATE SCHEMA $schema AUTHORIZATION DB2INST3"
        db2 -v "CREATE TABLE $schema.SIBOWNERO (EMPTY_COLUMN INTEGER)"
        db2 -v "CREATE TABLE $schema.SIBOWNER (ME_UUID VARCHAR(16), INC_UUID VARCHAR(16), VERSION INTEGER, MIGRATION_VERSION INTEGER, ME_LUTS TIMESTAMP, ME_INFO VARCHAR(254), ME_STATUS VARCHAR(16))"
        db2 -v "CREATE TABLE $schema.SIBCLASSMAP (CLASSID INTEGER NOT NULL, URI VARCHAR(2048) NOT NULL)"
        db2 -v "ALTER TABLE $schema.SIBCLASSMAP ADD PRIMARY KEY (CLASSID)"
        db2 -v "CREATE TABLE $schema.SIBLISTING (ID INTEGER NOT NULL, SCHEMA_NAME VARCHAR(10), TABLE_NAME VARCHAR(10) NOT NULL, TABLE_TYPE CHAR(1) NOT NULL)"
        db2 -v "ALTER TABLE $schema.SIBLISTING ADD PRIMARY KEY (ID)"
        db2 -v "CREATE TABLE $schema.SIB000(ID BIGINT NOT NULL, STREAM_ID BIGINT NOT NULL, TYPE CHAR(2), EXPIRY_TIME BIGINT, STRATEGY INTEGER, REFERENCE BIGINT, CLASS_ID INTEGER NOT NULL, PRIORITY INTEGER, SEQUENCE BIGINT, PERMANENT_ID INTEGER, TEMPORARY_ID INTEGER, LOCK_ID BIGINT, DATA_SIZE INTEGER NOT NULL, DATA VARCHAR(28869) FOR BIT DATA, LONG_DATA BLOB(1073741824) LOGGED NOT COMPACT, XID VARCHAR(254), DELETED SMALLINT, REDELIVERED_COUNT INTEGER) IN JMS_SPACE INDEX IN JMS_IDX_SPACE"
        db2 -v "ALTER TABLE $schema.SIB000 VOLATILE CARDINALITY"
        db2 -v "CREATE UNIQUE INDEX $schema.SIB000PKIX ON $schema.SIB000(ID ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "ALTER TABLE $schema.SIB000 ADD CONSTRAINT SIB000PKIX PRIMARY KEY(ID)"
        db2 -v "CREATE INDEX $schema.SIB000STREAMIX ON $schema.SIB000 (STREAM_ID ASC, SEQUENCE ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "CREATE TABLE $schema.SIB001(ID BIGINT NOT NULL, STREAM_ID BIGINT NOT NULL, TYPE CHAR(2), EXPIRY_TIME BIGINT, STRATEGY INTEGER, REFERENCE BIGINT, CLASS_ID INTEGER NOT NULL, PRIORITY INTEGER, SEQUENCE BIGINT, PERMANENT_ID INTEGER, TEMPORARY_ID INTEGER, LOCK_ID BIGINT, DATA_SIZE INTEGER NOT NULL, DATA VARCHAR(28869) FOR BIT DATA, LONG_DATA BLOB(1073741824) LOGGED NOT COMPACT, XID VARCHAR(254), DELETED SMALLINT, REDELIVERED_COUNT INTEGER) IN JMS_SPACE INDEX IN JMS_IDX_SPACE"
        db2 -v "ALTER TABLE $schema.SIB001 VOLATILE CARDINALITY"
        db2 -v "CREATE UNIQUE INDEX $schema.SIB001PKIX ON $schema.SIB001(ID ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "ALTER TABLE $schema.SIB001 ADD CONSTRAINT SIB001PKIX PRIMARY KEY (ID)"
        db2 -v "CREATE INDEX $schema.SIB001STREAMIX ON $schema.SIB001(STREAM_ID ASC, SEQUENCE ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "CREATE TABLE $schema.SIB002(ID BIGINT NOT NULL, STREAM_ID BIGINT NOT NULL, TYPE CHAR(2), EXPIRY_TIME BIGINT, STRATEGY INTEGER, REFERENCE BIGINT, CLASS_ID INTEGER NOT NULL, PRIORITY INTEGER, SEQUENCE BIGINT, PERMANENT_ID INTEGER, TEMPORARY_ID INTEGER, LOCK_ID BIGINT, DATA_SIZE INTEGER NOT NULL, DATA VARCHAR(28869) FOR BIT DATA, LONG_DATA BLOB(1073741824) LOGGED NOT COMPACT, XID VARCHAR(254), DELETED SMALLINT, REDELIVERED_COUNT INTEGER) IN JMS_SPACE INDEX IN JMS_IDX_SPACE"
        db2 -v "ALTER TABLE $schema.SIB002 VOLATILE CARDINALITY"
        db2 -v "CREATE UNIQUE INDEX $schema.SIB002PKIX ON $schema.SIB002(ID ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "ALTER TABLE $schema.SIB002 ADD CONSTRAINT SIB002PKIX PRIMARY KEY (ID)"
        db2 -v "CREATE INDEX $schema.SIB002STREAMIX ON $schema.SIB002(STREAM_ID ASC, SEQUENCE ASC) COMPRESS NO ALLOW REVERSE SCANS"
        db2 -v "CREATE TABLE $schema.SIBKEYS (ID VARCHAR(50) NOT NULL, LAST_KEY BIGINT NOT NULL)"
        db2 -v "ALTER TABLE $schema.SIBKEYS ADD PRIMARY KEY (ID)"
        db2 -v "CREATE TABLE $schema.SIBXACTS (XID VARCHAR(254) NOT NULL, STATE CHAR(1) NOT NULL)"
        db2 -v "ALTER TABLE $schema.SIBXACTS ADD PRIMARY KEY(XID)"
done
# db2 -v "DISCONNECT ALL"
