Creating an external SQL procedure by using JCL

Using JCL is one of several ways that you can create and prepare an external SQL procedure.

Before you begin

Deprecated function: External SQL procedures are deprecated and not as fully supported as native SQL procedures. For best results, create native SQL procedures instead. For more information, see Creating native SQL procedures and Migrating an external SQL procedure to a native SQL procedure.

About this task

Restriction: You cannot use JCL to prepare an external SQL procedure for debugging with the Db2 stored procedure debugger or the Unified Debugger. If you plan to use either of these debugging tools, use DSNTPSMP to create the external SQL procedure.

Procedure

To create an external SQL procedure by using JCL, include the following job steps in your JCL job:

  1. Issue a CREATE PROCEDURE statement that includes either the FENCED keyword or the EXTERNAL keyword and the procedure body, which is written in SQL.

    Alternatively, you can issue the CREATE PROCEDURE statement dynamically by using an application such as SPUFI, DSNTEP2, DSNTIAD, or the Db2 command line processor.

    Tip: If the routine body of the CREATE PROCEDURE statement contains embedded semicolons, change the default SQL terminator character from a semicolon to some other special character, such as the percent sign (%).

    This statement defines the stored procedure to Db2. Db2 stores the definition in the Db2 catalog.

  2. Run program DSNHPC with the HOST(SQL) option.

    This program converts the external SQL procedure source statements into a C language program. DSNHPC also writes a new CREATE PROCEDURE statement in the data set that is specified in the SYSUT1 DD statement.

  3. Precompile, compile, and link-edit the generated C program by using one of the following techniques:
    • The Db2 precompiler and JCL instructions to compile and link-edit the program
    • The SQL statement coprocessor
    When you perform this step, specify the following settings:
    • Give the DBRM the same name as the name of the load module for the external SQL procedure.
    • Specify MARGINS(1,80) for the MARGINS SQL processing option.
    • Specify the NOSEQ compiler option.

    This process produces an executable C language program.

  4. Bind the resulting DBRM into a package.

Example

Suppose that you define an external SQL procedure by issuing the following CREATE PROCEDURE statement dynamically:
CREATE PROCEDURE DEVL7083.EMPDTLSS
(
 IN  PEMPNO        CHAR(6)
,OUT PFIRSTNME     VARCHAR(12)
,OUT PMIDINIT      CHAR(1)
,OUT PLASTNAME     VARCHAR(15)
,OUT PWORKDEPT     CHAR(3)
,OUT PHIREDATE     DATE
,OUT PSALARY       DEC(9,2)
,OUT PSQLCODE      INTEGER
)
RESULT SETS 0
MODIFIES SQL DATA
FENCED
NO DBINFO
WLM ENVIRONMENT DB2AWLMR
STAY RESIDENT NO
COLLID DEVL7083
PROGRAM TYPE MAIN
RUN OPTIONS 'TRAP(OFF),RPTOPTS(OFF)'
COMMIT ON RETURN NO
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET PSQLCODE = SQLCODE;
SELECT
       FIRSTNME
     , MIDINIT
     , LASTNAME
     , WORKDEPT
     , HIREDATE
     , SALARY
INTO   PFIRSTNME
     , PMIDINIT
     , PLASTNAME
     , PWORKDEPT
     , PHIREDATE
     , PSALARY
FROM   EMP
WHERE  EMPNO = PEMPNO
;
END
You can use JCL that is similar to the following JCL to prepare the procedure:
//ADMF001S JOB (999,POK),'SQL C/L/B/E',CLASS=A,MSGCLASS=T,
// NOTIFY=ADMF001,TIME=1440,REGION=0M
/*JOBPARM SYSAFF=SC63,L=9999
// JCLLIB ORDER=(DB2AU.PROCLIB)
//*
//JOBLIB  DD  DSN=DB2A.SDSNEXIT,DISP=SHR
//        DD  DSN=DB2A.SDSNLOAD,DISP=SHR
//        DD  DSN=CEE.SCEERUN,DISP=SHR
//*----------------------------------------------------------
//*        STEP 01: PRECOMP, COMP, LKED AN SQL PROCEDURE
//*----------------------------------------------------------
//SQL01 EXEC DSNHSQL,MEM=EMPDTLSS,
//  PARM.PC='HOST(SQL),SOURCE,XREF,MAR(1,80),STDSQL(NO)',
//  PARM.PCC='HOST(C),SOURCE,XREF,MAR(1,80),STDSQL(NO),TWOPASS',
//  PARM.C='SOURCE LIST MAR(1,80) NOSEQ LO RENT',
//             PARM.LKED='AMODE=31,RMODE=ANY,MAP,RENT'
//PC.SYSLIB DD DUMMY
//PC.SYSUT2 DD DSN=&&SPDML,DISP=(,PASS), <=MAKE IT PERMANENT, IF YOU 
//           UNIT=SYSDA,SPACE=(TRK,1),      WANT TO USE IT LATER 
//           DCB=(RECFM=FB,LRECL=80) 
//PC.SYSIN     DD DISP=SHR,DSN=SG247083.PROD.DDL(&MEM.) 
//PC.SYSCIN    DD DISP=SHR,DSN=SG247083.TEST.C.SOURCE(&MEM.) 
//PCC.SYSIN    DD DISP=SHR,DSN=SG247083.TEST.C.SOURCE(&MEM.) 
//PCC.SYSLIB   DD DUMMY 
//PCC.DBRMLIB  DD DISP=SHR,DSN=SG247083.DEVL.DBRM(&MEM.) 
//LKED.SYSLMOD DD DISP=SHR,DSN=SG247083.DEVL.LOAD(&MEM.) 
//LKED.SYSIN DD *     INCLUDE SYSLIB(DSNRLI)     NAME EMPDTLSS(R) 
/* 
//*----------------------------------------------------------
//*        STEP 02: BIND THE PROGRAM
//*----------------------------------------------------------
//SQL02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//DBRMLIB  DD DSN=SG247083.DEVL.DBRM,DISP=SHR 
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//REPORT   DD SYSOUT=*
//SYSIN    DD *
//SYSTSIN DD *
 DSN SYSTEM(DB2A)
 BIND PACKAGE(DEVL7083) MEMBER(EMPDTLSS) VALIDATE(BIND) -
 OWNER(DEVL7083)
 END
//*