DSNTEJ7
CREATES THE FOLLOWING OBJECTS FOR THE Db2 LOB SAMPLE - DSN8!!0.
//*********************************************************************
//* NAME = DSNTEJ7
//*
//* DESCRIPTIVE NAME = DB2 SAMPLE APPLICATION
//* PHASE 7 (LOCAL SITE ONLY)
//*
//* Licensed Materials - Property of IBM
//* 5698-DB2
//* COPYRIGHT IBM CORP 1982, 2022
//*
//* STATUS = Version 13
//*
//* FUNCTION = CREATES THE FOLLOWING OBJECTS FOR THE DB2 LOB SAMPLE
//* - DSN8!!0.EMP_PHOTO_RESUME: DB2 SAMPLE LOB TABLE
//* - EMPNO : EMPLOYEE SERIAL NO. (CHAR(6))
//* - EMP_ROWID : ROWID: REQUIRED FOR LOB TABLES
//* - PSEG_PHOTO: EMP. PHOTO IN PSEG FORMAT (BLOB 500K)
//* - BMP_PHOTO : EMP. PHOTO IN BMP FORMAT (BLOB 100K)
//* - RESUME : EMP. RESUME (CLOB 5K)
//* - RESUME : EMP. RESUME (CLOB 5K)
//*
//* - DSN8S!!B: BASE TABLESPACE FOR EMP_PHOTO_RESUME TABLE
//* - DSN8S!!L: AUXILIARY TABLESPACE FOR PSEG_PHOTO COLUMN
//* - DSN8S!!M: AUXILIARY TABLESPACE FOR BMP_PHOTO COLUMN
//* - DSN8S!!N: AUXILIARY TABLESPACE FOR RESUME COLUMN
//*
//* - DSN8D!!L: DATABASE FOR SAMPLE LOB TABLESPACES AND
//* TABLES
//*
//* THIS JCL ALSO INVOKES THE DB2 LOAD UTILITY TO POPULATE
//* THE TABLE.
//*
//* 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
//* 02/15/2016 Long data set names PI42601
//* 08/21/2018 Tolerate creation of deprecated TSs s81673 / t81374
//* 12/01/2021 Remove tolerance for deprecated TSs e6837 / s15746
//*
//*********************************************************************
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
//*
//* STEP 1: DROP SAMPLE LOB OBJECTS
//*
//PH07S01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSUT2 DD DSN=DSN!!0.DSNTEJ7.LOBLOAD,
// DISP=(MOD,DELETE),
// UNIT=SYSDA,SPACE=(TRK,0)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) -
PLAN(DSNTIA!!) -
LIB('DSN!!0.RUNLIB.LOAD') -
PARM('RC0')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
DROP DATABASE DSN8D!!L;
//*
//* STEP 2: CREATE SAMPLE LOB OBJECTS
//*
//PH07S02 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 DATABASE DSN8D!!L
STOGROUP DSN8G!!0
BUFFERPOOL BP0
CCSID EBCDIC;
CREATE TABLESPACE DSN8S!!B
IN DSN8D!!L
USING STOGROUP DSN8G!!0
ERASE NO
LOCKSIZE PAGE
LOCKMAX SYSTEM
BUFFERPOOL BP0
CLOSE NO
CCSID EBCDIC
MAXPARTITIONS 254;
CREATE TABLE DSN8!!0.EMP_PHOTO_RESUME
( EMPNO CHAR( 06 ) NOT NULL,
EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
PSEG_PHOTO BLOB( 500K ),
BMP_PHOTO BLOB( 100K ),
RESUME CLOB( 5K ),
PRIMARY KEY ( EMPNO ) )
IN DSN8D!!L.DSN8S!!B
CCSID EBCDIC;
CREATE UNIQUE INDEX DSN8!!0.XEMP_PHOTO_RESUME
ON DSN8!!0.EMP_PHOTO_RESUME
( EMPNO ASC )
USING STOGROUP DSN8G!!0
ERASE NO
BUFFERPOOL BP0
CLOSE NO;
CREATE LOB TABLESPACE DSN8S!!L
IN DSN8D!!L
LOG NO;
CREATE AUX TABLE DSN8!!0.AUX_PSEG_PHOTO
IN DSN8D!!L.DSN8S!!L
STORES DSN8!!0.EMP_PHOTO_RESUME
COLUMN PSEG_PHOTO
PART 1;
CREATE UNIQUE INDEX DSN8!!0.XAUX_PSEG_PHOTO
ON DSN8!!0.AUX_PSEG_PHOTO;
CREATE LOB TABLESPACE DSN8S!!M
IN DSN8D!!L
LOG NO;
CREATE AUX TABLE DSN8!!0.AUX_BMP_PHOTO
IN DSN8D!!L.DSN8S!!M
STORES DSN8!!0.EMP_PHOTO_RESUME
COLUMN BMP_PHOTO
PART 1;
CREATE UNIQUE INDEX DSN8!!0.XAUX_BMP_PHOTO
ON DSN8!!0.AUX_BMP_PHOTO;
CREATE LOB TABLESPACE DSN8S!!N
IN DSN8D!!L
LOG NO;
CREATE AUX TABLE DSN8!!0.AUX_EMP_RESUME
IN DSN8D!!L.DSN8S!!N
STORES DSN8!!0.EMP_PHOTO_RESUME
COLUMN RESUME
PART 1;
CREATE UNIQUE INDEX DSN8!!0.XAUX_EMP_RESUME
ON DSN8!!0.AUX_EMP_RESUME;
COMMIT;
//*
//* STEP 3: CREATE ALIASES, GRANT AUTHORITY
//*
//PH07S03 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') -
PARM('RC0')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
DROP ALIAS EMP_PHOTO_RESUME;
COMMIT;
CREATE ALIAS EMP_PHOTO_RESUME
FOR DSN8!!0.EMP_PHOTO_RESUME;
GRANT USE
OF TABLESPACE DSN8D!!L.DSN8S!!B
TO PUBLIC;
GRANT USE
OF TABLESPACE DSN8D!!L.DSN8S!!L
TO PUBLIC;
GRANT USE
OF TABLESPACE DSN8D!!L.DSN8S!!M
TO PUBLIC;
GRANT USE
OF TABLESPACE DSN8D!!L.DSN8S!!N
TO PUBLIC;
GRANT DELETE, INSERT, SELECT, UPDATE
ON TABLE DSN8!!0.EMP_PHOTO_RESUME
TO PUBLIC;
//*
//* STEP 4: MAKE A COPY OF THE LOAD STATEMENT
//*
//PH07S04 EXEC PGM=IEBGENER,COND=(4,LT)
//SYSIN DD *
GENERATE MAXFLDS=9,MAXGPS=2,MAXLITS=150
G1 RECORD IDENT=(8,' ',1),
FIELD=(139,1,,1), 001-139
FIELD=(29,' ',,140) 140-168
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=SHR,
// DSN=DSN!!0.SDSNIVPD(DSN8LLOB)
//SYSUT2 DD DSN=DSN!!0.DSNTEJ7.LOBLOAD,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(672,1),
// DCB=(RECFM=FB,LRECL=168,BLKSIZE=672)
//*
//* STEP 5: TAILOR THE LOAD DATA FOR THE LOCAL SITE
//*
//PH07S05 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
EDIT 'DSN!!0.DSNTEJ7.LOBLOAD' +
TEXT OLD NONUM NORECOVER ASIS
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8P130) +
/DSN!!0.SDSNIVPD(DSN8P130) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8B130) +
/DSN!!0.SDSNIVPD(DSN8B130) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8R130) +
/DSN!!0.SDSNIVPD(DSN8R130) /
CHANGE * 2 /DSN??0.SDSNIVPD(DSN8P140) +
/DSN!!0.SDSNIVPD(DSN8P140) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8B140) +
/DSN!!0.SDSNIVPD(DSN8B140) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8R140) +
/DSN!!0.SDSNIVPD(DSN8R140) /
CHANGE * 2 /DSN??0.SDSNIVPD(DSN8P150) +
/DSN!!0.SDSNIVPD(DSN8P150) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8B150) +
/DSN!!0.SDSNIVPD(DSN8B150) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8R150) +
/DSN!!0.SDSNIVPD(DSN8R150) /
CHANGE * 2 /DSN??0.SDSNIVPD(DSN8P190) +
/DSN!!0.SDSNIVPD(DSN8P190) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8B190) +
/DSN!!0.SDSNIVPD(DSN8B190) /
CHANGE * 1 /DSN??0.SDSNIVPD(DSN8R190) +
/DSN!!0.SDSNIVPD(DSN8R190) /
END SAVE
//*
//* STEP 6: LOAD RESUME (CLOB) DATA INTO SAMPLE LOB TABLE
//*
//PH07S06 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK01 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK04 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//DSNTRACE DD SYSOUT=*
//LOBDATA DD DSN=DSN!!0.DSNTEJ7.LOBLOAD,DISP=SHR
//SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND)
//SYSIN DD *
LOAD DATA INDDN(LOBDATA)
REPLACE
INTO TABLE DSN8!!0.EMP_PHOTO_RESUME
(EMPNO POSITION(1:6) CHAR(6),
PSEG_PHOTO POSITION(7) CHAR(54) BLOBF,
BMP_PHOTO POSITION(61) CHAR(54) BLOBF,
RESUME POSITION(115) CHAR(54) CLOBF)
//*
//* STEP 7: PRODUCE STATISTICS FOR SAMPLE LOB TABLESPACES
//*
//PH07S07 EXEC DSNUPROC,PARM='DSN,DSNTEX',COND=(4,LT)
//SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR
//SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK01 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK02 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK03 DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND)
//SORTWK04 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!!L.DSN8S!!B
INDEX(ALL)
RUNSTATS TABLESPACE DSN8D!!L.DSN8S!!L
INDEX(ALL)
RUNSTATS TABLESPACE DSN8D!!L.DSN8S!!M
INDEX(ALL)
RUNSTATS TABLESPACE DSN8D!!L.DSN8S!!N
INDEX(ALL)
/*