DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)

By Susan Lawson

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;