DDL for Additional Database Objects: Script 2 sampleDDL_2.db2

DDL for Additional Database Objects: Script #2 sampleDDL_2.db2

Figure F.3. DDL for additional tables.

 ------------------------------------------------ --  This DDL script is a common script for all --  platforms. ------------------------------------------------ --  DDL Statements for table tname ------------------------------------------------ CREATE  TABLE tname ( fullname VARCHAR(50) ); ------------------------------------------------ --  DDL Statements for table queue ------------------------------------------------      CREATE TABLE queue (data int); ------------------------------------------------ --  DDL Statements for table identity_tab ------------------------------------------------ CREATE  TABLE identity_tab (            id INT NOT NULL GENERATED ALWAYS AS IDENTITY,            data VARCHAR(100)) ; ------------------------------------------------ --  DDL Statements for table service_rq ------------------------------------------------ CREATE  TABLE service_rq(   rqid  SMALLINT NOT NULL           CONSTRAINT  rqid_pk PRIMARY KEY,    status VARCHAR(10) NOT NULL        WITH DEFAULT 'NEW'        CHECK ( status IN ( 'NEW', 'ASSIGNED', 'Pending', 'CANCELLED' )  ),    rq_desktop CHAR(1) NOT NULL        WITH DEFAULT 'N'        CHECK ( rq_desktop IN ( 'Y', 'N' ) ),    rq_ipaddress CHAR(1) NOT NULL        WITH DEFAULT 'N'        CHECK ( rq_ipaddress IN ( 'Y', 'N' ) ),    rq_unixid CHAR(1) NOT NULL        WITH DEFAULT 'N'        CHECK ( rq_unixid IN ( 'Y', 'N' ) ),    staffid INTEGER NOT NULL,    techid  INTEGER,    accum_rqnum INTEGER NOT NULL        GENERATED ALWAYS AS IDENTITY          ( START WITH 1,          INCREMENT  BY 1,        CACHE 10 ),   comment  VARCHAR(100) ); ------------------------------------------------ --  DDL Statements for table svcrq_sw ------------------------------------------------  CREATE  TABLE svcrq_sw (    rqid  SMALLINT NOT NULL            CONSTRAINT  rwid_pk PRIMARY KEY,    status  VARCHAR(10) NOT NULL         WITH DEFAULT 'NEW'         CHECK (status IN ('NEW', 'ASSIGNED', 'Pending', 'CANCELLED')),     ostype VARCHAR(50) NOT NULL         WITH DEFAULT 'W2K',     staffid INTEGER NOT NULL,     techid INTEGER,     accum_rqnum INTEGER NOT NULL         GENERATED ALWAYS AS IDENTITY           ( START WITH 1,           INCREMENT  BY 1,           CACHE  10 ),     comment VARCHAR(100) ); ------------------------------------------------ --  DDL Statements for sequence service_rq_seq ------------------------------------------------ CREATE  SEQUENCE service_rq_seq AS SMALLINT    START WITH 1    INCREMENT BY 1    MAXVALUE 5000    NO CYCLE    CACHE 50; ------------------------------------------------ --  DDL Statements for sequence staff_seq ------------------------------------------------ CREATE  SEQUENCE staff_seq AS INTEGER     START WITH 360     INCREMENT BY 10     NO MAXVALUE     NO CYCLE     NO CACHE; ------------------------------------------------ --  DDL Statements for table audit ------------------------------------------------ CREATE  TABLE AUDIT (   event_time  TIMESTAMP,   desc  VARCHAR(100)); ------------------------------------------------ --  DDL Statements for view emp_dep_v ------------------------------------------------ CREATE  VIEW emp_dep_v AS   SELECT  empno, firstnme, lastname, deptname   FROM  employee e LEFT OUTER JOIN department d                   ON  e.empno=d.mgrno; ------------------------------------------------ --  DDL Statements for table atomic_test ------------------------------------------------ CREATE  TABLE atomic_test(   proc  VARCHAR(20),   res  VARCHAR(20)); ------------------------------------------------ --  DDL Statements for sequence seq1 ------------------------------------------------ CREATE  SEQUENCE seq1; ------------------------------------------------ --  DDL Statements for table master_table ------------------------------------------------ CREATE  TABLE master_table (id INT NOT NULL, data VARCHAR(100)); ----------------------------------------------------- --  DDL Statements for index ix1 on table master_table ----------------------------------------------------- CREATE  UNIQUE INDEX ix1 ON master_table (id); ------------------------------------------------------------- --  DDL Statements for adding constraint on table master_table ------------------------------------------------------------- ALTER  TABLE master_table ADD CONSTRAINT master_pk PRIMARY KEY (id); ----------------------------------------------------- --  DDL Statements for table stage_table ----------------------------------------------------- CREATE  TABLE stage_table LIKE master_table; -------------------------------------------------------------- --  DDL Statements for user temporary table space usertempspace -------------------------------------------------------------- --  DB2/LUW CREATE  USER TEMPORARY TABLESPACE usertempspace     MANAGED BY SYSTEM USING ('usertempspace')     BUFFERPOOL ibmdefaultbp; --  DB2/390 create  database tempdb AS TEMP; create  tablespace temptbl in tempdb bufferpool BP0;