Chapter C. DB2CERT Database DDL The following is the DDL that will create the following DB2 objects for the DB2CERT database: -
Stogroup -
Database -
Segmented Tablespace -
Partitioned Tablespace -
LOB Tablespace -
Tables -
Indexes -
Auxiliary Table -
Auxiliary Index -
User-Defined Types -- Create Storage Group CERTSTG CREATE STOGROUP CERTSTG VOLUME(*) VCAT DB2USER1; -- Create Database DB2CERT CREATE DATABASE DB2CERT STOGROUP CERTSTG BUFFERPOOL BP7 INDEXBP BP8; -- Create Segmented Table Space CERTTS CREATE TABLESPACE CERTTS IN DB2CERT USING STOGROUP CERTSTG PRIQTY 52 SECQTY 20 ERASE NO LOCKSIZE PAGE BUFFERPOOL BP6 CLOSE YES SEGSIZE 32; -- Create Partitioned Table Space CERTTSPT 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 Auxiliary Table Space for LOB Column CAND_PHOTO CREATE LOB TABLESPACE CERTPIC IN DB2CERT USING STOGROUP CERTSTG PRIQTY 3200 SECQTY 1600 LOCKSIZE LOB BUFFERPOOL BP16K1 GBPCACHE SYSTEM LOG NO CLOSE NO; -- Create User Defined Data Types CREATE DISTINCT TYPE CANDIDATE_ID AS CHAR(9) WITH COMPARISONS; CREATE DISTINCT TYPE TEST_ID AS CHAR(6) WITH COMPARISONS; CREATE DISTINCT TYPE CENT AS CHAR(4) WITH COMPARISONS; CREATE DISTINCT TYPE PHONE AS CHAR(10) WITH COMPARISONS; CREATE DISTINCT TYPE SCORE AS DECIMAL(6,2) WITH COMPARISONS; CREATE DISTINCT TYPE BITMAP AS BLOB(1M); CREATE DISTINCT TYPE MINUTES AS SMALLINT WITH COMPARISONS; -- Create CANDIDATE Table Using UDTs Previously Defined CREATE TABLE DB2USER1.CANDIDATE (CID CANDIDATE_ID 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, PROV_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 Table TEST with Primary Key and Check Constraint CREATE TABLE DB2USER1.TEST (NUMBER TEST_ID NOT NULL, NAME VARCHAR(50) NOT NULL, TYPE CHAR(1) NOT NULL, CUT_SCORE SCORE, LENGTH MINUTES NOT NULL, TOTALTAKEN SMALLINT NOT NULL WITH DEFAULT, TOTALPASSED SMALLINT NOT NULL WITH DEFAULT, PRIMARY KEY (NUMBER), 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 TEST_CENTER with Primary Key CREATE TABLE DB2USER1.TEST_CENTER (TCID CENTER_ID 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); -- Create Partitioning Index CREATE UNIQUE INDEX DB2USER1.TESTCNTX ON DB2USER1.TEST_CENTER (TCID ASC) USING STOGROUP CERTSTG PRIQTY 512 SECQTY 64 ERASE NO CLUSTER (PART 1 VALUES (300), PART 2 VALUES (500)) BUFFERPOOL BP3 CLOSE YES; -- Create NPI with pieces CREATE UNIQUE INDEX DB2USER1.TESTCN2X ON DB2USER1.TEST_CENTER (CODE ASC) USING STOGROUP CERTSTG PIECESIZE 512K; -- Create TEST_TAKEN Table With Primary and Foreign Keys CREATE TABLE DB2USER1.TEST_TAKEN (CID CANDIDATE_ID NOT NULL, TCID CENTER_ID NOT NULL, NUMBER TEST_ID NOT NULL, DATE_TAKEN DATE NOT NULL WITH DEFAULT, START_TIME TIME WITH DEFAULT, FINISH_TIME TIME WITH DEFAULT, SCORE SCORE, 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 Auxiliary Table for LOB Column CAND_PHOTO CREATE AUX TABLE CAND_PHOTO IN DB2CERT.CERTPIC STORES DB2USER1.CANDIDATE COLUMN PHOTO; -- Create Auxiliary Index for LOB Column CAND_PHOTO CREATE UNIQUE INDEX DB2CERT.PHOTOIX ON DB2USER1.CAND_PHOTO USING VCAT DB2USER1 COPY YES; |