AppendixA.DDL for the DB2CERT Database

Appendix A. DDL for the DB2CERT Database

The following DDL represents the statements needed to create DB2 objects for the DB2CERT database: stogroup, database, segmented table space, partitioned table space, LOB table space, tables, indexes, auxiliary table, auxiliary index, and user-defined types:

 -- CREATE STOGROUP CREATE STOGROUP CERTSTG VOLUME(*) VCAT DB2USER1; -- CREATE DATABASE CREATE DATABASE DB2CERT STOGROUP CERTSTG BUFFERPOOL BP7 INDEXBP BP8; -- CREATE SEGMENTED TABLESPACE CREATE TABLESPACE CERTTS IN DB2CERT USING STOGROUP CERTSTG    PRIQTY 52    SECQTY 20    ERASE NO  LOCKSIZE PAGE  BUFFERPOOL BP6  CLOSE YES  SEGSIZE 32; -- CREATE PARTITIONED TABLESPACE WITH 2 PARTITIONS CREATE TABLESPACE CERTTSPT IN DB2CERT USING STOGROUP CERTSTG    PRIQTY 100    SECQTY 120    ERASE NO NUMPARTS 2 (PART 1  COMPRESS YES,  PART 2  FREEPAGE 20) ERASE NO LOCKSIZE PAGE CLOSE NO; -- CREATE LOB TABLESPACE CREATE LOB TABLESPACE CERTPIC   IN DB2CERT   USING STOGROUP CERTSTG       PRIQTY 3200       SECQTY 1600   LOCKSIZE LOB   BUFFERPOOL BP16K1   GBPCACHE SYSTEM   LOG NO   CLOSE NO; -- CREATE DISTINCT DATA TYPES CREATE DISTINCT TYPE PHONE AS CHAR(10) WITH COMPARISONS; CREATE DISTINCT TYPE BITMAP AS BLOB(1M); CREATE DISTINCT TYPE MINUTES AS SMALLINT WITH COMPARISONS; -- CREATE PARENT TABLE CREATE TABLE DB2USER1.CANDIDATE  (CID           CHAR(9)         NOT NULL,   LNAME         VARCHAR(30)     NOT NULL,   FNAME         VARCHAR(30)     NOT NULL,   INITIAL       CHAR(1),   HPHONE        PHONE,   WPHONE        PHONE,   STREETNO      VARCHAR(8),   STREETNAME    VARCHAR(20)     NOT NULL,   CITY          VARCHAR(30)     NOT NULL,   STATE         VARCHAR(30)     NOT NULL,   CODE          CHAR(6)         NOT NULL,   COUNTRY       VARCHAR(20)     NOT NULL,   CERT_DBA      CHAR(1) NOT NULL WITH DEFAULT,   CERT_APP      CHAR(1) NOT NULL WITH DEFAULT,   PHOTO         BITMAP,   PRIMARY KEY (CID)   IN DB2CERT.CERTTS  ); -- CREATE PARENT TABLE WITH CHECK CONSTRAINT CREATE TABLE DB2USER1.TEST (NUMBER         CHAR(6)         NOT NULL,  NAME           VARCHAR(50)     NOT NULL,  TYPE           CHAR(1)         NOT NULL,  CUT_SCORE      DECIMAL(6,2)    NOT NULL,  LENGTH         MINUTES         NOT NULL,  TOTALTAKEN     SMALLINT        NOT NULL WITH DEFAULT,  TOTALPASSED    SMALLINT        NOT NULL WITH DEFAULT, CONSTRAINT UNIQUE_TEST PRIMARY KEY (NUMBER), CONSTRAINT UNIQUE_TEST_NAME UNIQUE (NAME), CONSTRAINT TEST_TYPE CHECK (TYPE IN ('P','B'))  IN DB2CERT.CERTTS  ); -- CREATE UNIQUE CLUSTERING INDEX CREATE UNIQUE INDEX DB2USER1.TESTIX   ON DB2USER1.TEST    (NUMBER ASC)   USING STOGROUP CERTSTG   PRIQTY 512   SECQTY 64   ERASE NO   CLUSTER -- CREATE TABLE WITH PARITIONING CREATE TABLE DB2USER1.TEST_CENTER  (TCID          CHAR(6)         NOT NULL,   NAME          VARCHAR(40)     NOT NULL,   STREETNO      VARCHAR(8)      NOT NULL,   STREETNAME    VARCHAR(20)     NOT NULL,   CITY          VARCHAR(30)     NOT NULL,   PROV_STATE    VARCHAR(30)     NOT NULL,   COUNTRY       VARCHAR(20)     NOT NULL,   CODE          CHAR(6)         NOT NULL,   TYPE          CHAR(1)         NOT NULL,   PHONE         PHONE           NOT NULL,   NOSEATS       SMALLINT        NOT NULL,   PRIMARY KEY (TCID)   IN DB2CERT.CERTTSPT       PARTITION BY (TCID)              (PARTITION 1 ENDING AT ('300'),               PARTITION 2 ENDING AT ('500')); -- CREATE UNIQUE INDEX CREATE UNIQUE INDEX DB2USER1.TESTCNTX   ON DB2USER1.TEST_CENTER    (TCID ASC)   USING STOGROUP CERTSTG   PRIQTY 512   SECQTY 64   ERASE NO   CLUSTER   BUFFERPOOL BP3   CLOSE YES; -- CREATE NON-PARTITIONING INDEX WITH PIECES CREATE UNIQUE INDEX DB2USER1.TESTCN2X   ON DB2USER1.TEST_CENTER    (CODE ASC)   USING STOGROUP CERTSTG   PIECESIZE 512K; -- CREATE DEPENDENT TABLE CREATE TABLE DB2USER1.TEST_TAKEN ( CID           CHAR(6)         NOT NULL,   TCID          CHAR(6)         NOT NULL,   NUMBER        CHAR(6)         NOT NULL,   DATE_TAKEN    DATE            NOT NULL WITH DEFAULT,   START_TIME    TIME            WITH DEFAULT,   FINISH_TIME   TIME            WITH DEFAULT,   SCORE         DECIMAL(6,2),   PASS_FAIL     CHAR(1),   SEAT_NO       CHAR(2)         NOT NULL,   PRIMARY KEY (TCID,CID,DATE_TAKEN),   FOREIGN KEY (CID)         REFERENCES CANDIDATE ON DELETE CASCADE,   FOREIGN KEY (TCID)         REFERENCES TEST_CENTER ON DELETE CASCADE,   FOREIGN KEY (NUMBER)         REFERENCES TEST ON DELETE RESTRICT   IN DB2CERT.CERTTS ); -- CREATE AUXILARY TABLE CREATE AUX TABLE CAND_PHOTO   IN DB2CERT.CERTPIC   STORES DB2USER1.CANDIDATE   COLUMN PHOTO; -- CREATE AUXILARY INDEX CREATE UNIQUE INDEX DB2CERT.PHOTOIX   ON DB2USER1.CAND_PHOTO   USING VCAT DB2USER1   COPY YES;