DSNTEJ63

//*********************************************************************
//*  Name = DSNTEJ63
//*
//*  Descriptive Name =
//*    DB2 Sample Application
//*    Phase 6
//*    Sample SQL Procedure
//*    SQL Procedure Language
//*
//*
//*    LICENSED MATERIALS - PROPERTY OF IBM
//*    5650-DB2
//*    (C) COPYRIGHT 1982, 2016 IBM Corp.  All Rights Reserved.
//*
//*    STATUS = Version 12
//*
//*  Function =
//*    This JCL prepares DSN8ES1, a sample SQL procedure that
//*    accepts a department number and returns salary and bonus data
//*    for employees in that department from the DB2 sample database.
//*
//*  Pseudocode =
//*    PH063S01 Step     Drop objects created by prior runs of this job
//*    PH063S02 Step     Prepare DSN8ES1 load module from DSN8ES1 src
//*    PH063S03 Step     Bind DSN8ES1 package in collection DSN8ES!!
//*                      Register the stored procedure using generated
//*                      DDL from the precompiler
//*                      Create the global temporary table required by
//*                      the result set
//*
//*  Dependencies =
//*  (1) This job requires the DB2-provided JCL procedure DSNHSQL
//*  (2) Run this job prior to running the client job DSNTEJ64
//*
//*  Notes =
//*
//*  Change Activity =
//*    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
//         DD  DSN=DSN!!0.RUNLIB.LOAD,DISP=SHR
//*
//*
//*********************************************************************
//*  STEP 1:  Drop any pre-existing entries for stored proc DSN8ES1
//*           and the global temporary table for its result set
//*********************************************************************
//PH063S01 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.DSN8ES1 RESTRICT;
  COMMIT;

  DROP TABLE DSN8.DSN8ES1_RS_TBL;
  COMMIT;

//*
//*********************************************************************
//*  Step 2:  Pre-compile, compile, and link-edit the stored procedure
//*********************************************************************
//PH063S02 EXEC DSNHSQL,MEM=DSN8ES1,
//         COND=(4,LT),
//         PARM.PC=('HOST(SQL),SOURCE,XREF,MAR(1,72),CCSID(37)',
//            'STDSQL(NO)'),
//         PARM.PCC=('HOST(C),SOURCE,XREF,MAR(1,80),CCSID(37)',
//            'TWOPASS,STDSQL(NO)'),
//         PARM.C='SOURCE LIST MARGINS(1,80) NOSEQ LO RENT
//             LOCALE("SAA") OPTFILE(DD:CCOPTS)',
//         PARM.LKED='AMODE=31,RMODE=ANY,MAP,RENT'
//PC.SYSLIB    DD DSN=DSN!!0.SRCLIB.DATA,
//             DISP=SHR
//PC.SYSIN     DD DSN=DSN!!0.NEW.SDSNSAMP(DSN8ES1),
//             DISP=SHR
//PC.SYSUT2    DD DSN=&&SPDDL,DISP=(,PASS),
//             UNIT=SYSDA,SPACE=(TRK,1),
//             DCB=(RECFM=FB,LRECL=80)
//PCC.DBRMLIB  DD DSN=DSN!!0.DBRMLIB.DATA(DSN8ES1),
//             DISP=SHR
//PCC.SYSLIB   DD DSN=DSN!!0.SRCLIB.DATA,
//             DISP=SHR
//LKED.SYSLMOD DD DSN=DSN!!0.RUNLIB.LOAD(DSN8ES1),
//             DISP=SHR
//LKED.SYSIN   DD *
 INCLUDE SYSLIB(DSNRLI)
 NAME DSN8ES1(R)
//*
//*********************************************************************
//*  STEP 3:  Create the global temp table for DSN8ES1's result set
//*           Register DSN8ES1 in SYSIBM.SYSROUTINES
//*           Bind the package for DSN8ES1
//*********************************************************************
//PH063S03 EXEC PGM=IKJEFT01,DYNAMNBR=20,
//             COND=(4,LT)
//DBRMLIB  DD  DSN=DSN!!0.DBRMLIB.DATA(DSN8ES1),
//             DISP=SHR
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(DSN)
  RUN PROGRAM(DSNTIAD) PLAN(DSNTIA!!) -
       LIB('DSN!!0.RUNLIB.LOAD') -
       PARM('SQLTERM(%)')
  BIND PACKAGE(DSN8ES!!) APPLCOMPAT(V!!R1) +
       QUALIFIER(DSN8!!0) -
       MEMBER(DSN8ES1) -
       ACT(REP) ISO(CS) CURRENTDATA(YES) ENCODING(EBCDIC)
 END
//SYSPRINT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  *
  SET CURRENT SQLID = 'SYSADM'
  %
  CREATE GLOBAL TEMPORARY TABLE
         DSN8.DSN8ES1_RS_TBL
         ( RS_SEQUENCE   INTEGER      NOT NULL,
           RS_EMPNO      CHAR(6)      NOT NULL,
           RS_FIRSTNME   CHAR(12)     NOT NULL,
           RS_LASTNAME   CHAR(15)     NOT NULL,
           RS_SALARY     DECIMAL(9, 2) NOT NULL,
           RS_BONUS      DECIMAL(9, 2) NOT NULL )
         CCSID EBCDIC
  %
//         DD  DSN=&&SPDDL,DISP=(OLD,DELETE)   <- From preceding step
//*