//PS112DF JOB (516B,1025),'create PSTORE Tables' //* Licensed Material - Property of IBM //* //* 5655-F31 (C) Copyright IBM Corp. 2001 //* All Rights Reserved. //* U.S. Government users - RESTRICTED RIGHTS - Use, Duplication, or //* Disclosure restricted by GSA-ADP schedule contract with IBM Corp. //* Status = H28W401 //* This program may be used, executed, copied, modified and distributed //* without royalty for the purpose of developing, using, marketing, //* or distributing. //* //* 1) FILL AN THE APPROPRIATE JOB CARD ABOVE //* 2) FILL IN THE APPROPRIATE DB2 SUBSYSTEM NAME %DB2 SSN% //* 3) FILL IN THE DB2 HIGH LEVEL QUALIFIER %DB2 HLQ% //* 4) ALL THE TABLES AND INDEXES ARE PREFIXED WITH THE ID THAT THE //* SERVER REGIONS RUN UNDER (CBASRU1), IF YOUR SERVER REGION //* USES A DIFFERENT ID THEN USE A GLOBAL CHANGE OF CBASRU1. //* THIS VALUE CAN BE FOUND IN THE SYSTEMS MANAGEMENT END USER //* INTERFACE UNDER YOU SERVER IN A FIELD CALLED //* SERVER REGION IDENTITY. //* //* CHANGE HISTORY: //* 12/10/01: HME added grant statement for CBASRU1 to sysibm.systables //* 08/15/02: JCL in 2001 version of file needed to be updated //* //DROPTAB EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(%DB2 SSN%) RUN PROGRAM(DSNTIAD) PLAN(DSNTIA71) - LIB('%DB2 HLQ%.RUNLIB.LOAD') END //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSIN DD * drop tablespace ESTOREDB.ESTORETS ;commit; drop database ESTOREDB ;commit; create database ESTOREDB ;commit; CREATE TABLESPACE ESTORETS IN ESTOREDB ;commit; create table CBASRU1.supplier ( suppid int not null, name varchar(80), status char(2) not null, addr1 varchar(80), addr2 varchar(80), city varchar(80), state varchar(80), zip char(5) , phone varchar(80) , PRIMARY KEY(suppid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.supplierix on CBASRU1.supplier(suppid ASC) ;commit; create table CBASRU1.signon ( username varchar(25) not null, password varchar(25) not null, primary key (username))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.signonix on CBASRU1.signon(username ASC) ;commit; create table CBASRU1.account ( userid varchar(80) not null, email varchar(80) not null, firstname varchar(80) not null, lastname varchar(80) not null, status char(2) , addr1 varchar(80) not null, addr2 varchar(40) , city varchar(80) not null, state varchar(80) not null, zip varchar(20) not null, country varchar(20) not null, phone varchar(80) not null, primary key (userid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.accountix on CBASRU1.account(userid ASC) ;commit; create table CBASRU1.profile ( userid varchar(80) not null, langpref varchar(80) not null, favcategory varchar(30), mylistopt int, banneropt int, primary key (userid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.profileix on CBASRU1.profile(userid ASC) ;commit; create table CBASRU1.bannerdata ( favcategory varchar(80) not null, bannername varchar(255), primary key (favcategory))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.bannerdataix on CBASRU1.bannerdata(favcategory ASC) ;commit; create table CBASRU1.orders ( orderid int not null, userid varchar(80) not null, orderdate date not null, shipaddr1 varchar(80) not null, shipaddr2 varchar(80) , shipcity varchar(80) not null, shipstate varchar(80) not null, shipzip varchar(20) not null, shipcountry varchar(20) not null, billaddr1 varchar(80) not null, billaddr2 varchar(80) , billcity varchar(80) not null, billstate varchar(80) not null, billzip varchar(20) not null, billcountry varchar(20) not null, courier varchar(80) not null, totalprice decimal(10, 2) not null, billtofirstname varchar(80) not null, billtolastname varchar(80) not null, shiptofirstname varchar(80) not null, shiptolastname varchar(80) not null, creditcard varchar(80) not null, exprdate char(7) not null, cardtype varchar(80) not null, locale varchar(20) not null, primary key (orderid), foreign key (userid) references CBASRU1.account (userid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.ordersix on CBASRU1.orders(orderid ASC) ;commit; create table CBASRU1.orderstatus ( orderid int not null, linenum int not null, updatedate date not null, status char(2) not null, primary key (orderid, linenum), foreign key (orderid) references CBASRU1.orders (orderid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.orderstatusix on CBASRU1.orderstatus(orderid ,linenum ASC) ;commit; create table CBASRU1.category ( catid char(10) not null, name varchar(80) , descn varchar(255) , primary key (catid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.categoryix on CBASRU1.category(catid ASC) ;commit; create table CBASRU1.product ( productid char(10) not null, category char(10) not null, name varchar(80) , descn varchar(255) , primary key (productid), foreign key (category) references CBASRU1.category (catid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.productix on CBASRU1.product(productid ASC) ;commit; create table CBASRU1.item ( itemid char(10) not null, productid char(10) not null, listprice decimal(10,2) , unitcost decimal(10,2) , supplier int , status char(2) , attr1 varchar(80) , attr2 varchar(80) , attr3 varchar(80) , attr4 varchar(80) , attr5 varchar(80) , primary key (itemid), foreign key (productid) references CBASRU1.product (productid), foreign key (supplier) references CBASRU1.supplier (suppid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.itemix on CBASRU1.item(itemid ASC) ;commit; create table CBASRU1.inventory ( itemid char(10) not null, qty int not null)IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.inventoryix on CBASRU1.inventory(itemid ASC) ;commit; create table CBASRU1.lineitem ( orderid int not null, linenum int not null, itemid char(10) not null, quantity int not null, unitprice decimal(10,2) not null, primary key (orderid, linenum), foreign key (orderid) references CBASRU1.orders (orderid))IN ESTOREDB.ESTORETS ;commit; CREATE Unique INDEX CBASRU1.lineitemix on CBASRU1.lineitem(orderid,linenum ASC) ;commit; create table CBASRU1.sequence ( seqnum int not null) IN ESTOREDB.ESTORETS ;commit; CREATE INDEX CBASRU1.seqix on CBASRU1.sequence(seqnum ASC) ;commit; GRANT SELECT ON TABLE sysibm.systables to CBASRU1 ;commit; INSERT INTO CBASRU1.sequence values (1) ;commit; INSERT INTO CBASRU1.signon VALUES('j2ee','j2ee') ;commit; INSERT INTO CBASRU1.account VALUES('j2ee', 'yourname@yourdomain.com', 'ABC', 'XYZ', 'OK', '901 San Antonio Road', 'MS UCUP02-206', 'Palo Alto', 'CA', '94303', 'USA', '555-555-5555') ;commit; INSERT INTO CBASRU1.profile VALUES('j2ee','English','dogs',1,1) ;commit; INSERT INTO CBASRU1.bannerdata VALUES ('fish',' ') ;commit; INSERT INTO CBASRU1.bannerdata VALUES ('cats',' ') ;commit; INSERT INTO CBASRU1.bannerdata VALUES ('dogs',' ') ;commit; INSERT INTO CBASRU1.bannerdata VALUES ('reptiles',' ') ;commit; INSERT INTO CBASRU1.bannerdata VALUES ('birds',' ') ;commit; INSERT INTO CBASRU1.category VALUES ('FISH','Fish',' Fish') ;commit; INSERT INTO CBASRU1.category VALUES ('DOGS','Dogs',' Dogs') ;commit; INSERT INTO CBASRU1.category VALUES ('REPTILES','Reptiles',' Reptiles') ;commit; INSERT INTO CBASRU1.category VALUES ('CATS','Cats',' Cats') ;commit; INSERT INTO CBASRU1.category VALUES ('BIRDS','Birds',' Birds') ;commit; INSERT INTO CBASRU1.product VALUES ('FI-SW-01','FISH','Angelfish',' Salt Water fish from Australia') ;commit; INSERT INTO CBASRU1.product VALUES ('FI-SW-02','FISH','Tiger Shark',' Salt Water fish from Australia') ;commit; INSERT INTO CBASRU1.product VALUES ('FI-FW-01','FISH', 'Koi',' Fresh Water fish from Japan') ;commit; INSERT INTO CBASRU1.product VALUES ('FI-FW-02','FISH', 'Goldfish',' Fresh Water fish from China') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-BD-01','DOGS','Bulldog',' Friendly dog from England') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-PO-02','DOGS','Poodle',' Cute dog from France') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-DL-01','DOGS', 'Dalmation',' Great dog for a Fire Station') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-RT-01','DOGS', 'Golden Retriever',' Great family dog') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-RT-02','DOGS', 'Labrador Retriever',' Great hunting dog') ;commit; INSERT INTO CBASRU1.product VALUES ('K9-CW-01','DOGS', 'Chihuahua',' Great companion dog') ;commit; INSERT INTO CBASRU1.product VALUES ('RP-SN-01','REPTILES','Rattlesnake',' Doubles as a watch dog') ;commit; INSERT INTO CBASRU1.product VALUES ('RP-LI-02','REPTILES','Iguana',' Friendly green friend') ;commit; INSERT INTO CBASRU1.product VALUES ('FL-DSH-01','CATS','Manx',' Great for reducing mouse populations') ;commit; INSERT INTO CBASRU1.product VALUES ('FL-DLH-02','CATS','Persian',' Friendly house cat, doubles as a princess') ;commit; INSERT INTO CBASRU1.product VALUES ('AV-CB-01','BIRDS','Amazon Parrot',' Great companion for up to 75 years') ;commit; INSERT INTO CBASRU1.product VALUES ('AV-SB-02','BIRDS','Finch', 'Great stress reliever') ;commit; INSERT INTO CBASRU1.supplier VALUES (1,'XYZ Pets','AC','600 Avon Way','', 'Los Angeles','CA','94024','212-947-0797') ;commit; INSERT INTO CBASRU1.supplier VALUES (2,'ABC Pets','AC','700 Abalone Way','', 'San Francisco','CA','94024','415-947-0797') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1, attr2) VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large', 'Cuddly') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1, attr2) VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless', 'Mean') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-24','K9-RT-02',255.50, 92.00,1,'P','Male Puppy') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-25','K9-RT-02',325.29, 90.00,1,'P','Female Puppy') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female') ;commit; INSERT INTO CBASRU1.item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female') ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-1',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-2',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-3',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-4',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-5',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-6',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-7',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-8',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-9',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-10',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-11',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-12',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-13',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-14',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-15',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-16',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-17',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-18',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-19',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-20',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-21',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-22',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-23',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-24',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-25',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-26',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-27',10) ;commit; INSERT INTO CBASRU1.inventory (itemid, qty ) VALUES ('EST-28',10) ;commit; /* //*