CREDITCARD.DDL

-- <ScriptOptions statementTerminator=";" />
CREATE TABLE DB2INST1.CREDITCARD ( 
CCARDID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 CACHE 20 ),
ACCID BIGINT NOT NULL,
CCNUMBER BIGINT NOT NULL,
EXPIRATION INTEGER NOT NULL,
CVV INTEGER NOT NULL,
PUBKEY BIGINT,
PRIMARY KEY ( CCARDID ) );

ALTER TABLE DB2INST1.CREDITCARD ADD CONSTRAINT CREDITCARD_ACCOUNT_FK FOREIGN KEY ( ACCID ) REFERENCES DB2INST1.ACCOUNT ( ACCID ) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE DB2INST1.CREDITCARD ADD CONSTRAINT CREDITCARD_UN UNIQUE ( CCNUMBER, EXPIRATION );

CREATE TABLE DB2INST1.MERCHANT ( MERCHANTID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE -2147483648 MAXVALUE 2147483647 CACHE 20 ), MCC INTEGER, MERCHANT_NAME VARCHAR(128),PRIMARY KEY ( MERCHANTID ) );
CREATE TABLE DB2INST1.MERCHANTACC ( ACCID BIGINT NOT NULL, MERCHANTID INTEGER NOT NULL, TOKEN CHAR(32),PRIMARY KEY ( MERCHANTID, ACCID ) );

ALTER TABLE DB2INST1.MERCHANTACC ADD CONSTRAINT MERCHANTACC_ACCOUNT_FK FOREIGN KEY ( ACCID ) REFERENCES DB2INST1.ACCOUNT ( ACCID ) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE DB2INST1.MERCHANTACC ADD CONSTRAINT MERCHANTACC_MERCHANT_FK FOREIGN KEY ( MERCHANTID ) REFERENCES DB2INST1.MERCHANT ( MERCHANTID ) ON DELETE CASCADE ON UPDATE NO ACTION;
CALL SYSPROC.ADMIN_CMD( 'REORG TABLE DB2INST1.MERCHANTACC USE TEMPSPACE1' );

--ALTER TABLE DB2INST1.HISTORY ADD CONSTRAINT HISTORY_PK PRIMARY KEY ( TXID );
CREATE TABLE DB2INST1.ERROR ( ERRID INTEGER NOT NULL, ERRORSTR CHAR(50), PRIMARY KEY (ERRID) );
CREATE TABLE DB2INST1.CARDHISTORY ( TXID BIGINT NOT NULL, CCARDID BIGINT NOT NULL, METHOD INTEGER NOT NULL, ERRID INTEGER, PRIMARY KEY ( TXID ) );
ALTER TABLE DB2INST1.CARDHISTORY ADD CONSTRAINT CARDHISTORY_TXID_FK FOREIGN KEY ( TXID ) REFERENCES DB2INST1.HISTORY ( TXID ) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE DB2INST1.CARDHISTORY ADD CONSTRAINT CARDHISTORY_CCARDID_FK FOREIGN KEY ( CCARDID ) REFERENCES DB2INST1.CREDITCARD ( CCARDID ) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE DB2INST1.CARDHISTORY ADD CONSTRAINT CARDHISTORY_ERRID_FK FOREIGN KEY ( ERRID ) REFERENCES DB2INST1.ERROR ( ERRID ) ON DELETE CASCADE ON UPDATE NO ACTION;

CREATE INDEX DB2INST1.MERCHANTACC_IDX ON DB2INST1.MERCHANTACC ( ACCID ) ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS COMPRESS NO INCLUDE NULL KEYS;
CREATE INDEX DB2INST1.HISTORY_IDX ON DB2INST1.HISTORY ( REFTXID ) ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS COMPRESS NO INCLUDE NULL KEYS;
--CREATE INDEX DB2INST1.CARDHISTORY_IDX ON DB2INST1.CARDHISTORY ( CCARDID ) ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS COMPRESS NO INCLUDE NULL KEYS;

--CREATE INDEX "DB2INST1"."IDX2112211444350" ON "DB2INST1"."CARDHISTORY"                                             
--   ("CCARDID" ASC, "METHOD" ASC, "TXID" ASC, "ERRID"                                                                  
--   ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;                                                      
--   COMMIT WORK ;
   
--CREATE INDEX "DB2INST1"."IDX2202161335390" ON "DB2INST1"."CARDHISTORY"                                             
--   ("CCARDID" ASC, "TXID" ASC, "ERRID" ASC, "METHOD"
--   ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS; 

CREATE UNIQUE INDEX "DB2INST1"."IDX_CARDHISTORY_CCARDID_TXID"                                                                  
   ON "DB2INST1"."CARDHISTORY" ("CCARDID" ASC, "TXID"                                                                                                                                                                                       
   DESC) INCLUDE ("ERRID", "METHOD") ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS;                         
   COMMIT WORK ; 

INSERT INTO DB2INST1.CUSTOMERTYPE (CODE, DESCRIPTION) VALUES('m','A Merchant Customer'); 

ALTER TABLE "HISTORY" ADD CONSTRAINT REFTX_UN UNIQUE ( REFTXID );

ALTER TABLE CARDHISTORY
      ADD CONSTRAINT CH_H_REFTEID_FK FOREIGN KEY (TXID)
      REFERENCES HISTORY(REFTXID)
      ON DELETE CASCADE
      --ON UPDATE NO ACTION
      ENFORCED
      ENABLE QUERY OPTIMIZATION;