I follow these steps:
1. Create the procedure using either SPUFI or DSNTEP2. The key is to
change the SQL termination character to something other than ;. I do
this for TEP2:
DSN SYSTEM(TDBW)
RUN PROGRAM(DSNTEP2) PLAN(DB2TEP2) PARMS('SQLTERM(@)')
END
2. Generate C language code, and pre-compile, compile and link that:
//**********************************************************************
//* DSNHSQL - COMPILE AND LINKEDIT A DB2 SQL PROCEDURE
//*
//DSNHSQL PROC WSPC=500,MEM=TEMPNAME,MEMO=TEMPNAME,USER=USER
//*
//*********************************************************************
//* PC: PRECOMPILE THE SQL PROCEDURE SOURCE
//*********************************************************************
//PC EXEC PGM=DSNHPC,
// PARM='HOST(SQL)'
//STEPLIB DD DISP=SHR,DSN=DB2LIVE.TDBW.SDSNEXIT
// DD DISP=SHR,DSN=DB2LIVE.TDBW.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSCIN DD DSN=&&DSNHSQL,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(800,(&WSPC,&WSPC))
//SYSLIB DD DISP=SHR,DSN=FDB.TDBW.CHRMS.DDL
//SYSUT1 DD DUMMY <-- DML TO REGISTER PSM SP (V5.1 ONLY)
//SYSUT2 DD DUMMY <-- DDL TO REGISTER PSM SP (V6.1 AND SUBSEQUENT)
//*
//*********************************************************************
//* PCC: PRECOMPILE C SOURCE GENERATED BY THE PREVIOUS STEP
//*********************************************************************
//PCC EXEC PGM=DSNHPC,
// COND=(4,LT,PC),
// PARM='HOST(C),MAR(1,80)'
//STEPLIB DD DISP=SHR,DSN=DB2LIVE.TDBW.SDSNEXIT
// DD DISP=SHR,DSN=DB2LIVE.TDBW.SDSNLOAD
//DBRMLIB DD DISP=SHR,DSN=FDB.AGRV.DBRMLIB(&MEMO)
//SYSPRINT DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD DSN=&&DSNHSQL,DISP=(OLD,DELETE)
//SYSCIN DD DSN=&&DSNHOUT,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(800,(&WSPC,&WSPC))
//*SYSLIB DD DISP=SHR,DSN=&USER..SRCLIB.DATA
//SYSLIB DD DISP=SHR,DSN=FDB.AGRV.SOURCE
//SYSUT1 DD SPACE=(800,(&WSPC,&WSPC),,,ROUND),UNIT=SYSDA
//SYSUT2 DD SPACE=(800,(&WSPC,&WSPC),,,ROUND),UNIT=SYSDA
//*
//*********************************************************************
//* C: COMPILE THE OUTPUT FROM THE PRECOMPILER
//*********************************************************************
//C EXEC PGM=CBCDRVR,
// COND=((4,LT,PC),(4,LT,PCC)),
// PARM=('MAR(1,80) NOSEQ LO RENT')
//SYSMSGS DD DUMMY
//SYSLIB DD DUMMY
//* DD DISP=SHR,DSN=CEE.SCEEH.SYS.H
//SYSLIN DD DSN=&&LOADSET,DISP=(MOD,PASS),UNIT=SYSDA,
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSPRINT DD SYSOUT=*
//SYSCPRT DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSIN DD DSN=&&DSNHOUT,DISP=(OLD,DELETE)
//SYSUT1 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSUT2 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSUT3 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSUT4 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSUT5 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)
//SYSUT6 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)
//SYSUT7 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)
//SYSUT8 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)
//SYSUT9 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=VB,LRECL=137,BLKSIZE=882)
//SYSUT10 DD SYSOUT=*
//SYSUT14 DD UNIT=SYSDA,DISP=(NEW,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=3200,BLKSIZE=12800)
//*
//*********************************************************************
//* PLKED: PRE-LINKEDIT THE OBJECT MODULE FROM THE C COMPILER
//*********************************************************************
//PLKED EXEC PGM=EDCPRLK,
// COND=((4,LT,PC),(4,LT,PCC),(4,LT,C))
//STEPLIB DD DISP=SHR,DSN=CEE.SCEERUN
//SYSMSGS DD DISP=SHR,DSN=CEE.SCEEMSGP(EDCPMSGE)
//SYSLIB DD DUMMY
//SYSIN DD DSN=&&LOADSET,DISP=(OLD,DELETE)
//SYSMOD DD DSN=&&PLKSET,UNIT=SYSDA,DISP=(MOD,PASS),
// SPACE=(32000,(30,30)),
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//*
//********************************************************************
//* LKED: LINKEDIT THE OUTPUT FROM THE PRE-LINKEDITOR
//********************************************************************
//LKED EXEC PGM=IEWL,
// COND=((4,LT,PC),(4,LT,PCC),(4,LT,C),(4,LT,PLKED)),
// PARM='MAP'
//SYSLIB DD DISP=SHR,DSN=CEE.SCEELKED
// DD DISP=SHR,DSN=DB2LIVE.TDBW.SDSNLOAD
//SYSLIN DD DSN=&&PLKSET,DISP=(OLD,DELETE)
// DD DDNAME=SYSIN
//SYSLMOD DD DISP=SHR,DSN=&USER..RUNLIB.LOAD(&MEM)
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD SPACE=(32000,(30,30)),UNIT=SYSDA
// PEND
//********************************************************************
//********************************************************************
//S1 EXEC DSNHSQL,MEM=LV07REPP,MEMO=PLV07REP,WSPC=60,
// PARM.PC='HOST(SQL),SOURCE,XREF,MAR(1,72),STDSQL(NO)',
// PARM.PCC='HOST(C),SOURCE,XREF,MAR(1,80),STDSQL(NO)',
// PARM.C='SOURCE LIST MAR(1,80) NOSEQ LO RENT',
// PARM.LKED='AMODE=31,RMODE=ANY,MAP,RENT'
//PC.SYSUT2 DD DSN=FDB.TDBW.CHRMS.DDL(SYSUT2),DISP=SHR
//PC.SYSIN DD DSN=FDB.TDBW.CHRMS.DDL(&MEM),DISP=SHR
//LKED.SYSLMOD DD DSN=DB2LIVE.TDBW.STORED.PROCS(&MEM),DISP=SHR
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME PLV07REP(R)
/*
//
3. BIND the package and grant execute privileges.
-----Original Message-----
From: divakar
[mailto:db2udbdba-ezmlmshield-x2139724.[Email address protected]
Sent: Wednesday, November 23, 2005 12:50 AM
To: LazyDBA Discussion
Subject: SQL Procedure Z/OS
We are having difficulties in compiling, binding and cataloging an SQL
Stored procedure for DB2 for OS 390. Can someone list out the steps
required to do the same.
It wud be helpful if someone can send the relevant JCL.
Divakar
Confidentiality Notice
The information contained in this electronic message and any attachments
to this message are intended
for the exclusive use of the addressee(s) and may contain confidential
or privileged information. If
you are not the intended recipient, please notify the sender at Wipro or
[Email address protected] immediately
and destroy all copies of this message and any attachments.
---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
=========================================================
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========================================================
DB2 & UDB email list listserv db2-l LazyDBA home page