Chapter C. DB2CERT Database DDLT

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Part 5.  Advanced Programming Functions


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; 

Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net