DSNTEJ1
THIS JCL PERFORMS THE PHASE 1 SETUP FOR THE SAMPLE APPLICATIONS ON A LOCAL Db2 SYSTEM (A SYSTEM WHICH WILL CONTAIN THE ACTUAL SAMPLE TABLES).
//*********************************************************************
//* NAME = DSNTEJ1
//*
//* DESCRIPTIVE NAME = DB2 SAMPLE APPLICATION
//* PHASE 1 (LOCAL SITE ONLY)
//*
//* Licensed Materials - Property of IBM
//* 5698-DB2
//* COPYRIGHT IBM CORP 1982, 2022
//*
//* STATUS = Version 13
//*
//* FUNCTION = THIS JCL PERFORMS THE PHASE 1 SETUP FOR THE SAMPLE
//* APPLICATIONS ON A LOCAL DB2 SYSTEM (A SYSTEM WHICH
//* WILL CONTAIN THE ACTUAL SAMPLE TABLES).
//*
//* NOTICE =
//* THIS SAMPLE JOB USES DB2 UTILITIES. SOME UTILITY FUNCTIONS ARE
//* ELEMENTS OF SEPARATELY ORDERABLE PRODUCTS. SUCCESSFUL USE OF
//* A PARTICULAR SAMPLE JOB MAY BE DEPENDENT UPON THE OPTIONAL
//* PRODUCT BEING LICENSED AND INSTALLED IN YOUR ENVIRONMENT.
//*
//* CHANGE ACTIVITY =
//* 10/20/2012 Create ALIASes instead of SYNONYMs N0102 / 163503
//* 11/07/2012 ADD SET CURRENT SQLID DN1651_INST1 / DN1651
//* 05/17/2013 FIX COPYRIGHT STATEMENT. 49779_077_724
//* 10/29/2013 Create EMP as PBR not index partitioned PM98424
//* 02/24/2014 Increase length of the LRSN column in PI10794
//* MAP_TBL from CHAR(06) to CHAR(10)
//* 02/15/2016 Long data set names PI42601
//* 10/27/2016 Update MAP_TBL definition for DB2 12 PI66261
//* 08/21/2018 Tolerate creation of deprecated TSs s81673 / t81374
//* 12/01/2021 Remove tolerance for deprecated TSs e6837 / s15746
//* 01/26/2022 Update RID size from CHAR(5) to CHAR(7) dq17293
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
//*
//*********************************************************************
//* ASMCL PROC - ASSEMBLE AND LINKEDIT AN ASM PROGRAM
//*
//ASMCL PROC WSPC=500,MEM=TEMPNAME
//*
//* ASSEMBLE
//*
//ASM EXEC PGM=ASMA90,PARM='OBJECT,NODECK'
//SYSIN DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(&MEM)
//SYSLIB DD DSN=SYS1.MACLIB,DISP=SHR
// DD DSN=DSN!!0.SDSNMACS,DISP=SHR
// DD DSN=DSN!!0.SDSNSAMP,DISP=SHR
//SYSLIN DD DSN=&&LOADSET,DISP=(MOD,PASS),
// UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC)),
// DCB=(BLKSIZE=800)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//SYSUT2 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//SYSUT3 DD UNIT=SYSDA,SPACE=(800,(&WSPC,&WSPC),,,ROUND)
//*
//* LINKEDIT IF THE ASSEMBLER
//* RETURN CODE IS 4 OR LESS
//*
//LKED EXEC PGM=IEWL,PARM='LIST,XREF,NCAL,RENT,AMODE=31,RMODE=ANY',
// COND=(4,LT,ASM)
//SYSLIN DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//SYSLMOD DD DISP=SHR,
// DSN=DSN!!0.SDSNEXIT(&MEM)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(1024,(50,50))
//*
//ASMCL PEND
//*********************************************************************
//*
//* STEP 1: CREATE SAMPLE STORAGE GROUPS, TABLESPACES
//*
//PH01S01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE STOGROUP DSN8G!!0
VOLUMES (DSNV01)
VCAT DSNC!!0;
CREATE DATABASE DSN8D!!A
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID EBCDIC;
CREATE DATABASE DSN8D!!P
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID EBCDIC;
CREATE DATABASE DSN8D!!X
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID EBCDIC;
CREATE TABLESPACE DSN8S!!D
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT ;
CREATE TABLESPACE DSN8S!!E
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
NUMPARTS 4
(PART 1 USING STOGROUP DSN8G!!0
,PART 3 USING STOGROUP DSN8G!!0
)
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
COMPRESS YES
CCSID EBCDIC;
COMMIT ;
CREATE TABLESPACE DSN8S!!A
IN DSN8D!!P
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE PAGE
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!B
IN DSN8D!!P
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE PAGE
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!C
IN DSN8D!!P
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE PAGE
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT ;
CREATE TABLESPACE DSN8S!!Q
IN DSN8D!!P
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE PAGE
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT ;
CREATE TABLESPACE DSN8S!!F
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!G
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!H
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!I
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!J
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!R
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!M
IN DSN8D!!A
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE ROW
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!N
IN DSN8D!!A
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE ROW
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!O
IN DSN8D!!A
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE ROW
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!P
IN DSN8D!!A
USING STOGROUP DSN8G!!0
SEGSIZE 4
LOCKSIZE ROW
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT ;
CREATE TABLESPACE DSN8S!!S
IN DSN8D!!A
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT;
CREATE TABLESPACE DSN8S!!T
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!U
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!V
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!W
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!X
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!Y
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLESPACE DSN8S!!Z
IN DSN8D!!X
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
COMMIT;
//*
//* STEP 2: CREATE SAMPLE TABLES, VIEWS
//*
//PH01S02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE TABLE DSN8!!0.DEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6) ,
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16) ,
PRIMARY KEY(DEPTNO))
IN DSN8D!!A.DSN8S!!D
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XDEPT1
ON DSN8!!0.DEPT
(DEPTNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.XDEPT2
ON DSN8!!0.DEPT
(MGRNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.XDEPT3
ON DSN8!!0.DEPT
(ADMRDEPT ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VDEPT
AS SELECT ALL DEPTNO ,
DEPTNAME,
MGRNO ,
ADMRDEPT
FROM DSN8!!0.DEPT;
CREATE VIEW DSN8!!0.VHDEPT
AS SELECT ALL DEPTNO ,
DEPTNAME,
MGRNO ,
ADMRDEPT,
LOCATION
FROM DSN8!!0.DEPT;
COMMIT ;
CREATE TABLE DSN8!!0.EMP
(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) CONSTRAINT NUMBER CHECK
(PHONENO >= '0000' AND PHONENO <= '9999'),
HIREDATE DATE ,
JOB CHAR(8) ,
EDLEVEL SMALLINT ,
SEX CHAR(1) ,
BIRTHDATE DATE ,
SALARY DECIMAL(9, 2) ,
BONUS DECIMAL(9, 2) ,
COMM DECIMAL(9, 2) ,
PRIMARY KEY(EMPNO),
FOREIGN KEY RED (WORKDEPT) REFERENCES DSN8!!0.DEPT
ON DELETE SET NULL)
PARTITION BY RANGE (EMPNO)
(PARTITION 1 ENDING AT('099999'),
PARTITION 2 ENDING AT('199999'),
PARTITION 3 ENDING AT('299999'),
PARTITION 4 ENDING AT('999999'))
EDITPROC DSN8EAE1
IN DSN8D!!A.DSN8S!!E
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XEMP1
ON DSN8!!0.EMP
(EMPNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.XEMP2
ON DSN8!!0.EMP
(WORKDEPT ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VEMP
AS SELECT ALL EMPNO ,
FIRSTNME,
MIDINIT ,
LASTNAME,
WORKDEPT
FROM DSN8!!0.EMP;
COMMIT ;
CREATE TABLE DSN8!!0.PROJ
(PROJNO CHAR(6) PRIMARY KEY NOT NULL,
PROJNAME VARCHAR(24) NOT NULL WITH DEFAULT
'PROJECT NAME UNDEFINED',
DEPTNO CHAR(3) NOT NULL REFERENCES
DSN8!!0.DEPT ON DELETE RESTRICT,
RESPEMP CHAR(6) NOT NULL REFERENCES
DSN8!!0.EMP ON DELETE RESTRICT,
PRSTAFF DECIMAL(5, 2) ,
PRSTDATE DATE ,
PRENDATE DATE ,
MAJPROJ CHAR(6))
IN DSN8D!!A.DSN8S!!M
CCSID EBCDIC;
ALTER TABLESPACE DSN8D!!A.DSN8S!!M CLOSE NO;
ALTER TABLESPACE DSN8D!!A.DSN8S!!E
PART 3 COMPRESS NO;
CREATE UNIQUE INDEX DSN8!!0.XPROJ1
ON DSN8!!0.PROJ
(PROJNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.XPROJ2
ON DSN8!!0.PROJ
(RESPEMP ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VPROJ
AS SELECT ALL
PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,
PRSTDATE, PRENDATE, MAJPROJ
FROM DSN8!!0.PROJ ;
COMMIT ;
CREATE TABLE DSN8!!0.ACT
(ACTNO SMALLINT NOT NULL,
ACTKWD CHAR(6) NOT NULL,
ACTDESC VARCHAR(20) NOT NULL,
PRIMARY KEY(ACTNO))
IN DSN8D!!A.DSN8S!!N
CCSID EBCDIC;
ALTER TABLESPACE DSN8D!!A.DSN8S!!N CLOSE NO;
CREATE UNIQUE INDEX DSN8!!0.XACT1
ON DSN8!!0.ACT
(ACTNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE UNIQUE INDEX DSN8!!0.XACT2
ON DSN8!!0.ACT
(ACTKWD ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VACT
AS SELECT ALL ACTNO ,
ACTKWD ,
ACTDESC
FROM DSN8!!0.ACT ;
COMMIT ;
CREATE TABLE DSN8!!0.PROJACT
(PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
ACSTAFF DECIMAL(5, 2) ,
ACSTDATE DATE NOT NULL,
ACENDATE DATE ,
PRIMARY KEY(PROJNO,ACTNO,ACSTDATE),
FOREIGN KEY RPAP (PROJNO) REFERENCES DSN8!!0.PROJ
ON DELETE RESTRICT,
FOREIGN KEY RPAA (ACTNO) REFERENCES DSN8!!0.ACT
ON DELETE RESTRICT)
IN DSN8D!!A.DSN8S!!O
CCSID EBCDIC;
ALTER TABLESPACE DSN8D!!A.DSN8S!!O CLOSE NO;
CREATE UNIQUE INDEX DSN8!!0.XPROJAC1
ON DSN8!!0.PROJACT
(PROJNO ASC,
ACTNO ASC,
ACSTDATE ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VPROJACT
AS SELECT ALL
PROJNO,ACTNO, ACSTAFF, ACSTDATE, ACENDATE
FROM DSN8!!0.PROJACT ;
COMMIT ;
CREATE TABLE DSN8!!0.EMPPROJACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5, 2) ,
EMSTDATE DATE ,
EMENDATE DATE ,
FOREIGN KEY REPAPA (PROJNO, ACTNO, EMSTDATE)
REFERENCES DSN8!!0.PROJACT
ON DELETE RESTRICT,
FOREIGN KEY REPAE (EMPNO) REFERENCES DSN8!!0.EMP
ON DELETE RESTRICT)
IN DSN8D!!A.DSN8S!!P
CCSID EBCDIC;
ALTER TABLESPACE DSN8D!!A.DSN8S!!P CLOSE NO;
CREATE UNIQUE INDEX DSN8!!0.XEMPPROJACT1
ON DSN8!!0.EMPPROJACT
(PROJNO ASC,
ACTNO ASC,
EMSTDATE ASC,
EMPNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE INDEX DSN8!!0.XEMPPROJACT2
ON DSN8!!0.EMPPROJACT
(EMPNO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VEMPPROJACT
AS SELECT ALL
EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, EMENDATE
FROM DSN8!!0.EMPPROJACT ;
COMMIT ;
CREATE TABLE DSN8!!0.PARTS
(ITEMNUM CHAR(6) NOT NULL,
DESCRIPT VARCHAR(30) NOT NULL,
COLOR VARCHAR(8) ,
SUPPLIER VARCHAR(15) NOT NULL)
IN DSN8D!!A.DSN8S!!S
CCSID EBCDIC;
CREATE INDEX DSN8!!0.XPARTS
ON DSN8!!0.PARTS
(ITEMNUM ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
COMMIT ;
CREATE TABLE DSN8!!0.TCONA
(CONVID CHAR(16) NOT NULL,
LASTSCR CHAR(8) NOT NULL,
LASTPOS CHAR(254) NOT NULL,
LASTPOSC CHAR(254) NOT NULL,
LASTMSG VARCHAR(1609) NOT NULL)
IN DSN8D!!P.DSN8S!!A
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XCONA1
ON DSN8!!0.TCONA
(CONVID ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VCONA
AS SELECT ALL
CONVID, LASTSCR, LASTPOS, LASTPOSC, LASTMSG
FROM DSN8!!0.TCONA ;
COMMIT ;
CREATE TABLE DSN8!!0.TOPTVAL
(MAJSYS CHAR(1) NOT NULL,
ACTION CHAR(1) NOT NULL,
OBJFLD CHAR(2) NOT NULL,
SRCHCRIT CHAR(2) NOT NULL,
SCRTYPE CHAR(1) NOT NULL,
HEADTXT CHAR(50) NOT NULL,
SELTXT CHAR(50) NOT NULL,
INFOTXT CHAR(79) NOT NULL,
HELPTXT CHAR(79) NOT NULL,
PFKTXT CHAR(79) NOT NULL,
DSPINDEX CHAR(2) NOT NULL)
IN DSN8D!!P.DSN8S!!B
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XOPTVAL1
ON DSN8!!0.TOPTVAL
(MAJSYS ASC,
ACTION ASC,
OBJFLD ASC,
SRCHCRIT ASC,
SCRTYPE ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VOPTVAL
AS SELECT ALL
MAJSYS, ACTION, OBJFLD, SRCHCRIT, SCRTYPE, HEADTXT,
SELTXT, INFOTXT, HELPTXT, PFKTXT, DSPINDEX
FROM DSN8!!0.TOPTVAL ;
COMMIT ;
CREATE TABLE DSN8!!0.TDSPTXT
(DSPINDEX CHAR(2) NOT NULL,
LINENO CHAR(2) NOT NULL,
DSPLINE CHAR(79) NOT NULL)
IN DSN8D!!P.DSN8S!!C
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XDSPTXT1
ON DSN8!!0.TDSPTXT
(DSPINDEX ASC,
LINENO ASC)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE VIEW DSN8!!0.VDSPTXT
AS SELECT ALL
DSPINDEX, LINENO, DSPLINE
FROM DSN8!!0.TDSPTXT ;
COMMIT;
ALTER TABLE DSN8!!0.DEPT
FOREIGN KEY RDD (ADMRDEPT) REFERENCES DSN8!!0.DEPT
ON DELETE CASCADE;
ALTER TABLE DSN8!!0.DEPT
FOREIGN KEY RDE (MGRNO) REFERENCES DSN8!!0.EMP
ON DELETE SET NULL;
ALTER TABLE DSN8!!0.PROJ
FOREIGN KEY RPP (MAJPROJ) REFERENCES DSN8!!0.PROJ
ON DELETE CASCADE;
COMMIT;
CREATE TABLE DSN8!!0.EDEPT LIKE DSN8!!0.DEPT
IN DSN8D!!A.DSN8S!!F ;
CREATE TABLE DSN8!!0.EEMP LIKE DSN8!!0.EMP
IN DSN8D!!A.DSN8S!!G ;
CREATE TABLE DSN8!!0.EPROJ LIKE DSN8!!0.PROJ
IN DSN8D!!A.DSN8S!!H ;
CREATE TABLE DSN8!!0.EACT LIKE DSN8!!0.ACT
IN DSN8D!!A.DSN8S!!I ;
CREATE TABLE DSN8!!0.EPROJACT LIKE DSN8!!0.PROJACT
IN DSN8D!!A.DSN8S!!J ;
CREATE TABLE DSN8!!0.EEPA LIKE DSN8!!0.EMPPROJACT
IN DSN8D!!A.DSN8S!!R ;
COMMIT;
ALTER TABLE DSN8!!0.EDEPT
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EDEPT
ADD TSTAMP TIMESTAMP;
ALTER TABLE DSN8!!0.EEMP
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EEMP
ADD TSTAMP TIMESTAMP;
ALTER TABLE DSN8!!0.EPROJ
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EPROJ
ADD TSTAMP TIMESTAMP;
ALTER TABLE DSN8!!0.EACT
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EACT
ADD TSTAMP TIMESTAMP;
ALTER TABLE DSN8!!0.EPROJACT
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EPROJACT
ADD TSTAMP TIMESTAMP;
ALTER TABLE DSN8!!0.EEPA
ADD RID CHAR(7);
ALTER TABLE DSN8!!0.EEPA
ADD TSTAMP TIMESTAMP;
COMMIT;
CREATE SEQUENCE DSN8!!0.POID
AS BIGINT
START WITH 1000
INCREMENT BY 1;
CREATE SEQUENCE DSN8!!0.CID
AS BIGINT
START WITH 1000
INCREMENT BY 1;
CREATE TABLE DSN8!!0.PRODUCT
( PID VARCHAR(10) NOT NULL PRIMARY KEY
,NAME VARCHAR(128)
,PRICE DECIMAL(30, 2)
,PROMOPRICE DECIMAL(30, 2)
,PROMOSTART DATE
,PROMOEND DATE
,DESCRIPTION XML )
IN DSN8D!!X.DSN8S!!T
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.PROD_NAME_PIDX
ON DSN8!!0.PRODUCT(PID)
USING STOGROUP DSN8G!!0;
CREATE INDEX DSN8!!0.PROD_NAME_XMLIDX
ON DSN8!!0.PRODUCT(DESCRIPTION)
GENERATE KEY USING XMLPATTERN '/product/description/name'
AS SQL VARCHAR(128)
USING STOGROUP DSN8G!!0;
CREATE INDEX DSN8!!0.PROD_DETAIL_XMLIDX
ON DSN8!!0.PRODUCT(DESCRIPTION)
GENERATE KEY USING XMLPATTERN '/product/description/detail'
AS SQL VARCHAR(128)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.INVENTORY
( PID VARCHAR(10) NOT NULL PRIMARY KEY,
QUANTITY INTEGER,
LOCATION VARCHAR(128) )
IN DSN8D!!X.DSN8S!!U
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.INVENTORY_PIDX
ON DSN8!!0.INVENTORY(PID)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.CUSTOMER
( CID BIGINT NOT NULL PRIMARY KEY,
INFO XML,
HISTORY XML )
IN DSN8D!!X.DSN8S!!V
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.CUSTOMER_CIDX
ON DSN8!!0.CUSTOMER(CID)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.PURCHASEORDER
( POID BIGINT NOT NULL PRIMARY KEY,
STATUS VARCHAR(10) NOT NULL WITH DEFAULT 'New',
PORDER XML )
IN DSN8D!!X.DSN8S!!W
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.PURCHASEORDER_POIDX
ON DSN8!!0.PURCHASEORDER(POID)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.CATALOG
( NAME VARCHAR(128) NOT NULL PRIMARY KEY,
CATLOG XML )
IN DSN8D!!X.DSN8S!!X
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.CATALOG_NAMEX
ON DSN8!!0.CATALOG(NAME)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.SUPPLIERS
( SID VARCHAR(10) NOT NULL PRIMARY KEY,
ADDR XML )
IN DSN8D!!X.DSN8S!!Y
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.SUPPLIERS_SIDX
ON DSN8!!0.SUPPLIERS(SID)
USING STOGROUP DSN8G!!0;
CREATE TABLE DSN8!!0.PRODUCTSUPPLIER
( PID VARCHAR(10) NOT NULL,
SID VARCHAR(10) NOT NULL,
PRIMARY KEY(PID,SID) )
IN DSN8D!!X.DSN8S!!Z
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.PRODUCTSUPPLIER_PID_SIDX
ON DSN8!!0.PRODUCTSUPPLIER(PID,SID)
USING STOGROUP DSN8G!!0;
//*
//* STEP 3: CREATE SAMPLE MAPPING TABLE FOR ONLINE REORG
//*
//*********************************************************************
//* THE FOLLOWING IS AN EXAMPLE DEFINITION OF THE MAPPING TABLE *
//* THAT IS REQUIRED TO RUN AN ONLINE REORGANIZATION WITH SHRLEVEL *
//* CHANGE. *
//* *
//* A MAPPING TABLE MUST BE CREATED IN A SEGMENTED TABLE SPACE. *
//* THAT TABLESPACE SHOULD CONTAIN ONLY MAPPING TABLES. IT MUST *
//* NOT BE THE TABLE SPACE THAT IS TO BE REORGANIZED. *
//* *
//* IF YOU WANT CONCURRENT EXECUTIONS OF REORG (ON SEVERAL TABLE *
//* SPACES AND/OR ON SEVERAL PARTITIONS OF A TABLE SPACE) THE *
//* EXECUTIONS SHOULD USE DIFFERENT MAPPING TABLES. *
//*********************************************************************
//PH01S03 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE TABLE DSN8!!0.MAP_TBL
(TYPE CHAR( 01 ) NOT NULL,
SOURCE_RID CHAR( 07 ) NOT NULL,
TARGET_XRID CHAR( 11 ) NOT NULL,
LRSN CHAR( 10 ) NOT NULL)
IN DSN8D!!P.DSN8S!!Q
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XMAP_TBL
ON DSN8!!0.MAP_TBL
(SOURCE_RID ASC,
TYPE,
TARGET_XRID,
LRSN)
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
//*
//* STEP 4: CREATE SAMPLE VIEWS
//*
//PH01S04 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE VIEW DSN8!!0.VDEPMG1
(DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT, LASTNAME, ADMRDEPT)
AS SELECT ALL
DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT, LASTNAME, ADMRDEPT
FROM DSN8!!0.DEPT LEFT OUTER JOIN DSN8!!0.EMP
ON MGRNO = EMPNO ;
CREATE VIEW DSN8!!0.VEMPDPT1
(DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT,
LASTNAME, WORKDEPT)
AS SELECT ALL
DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1), MIDINIT,
LASTNAME, WORKDEPT
FROM DSN8!!0.DEPT RIGHT OUTER JOIN DSN8!!0.EMP
ON WORKDEPT = DEPTNO ;
CREATE VIEW DSN8!!0.VASTRDE1
(DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN)
AS SELECT ALL
D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
D1.LASTNAME, '1',
D2.DEPTNO,D2.DEPTNAME,D2.MGRNO,D2.FIRSTNME,D2.MIDINIT,
D2.LASTNAME
FROM DSN8!!0.VDEPMG1 D1, DSN8!!0.VDEPMG1 D2
WHERE D1.DEPTNO = D2.ADMRDEPT ;
CREATE VIEW DSN8!!0.VASTRDE2
(DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN)
AS SELECT ALL
D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
D1.LASTNAME,'2',
D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT,
E2.LASTNAME
FROM DSN8!!0.VDEPMG1 D1, DSN8!!0.EMP E2
WHERE D1.DEPTNO = E2.WORKDEPT;
CREATE VIEW DSN8!!0.VPROJRE1
(PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT,LASTNAME,MAJPROJ)
AS SELECT ALL
PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT,LASTNAME,MAJPROJ
FROM DSN8!!0.PROJ, DSN8!!0.EMP
WHERE RESPEMP = EMPNO ;
CREATE VIEW DSN8!!0.VPSTRDE1
(PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN,
PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN)
AS SELECT ALL
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
P1.LASTNAME,
P2.PROJNO,P2.PROJNAME,P2.RESPEMP,P2.FIRSTNME,P2.MIDINIT,
P2.LASTNAME
FROM DSN8!!0.VPROJRE1 P1,
DSN8!!0.VPROJRE1 P2
WHERE P1.PROJNO = P2.MAJPROJ ;
CREATE VIEW DSN8!!0.VPSTRDE2
(PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN,
PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN)
AS SELECT ALL
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
P1.LASTNAME,
P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
P1.LASTNAME
FROM DSN8!!0.VPROJRE1 P1
WHERE NOT EXISTS
(SELECT * FROM DSN8!!0.VPROJRE1 P2
WHERE P1.PROJNO = P2.MAJPROJ) ;
CREATE VIEW DSN8!!0.VFORPLA
(PROJNO,PROJNAME,RESPEMP,PROJDEP,FRSTINIT,MIDINIT,LASTNAME)
AS SELECT ALL
F1.PROJNO,PROJNAME,RESPEMP,PROJDEP, SUBSTR(FIRSTNME, 1, 1),
MIDINIT, LASTNAME
FROM DSN8!!0.VPROJRE1 F1 LEFT OUTER JOIN DSN8!!0.EMPPROJACT F2
ON F1.PROJNO = F2.PROJNO;
CREATE VIEW DSN8!!0.VSTAFAC1
(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
EMPTIME,STDATE,ENDATE, TYPE)
AS SELECT ALL
PA.PROJNO, PA.ACTNO, AC.ACTDESC,' ', ' ', ' ', ' ',
PA.ACSTAFF, PA.ACSTDATE,
PA.ACENDATE,'1'
FROM DSN8!!0.PROJACT PA, DSN8!!0.ACT AC
WHERE PA.ACTNO = AC.ACTNO ;
CREATE VIEW DSN8!!0.VSTAFAC2
(PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
EMPTIME,STDATE, ENDATE, TYPE)
AS SELECT ALL
EP.PROJNO, EP.ACTNO, AC.ACTDESC, EP.EMPNO,EM.FIRSTNME,
EM.MIDINIT, EM.LASTNAME, EP.EMPTIME, EP.EMSTDATE,
EP.EMENDATE,'2'
FROM DSN8!!0.EMPPROJACT EP, DSN8!!0.ACT AC, DSN8!!0.EMP EM
WHERE EP.ACTNO = AC.ACTNO AND EP.EMPNO = EM.EMPNO ;
CREATE VIEW DSN8!!0.VPHONE
(LASTNAME,
FIRSTNAME,
MIDDLEINITIAL,
PHONENUMBER,
EMPLOYEENUMBER,
DEPTNUMBER,
DEPTNAME)
AS SELECT ALL LASTNAME,
FIRSTNME,
MIDINIT ,
VALUE(PHONENO,' '),
EMPNO,
DEPTNO,
DEPTNAME
FROM DSN8!!0.EMP, DSN8!!0.DEPT
WHERE WORKDEPT = DEPTNO;
CREATE VIEW DSN8!!0.VEMPLP
(EMPLOYEENUMBER,
PHONENUMBER)
AS SELECT ALL EMPNO ,
PHONENO
FROM DSN8!!0.EMP ;
//*
//* STEP 5: DROP ALIASES
//*
//PH01S05 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) PARM('RC0') -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
DROP ALIAS ACT ;
DROP ALIAS TCONA ;
DROP ALIAS DEPT ;
DROP ALIAS TDSPTXT ;
DROP ALIAS EMP ;
DROP ALIAS EMPPROJACT ;
DROP ALIAS TOPTVAL ;
DROP ALIAS PROJACT ;
DROP ALIAS PROJ ;
DROP ALIAS VPHONE ;
DROP ALIAS VACT ;
DROP ALIAS VCONA ;
DROP ALIAS VDEPT ;
DROP ALIAS VHDEPT ;
DROP ALIAS VDSPTXT ;
DROP ALIAS VEMP ;
DROP ALIAS VEMPPROJACT ;
DROP ALIAS VOPTVAL ;
DROP ALIAS VPROJACT ;
DROP ALIAS VPROJ ;
DROP ALIAS VEMPLP ;
DROP ALIAS VDEPMG1 ;
DROP ALIAS VEMPDPT1 ;
DROP ALIAS VASTRDE1 ;
DROP ALIAS VASTRDE2 ;
DROP ALIAS VPROJRE1 ;
DROP ALIAS VPSTRDE1 ;
DROP ALIAS VPSTRDE2 ;
DROP ALIAS VFORPLA ;
DROP ALIAS VSTAFAC1 ;
DROP ALIAS VSTAFAC2 ;
DROP ALIAS PRODUCT ;
DROP ALIAS INVENTORY ;
DROP ALIAS CUSTOMER ;
DROP ALIAS PURCHASEORDER;
DROP ALIAS CATALOG ;
DROP ALIAS SUPPLIERS ;
DROP ALIAS PRODUCTSUPPLIER;
//*
//* STEP 6: GRANT AUTHORITY, CREATE ALIASES
//*
//PH01S06 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CREATE ALIAS ACT FOR DSN8!!0.ACT ;
CREATE ALIAS TCONA FOR DSN8!!0.TCONA ;
CREATE ALIAS DEPT FOR DSN8!!0.DEPT ;
CREATE ALIAS TDSPTXT FOR DSN8!!0.TDSPTXT ;
CREATE ALIAS EMP FOR DSN8!!0.EMP ;
CREATE ALIAS EMPPROJACT FOR DSN8!!0.EMPPROJACT ;
CREATE ALIAS TOPTVAL FOR DSN8!!0.TOPTVAL ;
CREATE ALIAS PROJACT FOR DSN8!!0.PROJACT ;
CREATE ALIAS PROJ FOR DSN8!!0.PROJ ;
CREATE ALIAS VPHONE FOR DSN8!!0.VPHONE ;
CREATE ALIAS VACT FOR DSN8!!0.VACT ;
CREATE ALIAS VCONA FOR DSN8!!0.VCONA ;
CREATE ALIAS VDEPT FOR DSN8!!0.VDEPT ;
CREATE ALIAS VHDEPT FOR DSN8!!0.VHDEPT ;
CREATE ALIAS VDSPTXT FOR DSN8!!0.VDSPTXT ;
CREATE ALIAS VEMP FOR DSN8!!0.VEMP ;
CREATE ALIAS VEMPPROJACT FOR DSN8!!0.VEMPPROJACT ;
CREATE ALIAS VOPTVAL FOR DSN8!!0.VOPTVAL ;
CREATE ALIAS VPROJACT FOR DSN8!!0.VPROJACT ;
CREATE ALIAS VPROJ FOR DSN8!!0.VPROJ ;
CREATE ALIAS VEMPLP FOR DSN8!!0.VEMPLP ;
CREATE ALIAS VDEPMG1 FOR DSN8!!0.VDEPMG1 ;
CREATE ALIAS VEMPDPT1 FOR DSN8!!0.VEMPDPT1 ;
CREATE ALIAS VASTRDE1 FOR DSN8!!0.VASTRDE1;
CREATE ALIAS VASTRDE2 FOR DSN8!!0.VASTRDE2;
CREATE ALIAS VPROJRE1 FOR DSN8!!0.VPROJRE1;
CREATE ALIAS VPSTRDE1 FOR DSN8!!0.VPSTRDE1;
CREATE ALIAS VPSTRDE2 FOR DSN8!!0.VPSTRDE2;
CREATE ALIAS VFORPLA FOR DSN8!!0.VFORPLA;
CREATE ALIAS VSTAFAC1 FOR DSN8!!0.VSTAFAC1;
CREATE ALIAS VSTAFAC2 FOR DSN8!!0.VSTAFAC2;
CREATE ALIAS PRODUCT FOR DSN8!!0.PRODUCT;
CREATE ALIAS INVENTORY FOR DSN8!!0.INVENTORY;
CREATE ALIAS CUSTOMER FOR DSN8!!0.CUSTOMER;
CREATE ALIAS PURCHASEORDER FOR DSN8!!0.PURCHASEORDER;
CREATE ALIAS CATALOG FOR DSN8!!0.CATALOG;
CREATE ALIAS SUPPLIERS FOR DSN8!!0.SUPPLIERS;
CREATE ALIAS PRODUCTSUPPLIER FOR DSN8!!0.PRODUCTSUPPLIER;
GRANT USE OF STOGROUP DSN8G!!0
TO PUBLIC;
GRANT DBADM ON DATABASE DSN8D!!A
TO PUBLIC;
GRANT DBADM ON DATABASE DSN8D!!P
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!D
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!E
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!M
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!N
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!O
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!P
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!A.DSN8S!!S
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!P.DSN8S!!A
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!P.DSN8S!!B
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!P.DSN8S!!C
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!P.DSN8S!!Q
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!T
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!U
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!V
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!W
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!X
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!Y
TO PUBLIC;
GRANT USE OF TABLESPACE DSN8D!!X.DSN8S!!Z
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.ACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.TCONA
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.DEPT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.TDSPTXT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.EMP
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.EMPPROJACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.TOPTVAL
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PARTS
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PROJACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PROJ
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.MAP_TBL
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPHONE
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VCONA
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VDEPT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VHDEPT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VDSPTXT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VEMP
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VEMPPROJACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VOPTVAL
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPROJACT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPROJ
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VEMPLP
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VDEPMG1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VEMPDPT1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VASTRDE1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VASTRDE2
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPROJRE1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPSTRDE1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VPSTRDE2
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VFORPLA
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VSTAFAC1
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.VSTAFAC2
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PRODUCT
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.INVENTORY
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.CUSTOMER
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PURCHASEORDER
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.CATALOG
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.SUPPLIERS
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE ON TABLE DSN8!!0.PRODUCTSUPPLIER
TO PUBLIC;
//*
//*
//*
//* STEP 7: ASSEMBLE AND LINKEDIT EDIT EXIT
//*
//PH01S07 EXEC ASMCL,MEM=DSN8EAE1,COND=(4,LT),
// PARM.LKED='LIST,XREF,NCAL,RENT,AMODE=31,RMODE=ANY'
//*
//* STEP 8: ASSEMBLE AND LINKEDIT EDIT EXIT
//*
//PH01S08 EXEC ASMCL,MEM=DSN8HUFF,COND=(4,LT),
// PARM.LKED='LIST,XREF,NCAL,RENT,AMODE=31,RMODE=ANY'
//*
//* STEP 9: ASSEMBLE AND LINKEDIT FIELD PROCEDURE
//*
//PH01S09 EXEC ASMCL,MEM=DSN8FPRC,COND=(4,LT),
// PARM.ASM='RENT,OBJECT,NODECK,SYSPARM(FLDPROC)',
// PARM.LKED='LIST,XREF,NCAL,RENT,AMODE=31,RMODE=ANY'
//*
//* STEP 10: PRECOMPILE, ASSEMBLE AND LINKEDIT THE
//* CALL ATTACH ASSEMBLER INTERFACE
//*
//PH01S10 EXEC DSNHASM,MEM=DSN8CA,
// COND=(4,LT),
// PARM.PC='HOST(ASM),STDSQL(NO)',
// PARM.ASM='RENT,OBJECT,NODECK',
// PARM.LKED='RENT,XREF,AMODE=31,RMODE=ANY'
//PC.DBRMLIB DD DISP=SHR,
// DSN=DSN!!0.DBRMLIB.DATA(DSN8CA)
//PC.SYSLIB DD DSN=DSN!!0.SDSNSAMP,
// DISP=SHR
//PC.SYSIN DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8CA)
//ASM.SYSLIB DD
// DD DSN=DSN!!0.SDSNSAMP,
// DISP=SHR
//LKED.SYSLMOD DD DISP=SHR,
// DSN=DSN!!0.RUNLIB.LOAD(DSN8CA)
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNALI)
//*
//* STEP 11: CREATE SAMPLE UTILITY LIST
//*
//PH01S11 EXEC PGM=IEBGENER,COND=(4,LT)
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD *
LISTDEF DSN8LDEF
INCLUDE TABLESPACES DATABASE DSN8D!!A
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!F
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!G
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!H
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!I
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!J
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!R
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!S
//SYSUT2 DD DSN=DSN!!0.DSN8.LISTDEF,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(TRK,1),
// DCB=(RECFM=FB,LRECL=80)
//*
//* STEP 12: LOAD DATA INTO SAMPLE PROGRAM TABLES
//*
//PH01S12 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSRECDT DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LDT)
//SYSRECOV DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LOV)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(SYSRECOV) CONTINUEIF(72:72)='X'
INTO TABLE DSN8!!0.TOPTVAL
(MAJSYS POSITION( 2) CHAR(1),
ACTION POSITION( 4) CHAR(1),
OBJFLD POSITION( 6) CHAR(2),
SRCHCRIT POSITION( 9) CHAR(2),
SCRTYPE POSITION( 12) CHAR(1),
HEADTXT POSITION( 80) CHAR(50),
SELTXT POSITION(159) CHAR(50),
INFOTXT POSITION(238) CHAR(71),
HELPTXT POSITION(317) CHAR(71),
PFKTXT POSITION(396) CHAR(71),
DSPINDEX POSITION(475) CHAR(2))
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECDT) CONTINUEIF(72:72)='X'
INTO TABLE DSN8!!0.TDSPTXT
(DSPINDEX POSITION( 2) CHAR(2),
LINENO POSITION( 6) CHAR(2),
DSPLINE POSITION(80) CHAR(71))
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 13: LOAD DATA INTO SAMPLE APPLICATION TABLES
//*
//PH01S13 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSRECAC DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LAC)
//SYSRECDP DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LDP)
//SYSRECEM DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LEM)
//SYSRECEP DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LEP)
//SYSRECPA DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LPA)
//SYSRECPJ DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LPJ)
//SYSRECXP DD DISP=SHR,
// DSN=DSN!!0.SDSNIVPD(DSN8LXPR)
//SYSERR DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSDISC DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSMAP DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(SYSRECDP)
INTO TABLE DSN8!!0.DEPT
(DEPTNO POSITION( 1) CHAR(3),
DEPTNAME POSITION( 5) VARCHAR,
MGRNO POSITION(37) CHAR(6) NULLIF(MGRNO=' '),
ADMRDEPT POSITION(44) CHAR(3),
LOCATION POSITION(48) CHAR(16))
ENFORCE NO
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECEM) CONTINUEIF(72:72)='X'
INTO TABLE DSN8!!0.EMP
(EMPNO POSITION( 1) CHAR(6),
FIRSTNME POSITION( 8) VARCHAR,
MIDINIT POSITION(21) CHAR(1),
LASTNAME POSITION(23) VARCHAR,
WORKDEPT POSITION(36) CHAR(3),
PHONENO POSITION(40) CHAR(4),
HIREDATE POSITION(45) DATE EXTERNAL,
JOB POSITION(56) CHAR(8),
EDLEVEL POSITION(65) INTEGER EXTERNAL(2),
SEX POSITION(68) CHAR(1),
BIRTHDATE POSITION(80) DATE EXTERNAL,
SALARY POSITION(91) INTEGER EXTERNAL(5),
BONUS POSITION(97) INTEGER EXTERNAL(5),
COMM POSITION(103) INTEGER EXTERNAL(5))
ENFORCE CONSTRAINTS MAPDDN SYSMAP
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECPJ) CONTINUEIF(72:72)='X'
INTO TABLE DSN8!!0.PROJ
(PROJNO POSITION( 1) CHAR(6),
PROJNAME POSITION( 8) VARCHAR,
DEPTNO POSITION(33) CHAR(3),
RESPEMP POSITION(37) CHAR(6),
PRSTAFF POSITION(44) DECIMAL EXTERNAL(5),
PRSTDATE POSITION(50) DATE EXTERNAL,
PRENDATE POSITION(61) DATE EXTERNAL,
MAJPROJ POSITION(80) CHAR(6) NULLIF(MAJPROJ=' '))
ENFORCE NO
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECAC)
INTO TABLE DSN8!!0.ACT
(ACTNO POSITION( 1) INTEGER EXTERNAL(3),
ACTKWD POSITION( 5) CHAR(6),
ACTDESC POSITION(13) VARCHAR)
ENFORCE NO
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECPA)
INTO TABLE DSN8!!0.PROJACT
(PROJNO POSITION( 1) CHAR(6),
ACTNO POSITION( 8) INTEGER EXTERNAL(3),
ACSTAFF POSITION(12) DECIMAL EXTERNAL(5),
ACSTDATE POSITION(18) DATE EXTERNAL,
ACENDATE POSITION(29) DATE EXTERNAL)
ENFORCE NO
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECEP)
INTO TABLE DSN8!!0.EMPPROJACT
(EMPNO POSITION( 1) CHAR(6),
PROJNO POSITION( 8) CHAR(6),
ACTNO POSITION(16) INTEGER EXTERNAL(3),
EMPTIME POSITION(20) DECIMAL EXTERNAL(5),
EMSTDATE POSITION(26) DATE EXTERNAL,
EMENDATE POSITION(37) DATE EXTERNAL)
ENFORCE NO
SORTDEVT SYSDA SORTNUM 4
LOAD DATA INDDN(SYSRECXP)
INTO TABLE DSN8!!0.PRODUCT
(PID POSITION(1) CHAR(10),
,NAME POSITION(11) VARCHAR NULLIF(140)='?'
,PRICE POSITION(141) DECIMAL NULLIF(157)='?'
,PROMOPRICE POSITION(158) DECIMAL NULLIF(174)='?'
,PROMOSTART POSITION(175) DATE EXTERNAL(10) NULLIF(185)='?'
,PROMOEND POSITION(186) DATE EXTERNAL(10) NULLIF(196)='?'
,DESCRIPTION POSITION(197) XML)
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 14: CHECK DATA FOR REFERENTIAL INTEGRITY
//*
//PH01S14 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSERR DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
CHECK DATA TABLESPACE DSN8D!!A.DSN8S!!D
TABLESPACE DSN8D!!A.DSN8S!!E
TABLESPACE DSN8D!!A.DSN8S!!M
TABLESPACE DSN8D!!A.DSN8S!!O
TABLESPACE DSN8D!!A.DSN8S!!P
SCOPE ALL
DELETE YES
FOR EXCEPTION IN DSN8!!0.DEPT USE DSN8!!0.EDEPT
IN DSN8!!0.EMP USE DSN8!!0.EEMP
IN DSN8!!0.PROJ USE DSN8!!0.EPROJ
IN DSN8!!0.ACT USE DSN8!!0.EACT
IN DSN8!!0.PROJACT USE DSN8!!0.EPROJACT
IN DSN8!!0.EMPPROJACT USE DSN8!!0.EEPA
EXCEPTIONS 50
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 15: ESTABLISH A QUIESCE POINT
//* NOTE: CONDITION CODE 4 INDICATES AN IMAGE COPY
//* CANNOT BE TAKEN
//*
//PH01S15 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSLISTD DD DISP=SHR,DSN=DSN!!0.DSN8.LISTDEF
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
QUIESCE LIST DSN8LDEF
//*
//* STEP 16: TAKE IMAGE COPY OF SAMPLE TABLES
//*
//PH01S16 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSLISTD DD DISP=SHR,DSN=DSN!!0.DSN8.LISTDEF
// DD *
INCLUDE TABLESPACE DSN8D!!P.DSN8S!!A
INCLUDE TABLESPACE DSN8D!!P.DSN8S!!B
INCLUDE TABLESPACE DSN8D!!P.DSN8S!!C
//DSNTRACE DD SYSOUT=*
//SYSIN DD *
TEMPLATE DSN8TPLT
DSN(DSN!!0.SYSCOPY.&DB..&TS.)
DISP (NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 5 NBRSECND 10
COPY LIST DSN8LDEF
COPYDDN(DSN8TPLT)
//*
//* STEP 17: ESTABLISH A QUIESCE POINT USING ONLY IMAGE COPIES
//*
//PH01S17 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSLISTD DD DISP=SHR,DSN=DSN!!0.DSN8.LISTDEF
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
QUIESCE LIST DSN8LDEF
//*
//* STEP 18: REORGANIZE TABLESPACES, PRODUCE STATISTICS
//*
//PH01S18 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSLISTD DD DISP=SHR,DSN=DSN!!0.DSN8.LISTDEF
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND)
//SYSIN DD *
REORG TABLESPACE DSN8D!!A.DSN8S!!D
SORTDEVT SYSDA SORTNUM 4
RUNSTATS TABLESPACE LIST DSN8LDEF
INDEX(ALL)
//*
//* STEP 19: RUN ONLINE REORG OF TBLSPACE USING SHRLEVEL CHANGE
//*
//PH01S19 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND)
//SYSIN DD *
TEMPLATE DSN8TPCY
DSN(DSN!!0.&DB..&TS..REORGCPY)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 1 NBRSECND 1
REORG TABLESPACE DSN8D!!P.DSN8S!!A
COPYDDN(DSN8TPCY)
SHRLEVEL CHANGE
DEADLINE NONE
MAPPINGTABLE DSN8!!0.MAP_TBL
MAXRO 240 LONGLOG DRAIN DELAY 900
SORTDEVT SYSDA SORTNUM 4
RUNSTATS TABLESPACE DSN8D!!P.DSN8S!!A
INDEX(ALL)
REORG TABLESPACE DSN8D!!P.DSN8S!!B
COPYDDN(DSN8TPCY)
SHRLEVEL CHANGE
DEADLINE NONE
MAPPINGTABLE DSN8!!0.MAP_TBL
MAXRO 240 LONGLOG DRAIN DELAY 900
SORTDEVT SYSDA SORTNUM 4
RUNSTATS TABLESPACE DSN8D!!P.DSN8S!!B
INDEX(ALL)
REORG TABLESPACE DSN8D!!P.DSN8S!!C
COPYDDN(DSN8TPCY)
SHRLEVEL CHANGE
DEADLINE NONE
MAPPINGTABLE DSN8!!0.MAP_TBL
MAXRO 240 LONGLOG DRAIN DELAY 900
SORTDEVT SYSDA SORTNUM 4
RUNSTATS TABLESPACE DSN8D!!P.DSN8S!!C
INDEX(ALL)
//*
//* STEP 20: LOAD DATA INTO PART 3 OF DSN8D!!A.DSN8S!!E
//*
//PH01S20 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSRECE3 DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8LE3)
//SYSERR DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSDISC DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSMAP DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(SYSRECE3) CONTINUEIF(72:72)='X'
RESUME YES
INTO TABLE DSN8!!0.EMP
PART 3
REPLACE
(EMPNO POSITION( 1) CHAR(6),
FIRSTNME POSITION( 8) VARCHAR,
MIDINIT POSITION(21) CHAR(1),
LASTNAME POSITION(23) VARCHAR,
WORKDEPT POSITION(36) CHAR(3),
PHONENO POSITION(40) CHAR(4),
HIREDATE POSITION(45) DATE EXTERNAL,
JOB POSITION(56) CHAR(8),
EDLEVEL POSITION(65) INTEGER EXTERNAL(2),
SEX POSITION(68) CHAR(1),
BIRTHDATE POSITION(80) DATE EXTERNAL,
SALARY POSITION(91) INTEGER EXTERNAL(5),
BONUS POSITION(97) INTEGER EXTERNAL(5),
COMM POSITION(103) INTEGER EXTERNAL(5))
ENFORCE CONSTRAINTS MAPDDN SYSMAP
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 21: SET CURRENT RULES THEN ALTER TABLE
//*
//PH01S21 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
SET CURRENT RULES = 'DB2';
COMMIT;
ALTER TABLE EMP ADD CONSTRAINT PERSON
CHECK (SEX = 'M' OR SEX = 'F');
COMMIT;
//*
//* STEP 22: CHECK DATA IN PART 3 OF DSN8D!!A.DSN8S!!E
//* FOR REFERENTIAL INTEGRITY
//*
//PH01S22 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSERR DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSDISC DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
CHECK DATA TABLESPACE DSN8D!!A.DSN8S!!D
TABLESPACE DSN8D!!A.DSN8S!!E
TABLESPACE DSN8D!!A.DSN8S!!M
TABLESPACE DSN8D!!A.DSN8S!!N
TABLESPACE DSN8D!!A.DSN8S!!O
TABLESPACE DSN8D!!A.DSN8S!!P
SCOPE ALL
DELETE YES
FOR EXCEPTION IN DSN8!!0.DEPT USE DSN8!!0.EDEPT
IN DSN8!!0.EMP USE DSN8!!0.EEMP
IN DSN8!!0.PROJ USE DSN8!!0.EPROJ
IN DSN8!!0.ACT USE DSN8!!0.EACT
IN DSN8!!0.PROJACT USE DSN8!!0.EPROJACT
IN DSN8!!0.EMPPROJACT USE DSN8!!0.EEPA
EXCEPTIONS 50
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 23: ATTEMPT INSERT INTO THE EMP TABLE
//*
//PH01S23 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) PARM('RC0') -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
INSERT INTO EMP
VALUES ('000011', 'CHRISTINE', 'I', 'HAAS', 'A00', 'A1A1',
'1965-01-01', 'PRES', 18, 'F', '1933-08-14',
52750, 1000, 4220);
COMMIT;
INSERT INTO EMP
VALUES ('200011', 'DIANE', 'J', 'HEMMINGER', 'A00', '3978',
'1965-01-01', 'SALESREP', 18, 'U', '1933-08-14',
46500, 1000, 4220);
COMMIT;
//*
//* STEP 24: ESTABLISH A QUIESCE POINT FOR PART 3
//* OF DSN8D!!A.DSN8S!!E
//* NOTE: CONDITION CODE 4 INDICATES AN IMAGE COPY
//* CANNOT BE TAKEN
//*
//PH01S24 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
QUIESCE TABLESPACE DSN8D!!A.DSN8S!!E
PART 3
//*
//* STEP 25: TAKE IMAGE COPY OF PART 3 OF DSN8D!!A.DSN8S!!E
//*
//PH01S25 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//DSNTRACE DD SYSOUT=*
//SYSCOPY DD DSN=DSN!!0.DSN8D!!A.DSN8S!!E.PART3,
// DISP=(NEW,CATLG),
// UNIT=SYSDA,
// SPACE=(4000,(20,20))
//SYSIN DD *
COPY TABLESPACE DSN8D!!A.DSN8S!!E
DSNUM 3 COPYDDN SYSCOPY
//*
//* STEP 26: ESTABLISH A QUIESCE POINT FOR PART 3
//* OF DSN8D!!A.DSN8S!!E USING ONLY IMAGE COPY
//* NOTE: CONDITION CODE 4 INDICATES AN IMAGE COPY
//* CANNOT BE TAKEN
//*
//PH01S26 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SYSIN DD *
QUIESCE TABLESPACE DSN8D!!A.DSN8S!!E
PART 3
//*
//* STEP 27: PRODUCE STATISTICS FOR PART 3 OF
//* OF DSN8D!!A.DSN8S!!E
//*
//PH01S27 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND)
//SYSIN DD *
RUNSTATS TABLESPACE DSN8D!!A.DSN8S!!E
PART 3
INDEX(ALL)
//*
//* STEP 28: UNLOAD PARTITIONS 1,3, AND 4
//* OF DSN8D!!A.DSN8S!!E
//*
//PH01S28 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
LISTDEF DSN8LDUL
INCLUDE TABLESPACE DSN8D!!A.DSN8S!!E PARTLEVEL
EXCLUDE TABLESPACE DSN8D!!A.DSN8S!!E PARTLEVEL(2)
TEMPLATE DSN8TPPU
DSN(DSN!!0.&DB..&TS..SYSPUNCH)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 1 NBRSECND 1
TEMPLATE DSN8TPSY
DSN(DSN!!0.&DB..&TS..P&PART.)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 5 NBRSECND 10
UNLOAD LIST DSN8LDUL
PUNCHDDN(DSN8TPPU)
UNLDDN(DSN8TPSY)
EBCDIC
NOPAD
//*
//* STEP 29: REDUCE THE PARTITIONING KEY ON PARTITION 4 OF
//* TABLESPACE DSN8S!!E
//*
//PH01S29 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
ALTER TABLE DSN8!!0.EMP ALTER PART 4 VALUES('499999');
//*
//* STEP 30: REORGANIZE TABLESPACE DSN8D!!A.DSN8S!!E
//*
//PH01S30 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SYSLISTD DD DISP=SHR,DSN=DSN!!0.DSN8.LISTDEF
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND)
//SYSIN DD *
TEMPLATE DSN8TPPU
DSN(DSN!!0.&DB..&TS..REORGPUN)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 1 NBRSECND 1
TEMPLATE DSN8TPCY
DSN(DSN!!0.&DB..&TS..REORGCPY)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 1 NBRSECND 1
TEMPLATE DSN8TPDS
DSN(DSN!!0.&DB..&TS..REORGDSC)
DISP(NEW,CATLG,DELETE)
UNIT SYSDA
PCTPRIME 100 MAXPRIME 1 NBRSECND 1
REORG TABLESPACE DSN8D!!A.DSN8S!!E
SHRLEVEL REFERENCE
PART 1:4
PUNCHDDN(DSN8TPPU)
COPYDDN(DSN8TPCY)
DISCARDDN(DSN8TPDS)
SORTDEVT SYSDA SORTNUM 4
//*
//* STEP 31: ADD A FIFTH PARTITION TO
//* TABLESPACE DSN8S!!E
//*
//PH01S31 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
-STOP DB(DSN8D!!A) SPACENAM(*)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
-START DB(DSN8D!!A) SPACENAM(*)
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
ALTER TABLE DSN8!!0.EMP ADD PARTITION ENDING AT('999999');
//*
//* STEP 32: EXTEND THE LENGTH OF A FIXED CHAR
//* COLUMN IN THE PARTS TABLE. ALSO
//* CONVERT A SMALL INTEGER FIELD TO
//* A DECIMAL TYPE FIELD OF EEMP TABLE.
//*
//PH01S32 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
ALTER TABLE DSN8!!0.PARTS ALTER COLUMN ITEMNUM
SET DATA TYPE VARCHAR(10);
ALTER TABLE DSN8!!0.EEMP ALTER COLUMN EDLEVEL
SET DATA TYPE DECIMAL(5, 0);
//*
//* STEP 33: REORGANIZE TABLESPACE DSN8D!!A.DSN8S!!R AND
//* DSN8D!!A.DSN8S!!S TO RESET REORG-PENDING STATUS
//*
//PH01S33 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND)
//SYSIN DD *
REORG TABLESPACE DSN8D!!A.DSN8S!!F
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!G
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!H
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!I
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!J
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!R
SORTDEVT SYSDA SORTNUM 4
REORG TABLESPACE DSN8D!!A.DSN8S!!S
SORTDEVT SYSDA SORTNUM 4
//* 