DSNTEJ66
This JCL does the following.
//*********************************************************************
//* Name = DSNTEJ66
//*
//* Descriptive Name = DB2 Sample Application - Native SQL Procedure
//* Phase 6
//*
//*
//* Licensed Materials - Property of IBM
//* 5650-DB2
//* (C) COPYRIGHT 2006, 2016 IBM Corp. All Rights Reserved.
//*
//* STATUS = Version 12
//*
//* Function = This JCL does the following:
//* - Creates a sample native SQL procedure called
//* DSN8.DSN8ES3 that generates and returns (by result
//* set) a CREATE PROCEDURE statement for a given stored
//* procedure.
//* - Prepares and executes a sample caller of DSN8ES3
//* called DSN8ED9.
//* - Shows how to use ALTER PROCEDURE ... ADD VERSION to
//* create a version V2 of DSN8ES3 that does the same
//* thing as the original version but also adds a
//* terminating semicolon at the end of the generated
//* CREATE PROCEDURE statement
//* - Shows how to ALTER ACTIVATE version V2 to make it
//* the active version of DSN8ES3
//* - Shows how to DEPLOY DSN8ES3 at a remote site
//*
//* Restrictions =
//* As part of the setup to DEPLOY DSN8ES3, the DSNTEP2 application
//* needs to be able to connect to the remote site.
//*
//* Notice =
//*
//* Pseudocode =
//* PH066S01 Step Drop objects created by prior runs of this job
//* PH066S02 Step Create the global temporary table for
//* DSN8.DSN8ES3
//* PH066S03 Step Prepare DSN8ES3 as a native SQL procedure
//* -> Also generates a package called
//* DSN8.DSN8ES3
//* PH066S04 Step Prepare DSN8ED9, sample caller for the DSN8ES3
//* SQL proc
//* PH066S05 Step Bind the plan for DSN8ED9
//* PH066S06 Step Execute DSN8ED9 to request a CREATE PROC
//* statement for the stored procedure
//* SYSPROC.DSNUTILS
//* PH066S07 Step Create a work copy of the DSN8ES3 source code
//* PH066S08 Step Use TSO edit to modify the work copy into an
//* ALTER PROCEDURE that will make a trivial
//* change to DSN8ES3 as VERSION V2
//* -> The generated CREATE PROC statement will be
//* terminated by a semicolon
//* PH066S09 Step Save the work copy as DSN8ES3 in
//* DSN!!0.NEW.SDSNSAMP
//* PH066S10 Step Process the ALTER PROCEDURE DSN8ES3 to ADD
//* VERSION V2
//* -> Also generates a package called
//* DSN8.DSN8ES3 (VERSION V2)
//* PH066S11 Step Activate V2 as the current version of DSN8ES3
//* PH066S12 Step Execute DSN8ED9 to request a CREATE PROC
//* statement for SYSPROC.DSNUTILU
//* -> When using DSN8ES3 V2, it's terminated by a
//* semicolon
//* PH066S13 Step Setup to DEPLOY DSN8ES3: Create a global
//* temporary table on the remote server
//* -> To rerun this step, uncomment the DROP
//* and COMMIT statements
//* PH066S14 Step DEPLOY DSN8ES3 on the remote server
//* PH066S15 Step Bind the plan for DSN8ED9 on the remote server
//* PH066S16 Step Execute DSN8ED9 to request a CREATE PROC
//* statement for SYSPROC.DSNUTILS at the remote
//* site
//*
//* Change Activity =
//* 10/16/2013 Don't use prelinker by default PI13612 DM1812
//* 08/18/2014 Single-phase migration s21938_inst1 s21938
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
// DD DSN=CEE.V!R!M!.SCEERUN,DISP=SHR
//*
//* Step 1: Drop objects created by prior runs of this job
//*
//PH066S01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//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 PROCEDURE DSN8.DSN8ES3;
COMMIT;
DROP TABLE DSN8.DSN8ES3_RS_TBL;
COMMIT;
//WORKCOPY DD DSN=DSN!!0.DSN8.DSN8ES3.WORKCOPY,
// DISP=(MOD,DELETE),
// UNIT=SYSDA,SPACE=(TRK,0)
//*
//* Step 2: Create the global temporary table for DSN8.DSN8ES3
//*
//PH066S02 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 GLOBAL TEMPORARY TABLE
DSN8.DSN8ES3_RS_TBL
( RS_SEQUENCE INTEGER NOT NULL,
RS_LINE CHAR(80) NOT NULL )
CCSID EBCDIC
//*
//* Step 3: Prepare DSN8ES3 as a native SQL procedure
//* -> Also generates a package called DSN8.DSN8ES3
//*
//PH066S03 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) +
LIB('DSN!!0.RUNLIB.LOAD') PARMS('/SQLTERM(%)')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM'
%
// DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8ES3)
// DD *
%
//*
//* Step 4: Prepare DSN8ED9, sample caller for the DSN8ES3 SQL proc
//*
//PH066S04 EXEC DSNHC,MEM=DSN8ED9,COND=(4,LT),
// PARM.PC=('HOST(C),CCSID(1047),MARGINS(1,72),STDSQL(NO)',
// SOURCE,XREF),
// PARM.C='SOURCE LIST MAR(1,72) LO RENT OPTFILE(DD:CCOPTS)',
// PARM.LKED='AMODE=31,RMODE=ANY,MAP,NORENT,UPCASE'
//PC.DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA(DSN8ED9),
// DISP=SHR
//PC.SYSLIB DD DSN=DSN!!0.SRCLIB.DATA,
// DISP=SHR
//PC.SYSIN DD DSN=DSN!!0.SDSNSAMP(DSN8ED9),
// DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8ED9),
// DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNELI)
INCLUDE SYSLIB(DSNTIAR)
//*
//* Step 5: Bind the plan for DSN8ED9
//*
//PH066S05 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA,
// DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT BIND, EXECUTE ON PLAN DSN8ED9
TO PUBLIC;
//SYSTSIN DD *
DSN SYSTEM(DSN)
BIND PACKAGE(DSN8ED!!) MEMBER(DSN8ED9) APPLCOMPAT(V!!R1) +
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
BIND PLAN(DSN8ED9) +
PKLIST(DSN8ED!!.DSN8ED9) +
ACTION(REPLACE) RETAIN +
ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) +
LIB('DSN!!0.RUNLIB.LOAD')
//*
//* Step 6: Execute DSN8ED9 to request a CREATE PROC statement
//* for the stored procedure named SYSPROC.DSNUTILS
//*
//PH066S06 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSN8ED9) PLAN(DSN8ED9) +
LIB('DSN!!0.RUNLIB.LOAD') +
PARMS('/SYSPROC DSNUTILS')
END
//*
//* Step 7: Create a work copy of the DSN8ES3 source code
//*
//PH066S07 EXEC PGM=IEBGENER,COND=(4,LT)
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=SHR,
// DSN=DSN!!0.SDSNSAMP(DSN8ES3)
//SYSUT2 DD DSN=DSN!!0.DSN8.DSN8ES3.WORKCOPY,
// DISP=(,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(TRK,1),
// DCB=(RECFM=FB,LRECL=80)
//*
//* Step 8: Use TSO edit to modify the work copy into an ALTER PROCE-
//* DURE that will make a trivial change to DSN8ES3 VERSION V2
//* -> The generated CREATE PROC statement will now be
//* terminated by a semicolon
//*
//PH066S08 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
EDIT 'DSN!!0.DSN8.DSN8ES3.WORKCOPY' +
DATA OLD NONUM NORECOVER ASIS
FIND /CREATE PROCEDURE DSN8.DSN8ES3/
CHANGE * 1 /CREATE PROCEDURE/ALTER PROCEDURE /
INSERT ADD VERSION V2
FIND /PARAMETER CCSID EBCDIC/
DELETE * 1
FIND /U100: -- Finish up/
CHANGE * 1 /Finish up /Add terminating semicolon/
INSERT SET LINE = ';';
INSERT SET RETURN_POINT = 'DONE';
INSERT GOTO INSERTLINE;
LIST 1 9999
END SAVE
//*
//* Step 9: Save in DSN!!0.NEW.SDSNSAMP
//*
//PH066S09 EXEC PGM=IEBGENER,COND=(4,LT)
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DISP=(OLD,DELETE),
// DSN=DSN!!0.DSN8.DSN8ES3.WORKCOPY
//SYSUT2 DD DISP=SHR,
// DSN=DSN!!0.NEW.SDSNSAMP(DSN8ES3)
//*
//* Step 10: Process the ALTER PROCEDURE DSN8ES3 to ADD VERSION V2
//* -> Also generates a package called DSN8.DSN8ES3 (V2)
//*
//PH066S10 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP!!) +
LIB('DSN!!0.RUNLIB.LOAD') PARMS('/SQLTERM(%)')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM'
%
// DD DISP=SHR,
// DSN=DSN!!0.NEW.SDSNSAMP(DSN8ES3)
// DD *
%
//*
//* Step 11: Activate V2 as the current version of DSN8ES3
//*
//PH066S11 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) +
LIB('DSN!!0.RUNLIB.LOAD')
END
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
ALTER PROCEDURE DSN8.DSN8ES3 ACTIVATE VERSION V2;
//*
//* Step 12: Execute DSN8ED9 to request a CREATE PROC statement
//* for SYSPROC.DSNUTILU
//* -> When using DSN8ES3 V2, it's terminated by a semicolon
//*
//PH066S12 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
REBIND PACKAGE(DSN8ED!!.DSN8ED9) APPLCOMPAT(V!!R1) +
PLANMGMT(OFF)
RUN PROGRAM(DSN8ED9) PLAN(DSN8ED9) +
LIB('DSN!!0.RUNLIB.LOAD') +
PARMS('/SYSPROC DSNUTILU')
END
//*
//* Step 13: Setup to DEPLOY DSN8ES3 - Create a global temporary
//* table on the remote server
//*
//PH066S13 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) +
PLAN(DSNTEP!!) +
LIB('DSN!!0.RUNLIB.LOAD')
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
CONNECT TO SAMPLOC;
* DROP TABLE DSN8.DSN8ES3_RS_TBL;
* COMMIT;
CREATE GLOBAL TEMPORARY TABLE
DSN8.DSN8ES3_RS_TBL
( RS_SEQUENCE INTEGER NOT NULL,
RS_LINE CHAR(80) NOT NULL )
CCSID EBCDIC;
//*
//* Step 14: DEPLOY DSN8ES3 on the remote server
//*
//PH066S14 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
BIND PACKAGE(SAMPLOC.DSN8) APPLCOMPAT(V!!R1) +
DEPLOY(DSN8.DSN8ES3) +
COPYVER(V2) +
ACTION(REP)
//*
//* Step 15: Bind the plan for DSN8ED9 on the remote server
//*
//PH066S15 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB DD DSN=DSN!!0.DBRMLIB.DATA,
// DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
SET CURRENT SQLID = 'SYSADM';
GRANT BIND, EXECUTE ON PLAN DSN8ED9
TO PUBLIC;
//SYSTSIN DD *
DSN SYSTEM(DSN)
BIND PACKAGE(SAMPLOC.DSN8ED!!) MEMBER(DSN8ED9) +
APPLCOMPAT(V!!R1) +
ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
BIND PLAN(DSN8ED9) +
PKLIST(DSN8ED!!.DSN8ED9, +
SAMPLOC.DSN8ED!!.DSN8ED9, +
SAMPLOC.DSN8ES!!.*) +
ACTION(REPLACE) RETAIN +
ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) +
LIB('DSN!!0.RUNLIB.LOAD')
//*
//* Step 16: Execute DSN8ED9 to request a CREATE PROC statement for
//* SYSPROC.DSNUTILS at the remote site
//*
//PH066S16 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSN8ED9) PLAN(DSN8ED9) +
LIB('DSN!!0.RUNLIB.LOAD') +
PARMS('/SYSPROC DSNUTILS SAMPLOC')
END
//*