-- This CLP file was created using DB2LOOK Version 8.2 -- Timestamp: 10/04/2004 06:10:49 PM -- Database Name: GHTDB -- Database Manager Version: DB2/NT Version 8.2.0 -- Database Codepage: 1208 -- Database Collating Sequence is: BINARY DROP DATABASE GHTDB; create database ghtdb USING CODESET UTF8 TERRITORY US; CONNECT TO GHTDB; --Create temp tablespace create user temporary tablespace ght_tempspace managed by system using ('GHT_TEMPSPACE'); -- Mimic tablespace ALTER TABLESPACE SYSCATSPACE PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000; ALTER TABLESPACE TEMPSPACE1 PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000; ALTER TABLESPACE USERSPACE1 PREFETCHSIZE 16 OVERHEAD 24.100000 TRANSFERRATE 0.900000; --------------------------------- -- DDL Statements for distinct types and/or Abstract Data Types --------------------------------- CREATE DISTINCT TYPE "DB2ADMIN"."BIGINT1" AS "SYSIBM ".DECIMAL(19) WITH COMPARISONS; ------------------------------------------------ -- DDL Statements for types tables ------------------------------------------------ CREATE TABLE "DB2ADMIN"."TYPES_SIMPLE" ( "ID" CHAR(10) , "T_SMALLINT" SMALLINT , "T_INTEGER" INTEGER , "T_BIGINT" BIGINT , "T_DECIMAL" DECIMAL(5,0) , "T_REAL" REAL , "T_DOUBLE" DOUBLE , "T_CHARACTER" CHAR(10) , "T_VARCHAR" VARCHAR(10) , "T_LONGVARCHAR" LONG VARCHAR ) IN "USERSPACE1" ; CREATE TABLE "DB2ADMIN"."TYPES_EXTENDED" ( "ID" CHAR(10) , "T_DATE" DATE , "T_TIME" TIME , "T_TIMESTAMP" TIMESTAMP , "T_BLOB" BLOB(1048576) LOGGED COMPACT , "T_CLOB" CLOB(1048576) LOGGED COMPACT , "T_DBCLOB" DBCLOB(1048576) LOGGED COMPACT ) IN "USERSPACE1" ; CREATE TABLE "DB2ADMIN"."TYPES_SPECIFIC" ( "ID" CHAR(10) , "T_GRAPHIC" GRAPHIC(10) , "T_VARGRAPHIC" VARGRAPHIC(10) , "T_LONGVARGRAPHIC" LONG VARGRAPHIC ) IN "USERSPACE1" ; ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."CATEGORIES" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."CATEGORIES" ( "CATEGORYID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "CATEGORYNAME" VARCHAR(15) NOT NULL , "DESCRIPTION" DBCLOB(1073741823) NOT LOGGED NOT COMPACT ) IN "USERSPACE1" ; CREATE TABLE "MAINSOFT"."CATEGORIESNEW" ( "CATEGORYID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "CATEGORYNAME" VARCHAR(15) NOT NULL , "DESCRIPTION" DBCLOB(1073741823) NOT LOGGED NOT COMPACT ) IN "USERSPACE1" ; CREATE TABLE "MAINSOFT"."CATEGORIES" ( "CATEGORYID" VARCHAR(10) NOT NULL , "CATEGORYNAME" VARCHAR(15) NOT NULL , "DESCRIPTION" DBCLOB(1073741823) NOT LOGGED NOT COMPACT) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."CATEGORIES" CREATE INDEX "DB2ADMIN"."CATEGORYNAME" ON "DB2ADMIN"."CATEGORIES" ("CATEGORYNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CATEGORIES" CREATE INDEX "DB2ADMIN"."PK_CATEGORIES" ON "DB2ADMIN"."CATEGORIES" ("CATEGORYID" ASC) CLUSTER ; -- DDL Statements for primary key on Table "DB2ADMIN"."CATEGORIES" ALTER TABLE "DB2ADMIN"."CATEGORIES" ADD CONSTRAINT "SQL040118121530460" PRIMARY KEY ("CATEGORYID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ( "CUSTOMERID" CHAR(5) NOT NULL , "CUSTOMERTYPEID" CHAR(10) NOT NULL ) IN "USERSPACE1" ; -- DDL Statements for primary key on Table "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ALTER TABLE "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ADD CONSTRAINT "PK_CUSTOMERCUSTOME" PRIMARY KEY ("CUSTOMERID", "CUSTOMERTYPEID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."CUSTOMERDEMOGRAPHICS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."CUSTOMERDEMOGRAPHICS" ( "CUSTOMERTYPEID" CHAR(10) NOT NULL , "CUSTOMERDESC" DBCLOB(1073741823) NOT LOGGED NOT COMPACT ) IN "USERSPACE1" ; -- DDL Statements for primary key on Table "DB2ADMIN"."CUSTOMERDEMOGRAPHICS" ALTER TABLE "DB2ADMIN"."CUSTOMERDEMOGRAPHICS" ADD CONSTRAINT "PK_CUSTOMERDEMOGRA" PRIMARY KEY ("CUSTOMERTYPEID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."CUSTOMERS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."CUSTOMERS" ( "CUSTOMERID" CHAR(5) NOT NULL , "COMPANYNAME" VARCHAR(40) NOT NULL , "CONTACTNAME" VARCHAR(30) , "CONTACTTITLE" VARCHAR(30) , "ADDRESS" VARCHAR(60) , "CITY" VARCHAR(15) , "REGION" VARCHAR(15) , "POSTALCODE" VARCHAR(10) , "COUNTRY" VARCHAR(15) , "PHONE" VARCHAR(24) , "FAX" VARCHAR(24) ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."CITY" ON "DB2ADMIN"."CUSTOMERS" ("CITY" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."COMPANYNAME" ON "DB2ADMIN"."CUSTOMERS" ("COMPANYNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."PK_CUSTOMERS" ON "DB2ADMIN"."CUSTOMERS" ("CUSTOMERID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."POSTALCODE" ON "DB2ADMIN"."CUSTOMERS" ("POSTALCODE" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."REGION" ON "DB2ADMIN"."CUSTOMERS" ("REGION" ASC); -- DDL Statements for unique constraints on Table "DB2ADMIN"."CUSTOMERS" ALTER TABLE "DB2ADMIN"."CUSTOMERS" ADD CONSTRAINT "UK_CUSTOMERID" UNIQUE ("CUSTOMERID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."EMPLOYEETERRITORIES" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."EMPLOYEETERRITORIES" ( "EMPLOYEEID" INTEGER NOT NULL , "TERRITORYID" VARCHAR(20) NOT NULL ) IN "USERSPACE1" ; -- DDL Statements for primary key on Table "DB2ADMIN"."EMPLOYEETERRITORIES" ALTER TABLE "DB2ADMIN"."EMPLOYEETERRITORIES" ADD CONSTRAINT "PK_EMPLOYEETERRITO" PRIMARY KEY ("EMPLOYEEID", "TERRITORYID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."SHIPPERS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."SHIPPERS" ( "SHIPPERID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "COMPANYNAME" VARCHAR(40) NOT NULL , "PHONE" VARCHAR(24) ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."SHIPPERS" CREATE INDEX "DB2ADMIN"."PK_SHIPPERS" ON "DB2ADMIN"."SHIPPERS" ("SHIPPERID" ASC) CLUSTER ; -- DDL Statements for primary key on Table "DB2ADMIN"."SHIPPERS" ALTER TABLE "DB2ADMIN"."SHIPPERS" ADD CONSTRAINT "SQL040118121538100" PRIMARY KEY ("SHIPPERID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."ORDERS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."ORDERS" ( "ORDERID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "CUSTOMERID" CHAR(5) , "EMPLOYEEID" INTEGER , "ORDERDATE" DATE , "REQUIREDDATE" DATE , "SHIPPEDDATE" DATE , "SHIPVIA" INTEGER , "FREIGHT" DECIMAL(19,4) WITH DEFAULT 0 , "SHIPNAME" VARCHAR(40) , "SHIPADDRESS" VARCHAR(60) , "SHIPCITY" VARCHAR(15) , "SHIPREGION" VARCHAR(15) , "SHIPPOSTALCODE" VARCHAR(10) , "SHIPCOUNTRY" VARCHAR(15) ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."CUSTOMERID" ON "DB2ADMIN"."ORDERS" ("CUSTOMERID" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."EMPLOYEEID" ON "DB2ADMIN"."ORDERS" ("EMPLOYEEID" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."ORDERDATE" ON "DB2ADMIN"."ORDERS" ("ORDERDATE" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."PK_ORDERS" ON "DB2ADMIN"."ORDERS" ("ORDERID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."SHIPPEDDATE" ON "DB2ADMIN"."ORDERS" ("SHIPPEDDATE" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."SHIPPERSORDERS" ON "DB2ADMIN"."ORDERS" ("SHIPVIA" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."ORDERS" CREATE INDEX "DB2ADMIN"."SHIPPOSTALCODE" ON "DB2ADMIN"."ORDERS" ("SHIPPOSTALCODE" ASC); -- DDL Statements for primary key on Table "DB2ADMIN"."ORDERS" ALTER TABLE "DB2ADMIN"."ORDERS" ADD CONSTRAINT "SQL040118121536160" PRIMARY KEY ("ORDERID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."PRODUCTS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."PRODUCTS" ( "PRODUCTID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "PRODUCTNAME" VARCHAR(40) NOT NULL , "SUPPLIERID" INTEGER , "CATEGORYID" INTEGER , "QUANTITYPERUNIT" VARCHAR(20) , "UNITPRICE" DECIMAL(19,4) WITH DEFAULT 0 , "UNITSINSTOCK" SMALLINT WITH DEFAULT 0 , "UNITSONORDER" SMALLINT WITH DEFAULT 0 , "REORDERLEVEL" SMALLINT WITH DEFAULT 0 , "DISCONTINUED" SMALLINT NOT NULL WITH DEFAULT 0 ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."PRODUCTS" CREATE INDEX "DB2ADMIN"."CATEGORIESPRODUCTS" ON "DB2ADMIN"."PRODUCTS" ("CATEGORYID" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."PRODUCTS" CREATE INDEX "DB2ADMIN"."PK_PRODUCTS" ON "DB2ADMIN"."PRODUCTS" ("PRODUCTID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."PRODUCTS" CREATE INDEX "DB2ADMIN"."PRODUCTNAME" ON "DB2ADMIN"."PRODUCTS" ("PRODUCTNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."PRODUCTS" CREATE INDEX "DB2ADMIN"."SUPPLIERID" ON "DB2ADMIN"."PRODUCTS" ("SUPPLIERID" ASC); -- DDL Statements for primary key on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "SQL040118121537130" PRIMARY KEY ("PRODUCTID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."REGION" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."REGION" ( "REGIONID" INTEGER NOT NULL , "REGIONDESCRIPTION" CHAR(50) NOT NULL ) IN "USERSPACE1" ; -- DDL Statements for unique constraints on Table "DB2ADMIN"."REGION" ALTER TABLE "DB2ADMIN"."REGION" ADD CONSTRAINT "UK_REGION" UNIQUE ("REGIONID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."SUPPLIERS" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."SUPPLIERS" ( "SUPPLIERID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE CACHE 20 NO ORDER ) , "COMPANYNAME" VARCHAR(40) NOT NULL , "CONTACTNAME" VARCHAR(30) , "CONTACTTITLE" VARCHAR(30) , "ADDRESS" VARCHAR(60) , "CITY" VARCHAR(15) , "REGION" VARCHAR(15) , "POSTALCODE" VARCHAR(10) , "COUNTRY" VARCHAR(15) , "PHONE" VARCHAR(24) , "FAX" VARCHAR(24) , "HOMEPAGE" DBCLOB(1073741823) NOT LOGGED NOT COMPACT ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."SUPPLIERS" CREATE INDEX "DB2ADMIN"."COMPANYNAME1" ON "DB2ADMIN"."SUPPLIERS" ("COMPANYNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."SUPPLIERS" CREATE INDEX "DB2ADMIN"."PK_SUPPLIERS" ON "DB2ADMIN"."SUPPLIERS" ("SUPPLIERID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."SUPPLIERS" CREATE INDEX "DB2ADMIN"."POSTALCODE2" ON "DB2ADMIN"."SUPPLIERS" ("POSTALCODE" ASC); -- DDL Statements for primary key on Table "DB2ADMIN"."SUPPLIERS" ALTER TABLE "DB2ADMIN"."SUPPLIERS" ADD CONSTRAINT "SQL040118121538820" PRIMARY KEY ("SUPPLIERID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."TERRITORIES" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."TERRITORIES" ( "TERRITORYID" VARCHAR(20) NOT NULL , "TERRITORYDESCRIPTION" CHAR(50) NOT NULL , "REGIONID" INTEGER NOT NULL ) IN "USERSPACE1" ; -- DDL Statements for primary key on Table "DB2ADMIN"."TERRITORIES" ALTER TABLE "DB2ADMIN"."TERRITORIES" ADD CONSTRAINT "PK_TERRITORIES" PRIMARY KEY ("TERRITORYID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."EMPLOYEES" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."EMPLOYEES" ( "EMPLOYEEID" INTEGER NOT NULL , "LASTNAME" VARCHAR(20) NOT NULL , "FIRSTNAME" VARCHAR(10) NOT NULL , "TITLE" VARCHAR(30) , "TITLEOFCOURTESY" VARCHAR(25) , "BIRTHDATE" TIMESTAMP , "HIREDATE" TIMESTAMP , "ADDRESS" VARCHAR(60) , "CITY" VARCHAR(15) , "REGION" VARCHAR(15) , "POSTALCODE" VARCHAR(10) , "COUNTRY" VARCHAR(15) , "HOMEPHONE" VARCHAR(24) , "EXTENSION" VARCHAR(4) , "NOTES" DBCLOB(1073741823) NOT LOGGED NOT COMPACT , "REPORTSTO" INTEGER , "PHOTOPATH" VARCHAR(255) , "MYCOLUMN" INTEGER ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."EMPLOYEES" CREATE INDEX "DB2ADMIN"."LASTNAME" ON "DB2ADMIN"."EMPLOYEES" ("LASTNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."EMPLOYEES" CREATE INDEX "DB2ADMIN"."PK_EMPLOYEES" ON "DB2ADMIN"."EMPLOYEES" ("EMPLOYEEID" ASC) CLUSTER ; -- DDL Statements for primary key on Table "DB2ADMIN"."EMPLOYEES" ALTER TABLE "DB2ADMIN"."EMPLOYEES" ADD CONSTRAINT "SQL040126121512760" PRIMARY KEY ("EMPLOYEEID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."Order Details" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."Order Details" ( "ORDERID" INTEGER NOT NULL , "PRODUCTID" INTEGER NOT NULL , "UNITPRICE" DECIMAL(19,4) NOT NULL WITH DEFAULT 0 , "QUANTITY" SMALLINT NOT NULL WITH DEFAULT 1 , "DISCOUNT" REAL NOT NULL WITH DEFAULT 0 ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."Order Details" CREATE INDEX "DB2ADMIN"."ORDERID" ON "DB2ADMIN"."Order Details" ("ORDERID" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."Order Details" CREATE INDEX "DB2ADMIN"."PK_ORDER_DETAILS" ON "DB2ADMIN"."Order Details" ("ORDERID" ASC, "PRODUCTID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."Order Details" CREATE INDEX "DB2ADMIN"."PRODUCTID" ON "DB2ADMIN"."Order Details" ("PRODUCTID" ASC); -- DDL Statements for primary key on Table "DB2ADMIN"."Order Details" ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "SQL040127121002770" PRIMARY KEY ("ORDERID", "PRODUCTID"); ------------------------------------------------ -- DDL Statements for table "DB2ADMIN"."GH_EMPTYTABLE" ------------------------------------------------ CREATE TABLE "DB2ADMIN"."GH_EMPTYTABLE" ( "COL1" INTEGER , "COL2" VARCHAR(50) ) IN "USERSPACE1" ; -- DDL Statements for foreign keys on Table "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ALTER TABLE "DB2ADMIN"."CUSTOMERCUSTOMERDEMO" ADD CONSTRAINT "SQL040118143112730" FOREIGN KEY ("CUSTOMERTYPEID") REFERENCES "DB2ADMIN"."CUSTOMERDEMOGRAPHICS" ("CUSTOMERTYPEID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for foreign keys on Table "DB2ADMIN"."EMPLOYEETERRITORIES" ALTER TABLE "DB2ADMIN"."EMPLOYEETERRITORIES" ADD CONSTRAINT "SQL040118143112790" FOREIGN KEY ("TERRITORYID") REFERENCES "DB2ADMIN"."TERRITORIES" ("TERRITORYID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for foreign keys on Table "DB2ADMIN"."ORDERS" ALTER TABLE "DB2ADMIN"."ORDERS" ADD CONSTRAINT "SQL040127151054370" FOREIGN KEY ("SHIPVIA") REFERENCES "DB2ADMIN"."SHIPPERS" ("SHIPPERID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for foreign keys on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "SQL040127151054430" FOREIGN KEY ("CATEGORYID") REFERENCES "DB2ADMIN"."CATEGORIES" ("CATEGORYID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "SQL040127151054460" FOREIGN KEY ("SUPPLIERID") REFERENCES "DB2ADMIN"."SUPPLIERS" ("SUPPLIERID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "CK_PRODUCTS_UNITPR" CHECK (UNITPRICE >= 0) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "CK_REORDERLEVEL" CHECK (REORDERLEVEL >= 0) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "CK_UNITSINSTOCK" CHECK (UNITSINSTOCK >= 0) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."PRODUCTS" ALTER TABLE "DB2ADMIN"."PRODUCTS" ADD CONSTRAINT "CK_UNITSONORDER" CHECK (UNITSONORDER >= 0) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for foreign keys on Table "DB2ADMIN"."EMPLOYEES" ALTER TABLE "DB2ADMIN"."EMPLOYEES" ADD CONSTRAINT "SQL040126121642680" FOREIGN KEY ("REPORTSTO") REFERENCES "DB2ADMIN"."EMPLOYEES" ("EMPLOYEEID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for foreign keys on Table "DB2ADMIN"."Order Details" ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "SQL040127151054020" FOREIGN KEY ("ORDERID") REFERENCES "DB2ADMIN"."ORDERS" ("ORDERID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "SQL040127151054350" FOREIGN KEY ("PRODUCTID") REFERENCES "DB2ADMIN"."PRODUCTS" ("PRODUCTID") ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."Order Details" ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "CK_DISCOUNT" CHECK (DISCOUNT >= 0 AND DISCOUNT <= 1) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."Order Details" ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "CK_QUANTITY" CHECK (QUANTITY > 0) ENFORCED ENABLE QUERY OPTIMIZATION; -- DDL Statements for check constraints on Table "DB2ADMIN"."Order Details" ALTER TABLE "DB2ADMIN"."Order Details" ADD CONSTRAINT "CK_UNITPRICE" CHECK (UNITPRICE >= 0) ENFORCED ENABLE QUERY OPTIMIZATION; CREATE view DB2ADMIN.CURRENT_PRODUCT_LIST AS SELECT PRODUCTS.ProductName,PRODUCTS.UnitPrice FROM DB2ADMIN.PRODUCTS AS PRODUCTS where PRODUCTS.UnitPrice > (select AVG(DB2ADMIN.PRODUCTS.UnitPrice) from DB2ADMIN.PRODUCTS); -------------------------------------------------------- -- Database and Database Manager configuration parameters -------------------------------------------------------- UPDATE DBM CFG USING cpuspeed 3.306410e-007; UPDATE DBM CFG USING intra_parallel NO; UPDATE DBM CFG USING federated YES; UPDATE DBM CFG USING fed_noauth NO; UPDATE DB CFG FOR GHTDB USING locklist 25; UPDATE DB CFG FOR GHTDB USING dft_degree 1; UPDATE DB CFG FOR GHTDB USING maxlocks 22; UPDATE DB CFG FOR GHTDB USING avg_appls 1; UPDATE DB CFG FOR GHTDB USING stmtheap 2048; UPDATE DB CFG FOR GHTDB USING dft_queryopt 5; --------------------------------- -- Environment Variables settings --------------------------------- --COMMIT WORK; ---CONNECT RESET; --TERMINATE; -- USER is: -- Specified SCHEMA is: DB2ADMIN -- The db2look utility will consider only the specified tables -- Creating DDL for table(s) -- Schema name is ignored for the Federated Section ; COMMIT WORK; CONNECT RESET; --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- DROP DATABASE GHTDB_EX; create database ghtdb_EX USING CODESET UTF8 TERRITORY US; CONNECT TO GHTDB_EX; CREATE TABLE "DB2ADMIN"."CUSTOMERS" ( "CUSTOMERID" CHAR(10) NOT NULL , "COMPANYNAME" VARCHAR(40) NOT NULL , "CONTACTNAME" VARCHAR(30) , "CONTACTTITLE" VARCHAR(30) , "ADDRESS" VARCHAR(60) , "CITY" VARCHAR(15) , "REGION" VARCHAR(15) , "POSTALCODE" VARCHAR(10) , "COUNTRY" VARCHAR(15) , "PHONE" VARCHAR(24) , "FAX" VARCHAR(24) ) IN "USERSPACE1" ; -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."CITY" ON "DB2ADMIN"."CUSTOMERS" ("CITY" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."COMPANYNAME" ON "DB2ADMIN"."CUSTOMERS" ("COMPANYNAME" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."PK_CUSTOMERS" ON "DB2ADMIN"."CUSTOMERS" ("CUSTOMERID" ASC) CLUSTER ; -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."POSTALCODE" ON "DB2ADMIN"."CUSTOMERS" ("POSTALCODE" ASC); -- DDL Statements for indexes on Table "DB2ADMIN"."CUSTOMERS" CREATE INDEX "DB2ADMIN"."REGION" ON "DB2ADMIN"."CUSTOMERS" ("REGION" ASC); -- DDL Statements for unique constraints on Table "DB2ADMIN"."CUSTOMERS" ALTER TABLE "DB2ADMIN"."CUSTOMERS" ADD CONSTRAINT "UK_CUSTOMERID" UNIQUE ("CUSTOMERID"); COMMIT WORK; CONNECT RESET; TERMINATE;