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; |