SAMPLE Database DDL: Script 1 sampleDDL_1.db2

Figure F.2. DDL for the SAMPLE database.

 ------------------------------------------------ --  This DDL script is a common script for all --  platforms. For LUW, the tables will be --  created in the default tablespace, which --  is USERSPACE1 when defaults are used for --  database creation. For zSeries, the tables --  will be created in the DSNDB04 default --  database. In addition, make sure to uncomment --  the lines that are specific to zSeries. -- --  DDL Statements for table ORG  CREATE TABLE ORG (             DEPTNUMB SMALLINT NOT NULL ,             DEPTNAME VARCHAR(14) ,             MANAGER  SMALLINT ,             DIVISION VARCHAR(10) ,             LOCATION VARCHAR(13) )           ; --  DDL Statements for primary key on Table ORG --  zSeries only --  CREATE UNIQUE INDEX ORG_PK --          ON ORG --                (DEPTNUMB); ALTER  TABLE ORG   ADD  PRIMARY KEY           (DEPTNUMB); ------------------------------------------------ --  DDL Statements for table STAFF ------------------------------------------------  CREATE TABLE STAFF (             ID     SMALLINT NOT NULL ,             NAME   VARCHAR(9) ,             DEPT   SMALLINT ,             JOB    CHAR(5) ,             YEARS  SMALLINT ,             SALARY DECIMAL(7,2) ,             COMM   DECIMAL(7,2) )           ; --  DDL Statements for primary key on Table STAFF --  zSeries only --  CREATE UNIQUE INDEX STAFF_PK --         ON STAFF --                 (ID); ALTER  TABLE STAFF   ADD  PRIMARY KEY            (ID); ------------------------------------------------ --  DDL Statements for table DEPARTMENT ------------------------------------------------  CREATE TABLE DEPARTMENT (             DEPTNO   CHAR(3) NOT NULL ,             DEPTNAME VARCHAR(29) NOT NULL ,             MGRNO    CHAR(6) ,             ADMRDEPT CHAR(3) NOT NULL ,             LOCATION CHAR(16) )            ; --  DDL Statements for primary key on Table DEPARTMENT --  zSeries only --  CREATE UNIQUE INDEX DEPARTMENT_PK --         ON DEPARTMENT --                 (DEPTNO); ALTER  TABLE DEPARTMENT   ADD  PRIMARY KEY           (DEPTNO); ------------------------------------------------ --  DDL Statements for table EMPLOYEE ------------------------------------------------  CREATE TABLE EMPLOYEE   (             EMPNO      CHAR(6) NOT NULL ,             FIRSTNME   VARCHAR(12) NOT NULL ,             MIDINIT    CHAR(1) NOT NULL ,             LASTNAME   VARCHAR(15) NOT NULL ,             WORKDEPT   CHAR(3) ,             PHONENO    CHAR(4) ,             HIREDATE   DATE ,             JOB        CHAR(8) ,             EDLEVEL    SMALLINT NOT NULL ,             SEX        CHAR(1) ,             BIRTHDATE  DATE ,             SALARY     DECIMAL(9,2) ,             BONUS      DECIMAL(9,2) ,             COMM       DECIMAL(9,2) )            ; --  DDL Statements for primary key on Table EMPLOYEE --  zSeries only --  CREATE UNIQUE INDEX EMPLOYEE_PK --         ON EMPLOYEE --                (EMPNO); ALTER  TABLE EMPLOYEE   ADD  PRIMARY KEY           (EMPNO); ------------------------------------------------ --  DDL Statements for table EMP_ACT ------------------------------------------------  CREATE TABLE EMP_ACT  (             EMPNO    CHAR(6) NOT NULL ,             PROJNO   CHAR(6) NOT NULL ,             ACTNO    SMALLINT NOT NULL ,             EMPTIME  DECIMAL(5,2) ,             EMSTDATE DATE ,             EMENDATE DATE )            ; ------------------------------------------------ --  DDL Statements for table PROJECT ------------------------------------------------  CREATE TABLE PROJECT  (             PROJNO   CHAR(6) NOT NULL ,             PROJNAME VARCHAR(24) NOT NULL ,             DEPTNO   CHAR(3) NOT NULL ,             RESPEMP  CHAR(6) NOT NULL ,             PRSTAFF  DECIMAL(5,2) ,             PRSTDATE DATE ,             PRENDATE DATE ,             MAJPROJ  CHAR(6) )            ; --  DDL Statements for primary key on Table PROJECT --  zSeries only --  CREATE UNIQUE INDEX PROJECT_PK --         ON PROJECT --                (PROJNO); ALTER  TABLE PROJECT   ADD  PRIMARY KEY           (PROJNO); ------------------------------------------------ --  DDL Statements for table EMP_PHOTO ------------------------------------------------ --  zSeries only --  CREATE LOB TABLESPACE PHOTOTS IN DSNDB04 LOG NO; --  COMMIT;  CREATE TABLE EMP_  PHOTO (             EMPNO   CHAR(6) NOT NULL ,             PHOTO_  FORMAT VARCHAR(10) NOT NULL ,             PICTURE BLOB(102400) )            ; --  DDL Statements for primary key on Table EMP_PHOTO --  zSeries only --  COMMIT; --  CREATE UNIQUE INDEX EMP_PHOTO_PK --         ON EMP_PHOTO --                 (EMPNO, --          PHOTO_FORMAT); ALTER  TABLE EMP_PHOTO   ADD  PRIMARY KEY            (EMPNO,             PHOTO_FORMAT); --  zSeries only --  CREATE AUXILIARY TABLE EMP_PHOTO_AUX --         IN DSNDB04.PHOTOTS --         STORES EMP_PHOTO --         COLUMN PICTURE; --  CREATE UNIQUE INDEX XEMP_PHOTO --         ON EMP_PHOTO_AUX; --  COMMIT; ------------------------------------------------ --  DDL Statements for table EMP_RESUME ------------------------------------------------ --  zSeries only --  CREATE LOB TABLESPACE RESUMETS IN DSNDB04 LOG NO; --  COMMIT;  CREATE TABLE EMP_RESUME  (             EMPNO         CHAR(6) NOT NULL ,             RESUME_FORMAT VARCHAR(10) NOT NULL ,             RESUME        CLOB(5120) )            ;  --  DDL Statements for primary key on Table EMP_RESUME --  zSeries only --  COMMIT; --  CREATE UNIQUE INDEX EMP_RESUME_PK --          ON EMP_RESUME --                (EMPNO, --                 RESUME_FORMAT); ALTER  TABLE EMP_RESUME   ADD  PRIMARY KEY           (EMPNO,            RESUME_FORMAT); --  zSeries only --  CREATE AUXILIARY TABLE EMP_RESUME_AUX --         IN DSNDB04.RESUMETS --         STORES EMP_RESUME --         COLUMN RESUME; --  CREATE UNIQUE INDEX XEMP_RESUME --         ON EMP_RESUME_AUX; --  COMMIT; ------------------------------------------------ --  DDL Statements for table SALES ------------------------------------------------  CREATE TABLE SALES  (             SALES_DATE   DATE ,             SALES_PERSON VARCHAR(15) ,             REGION       VARCHAR(15) ,             SALES        INTEGER )            ; ------------------------------------------------ --  DDL Statements for table CL_SCHED ------------------------------------------------  CREATE TABLE CL_SCHED   (             CLASS_CODE  CHAR(7) ,             DAY         SMALLINT ,             STARTING    TIME ,             ENDING      TIME )            ; ------------------------------------------------ --  DDL Statements for table IN_TRAY ------------------------------------------------  CREATE TABLE IN_TRAY (             RECEIVED TIMESTAMP ,             SOURCE CHAR(8) ,             SUBJECT CHAR(64) ,             NOTE_TEXT VARCHAR(3000) )            ;