IBM Mainframe Forum

by **pavelbely** » Sat Jun 30, 2012 5:09 pm

Hi all,

I got stuck with running DB2 for z/Os stored procedure.

So here is the procedure itself:

CREATE PROCEDURE PBELY.CSEARCH
(IN CAID CHAR(6)
,OUT CALNAME VARCHAR(30)
)
LANGUAGE SQL
WLM ENVIRONMENT DSNBWLM
COLLID PBELY
P1: BEGIN
UPDATE PBELY.CANDIDATE
SET LNAME = 'GALLO'
WHERE CID='888';
END P1

The Compile/Link/Bind JCL:

Select all

//CSECLBE JOB (,),'SQL C/L/B/E',CLASS=A,MSGCLASS=T,
// NOTIFY=&SYSUID
// JCLLIB ORDER=(DB2.V8R1.PROCLIB)
//*
//JOBLIB DD DSN=DB2.V8R1.DSNB.SDSNEXIT,DISP=SHR
// DD DSN=DB2.V8R1.SDSNLOAD,DISP=SHR
// DD DSN=CEE.SCEERUN,DISP=SHR
//*----------------------------------------------------------
//* STEP 01: PRECOMP, COMP, LKED AN SQL PROCEDURE
//*----------------------------------------------------------
//SQL01 EXEC DSNHSQL,MEM=CSEARCH,
// PARM.PC='HOST(SQL),SOURCE,XREF,MAR(1,80),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.SYSLIB DD DUMMY
//PC.SYSUT2 DD DSN=&&SPDML,DISP=(,PASS),
// UNIT=SYSDA,SPACE=(TRK,1),
// DCB=(RECFM=FB,LRECL=80)
//PC.SYSIN DD DISP=SHR,DSN=PBELY.SQLPROC(&MEM.)
//PC.SYSCIN DD DISP=SHR,DSN=PBELY.SQLPROC(C)
//PCC.SYSIN DD DISP=SHR,DSN=PBELY.SQLPROC(C)
//PCC.SYSLIB DD DUMMY
//PCC.DBRMLIB DD DISP=SHR,DSN=PBELY.TEMP.DBRM(&MEM.)
//LKED.SYSLMOD DD DISP=SHR,DSN=PBELY.TEMP.LOAD(&MEM.)
//LKED.SYSIN DD *
INCLUDE SYSLIB(DSNRLI)
NAME CSEARCH(R)
/*
//*----------------------------------------------------------
//* STEP 02: BIND THE PROGRAM
//*----------------------------------------------------------
//SQL02 EXEC PGM=IKJEFT01,COND=(4,LT),DYNAMNBR=20
//STEPLIB DD DSN=DB2.V8R1.SDSNEXIT,DISP=SHR
// DD DSN=DB2.V8R1.SDSNLOAD,DISP=SHR
//DBRMLIB DD DSN=PBELY.TEMP.DBRM,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//REPORT DD SYSOUT=*
//SYSIN DD *
//SYSTSIN DD *
DSN SYSTEM(DSNB)
BIND PACKAGE(PBELY) MEMBER(CSEARCH) VALIDATE(BIND) -
OWNER(PBELY)
BIND PLAN(PBELY) -
PKLIST(PBELY.CSEARCH)
END
/*

The operations return codes:
10.45.16 JOB00103 -JOBNAME STEPNAME PROCSTEP RC EXCP CPU SRB CLOCK
10.45.16 JOB00103 -CSECLBE SQL01 PC 00 453 .00 .00 .01
10.45.16 JOB00103 -CSECLBE SQL01 PCC 00 404 .00 .00 .01
10.45.20 JOB00103 -CSECLBE SQL01 C 04 3611 .03 .00 .06
10.45.21 JOB00103 -CSECLBE SQL01 PLKED 04 1986 .00 .00 .01
10.45.22 JOB00103 -CSECLBE SQL01 LKED 00 244 .00 .00 .01
10.45.23 JOB00103 -CSECLBE SQL02 00 180 .01 .00 .01

Compilation warning:
pragma filetag is ignored because the locale compiler option is not specified.

Pre-Linkaage warning:
WARNING EDC4015: Unresolved references are detected:
CEEBETBL CEEROOTA CEESG003 DSNHLI EDCINPL

Then I try to run the prepared procedure:

Select all

//CSEARCH JOB (,),'CSEARCH',CLASS=A,MSGCLASS=T,
// NOTIFY=&SYSUID
//*
//JOBLIB DD DSN=DB2.V8R1.SDSNLOAD,DISP=SHR
// DD DSN=DB2.V8R1.SDSNEXIT,DISP=SHR
// DD DSN=CEE.SCEERUN,DISP=SHR
// DD DSN=PBELY.TEMP.LOAD,DISP=SHR
//*
//GO EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSNB)
RUN PROGRAM(CSEARCH) PLAN(PBELY) -
LIB('PBELY.TEMP.LOAD')
END
/*
//

But I get failure:
+IDI0002I Module CSEARCH program @ST00001 offset X'4B8': Abend S0C4-X'4' (Protection Exception)
+IDI0003I Fault ID F00124 assigned in history file FAULTANL.V4R1.HIST

READY
DSN SYSTEM(DSNB)
DSN
RUN PROGRAM(CSEARCH) PLAN(PBELY) LIB('PBELY.TEMP.LOAD')
IKJ56641I DSN ENDED DUE TO ERROR+
IKJ56641I SYSTEM ABEND CODE 0C4 REASON CODE 00000004

CEE3204S The system detected a protection exception (System Completion Code=0C4)
From entry point SQLInitRetBlk at compile unit offset +00000060 at entry offset +00000060 at address 19429420.

I think perhaps the problem is with missing CEELIB modules or WLM.

Finally here is WLM environment description

WLM JCL put in my user PROCLIB (PBELY.PROCLIB):

Select all

//DSNBWLM PROC RGN=0K,APPLENV=DSNBWLM,DB2SSN=DSNB,NUMTCB=10
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV'
//STEPLIB DD DISP=SHR,DSN=PBELY.TEMP.LOAD
// DD DISP=SHR,DSN=DB2.V8R1.SDSNEXIT
// DD DISP=SHR,DSN=DB2.V8R1.SDSNLOAD
// DD DISP=SHR,DSN=CEE.SCEERUN
//SYSTSPRT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD DUMMY
//SQLDUMMY DD DUMMY

And I can't get what to do with that procedure, should I run it somehow or what.

Please help

by **dick scherrer** » Sun Jul 01, 2012 1:05 am

Hello,

Suggest resolving this:

Select all

WARNING EDC4015: Unresolved references are detected:
CEEBETBL CEEROOTA CEESG003 DSNHLI EDCINPL

and trying again.

If there are library issues (concatenation or missing dataset(s) this is best resolved working with your db2 or system support people who support the complier(s) / linker(s)/ binder(s).

by **pavelbely** » Tue Jul 10, 2012 7:00 pm

Thanks, dick scherrer!

CEEBETBL CEEROOTA CEESG003 DSNHLI EDCINPL are located in CEE.SCEELKED and DB2.V8R1.SDSNLOAD,
that are LKED.SYSLIBs.

I tried describing them in PKLED.STEPLIB, but still have unresolved external message.
Anyway I think this is not the reason of the problem, since unresolved references are fixed during linking.

Select all

XX********************************************************************
XX* LKED: Linkedit the output from the pre-linkeditor
XX********************************************************************
XXLKED EXEC PGM=IEWL,
XX COND=((4,LT,PC),(4,LT,PCC),(4,LT,C),(4,LT,PLKED)),
XX PARM='MAP'
XXSYSLIB DD DISP=SHR,DSN=CEE.SCEELKED
XX DD DISP=SHR,DSN=DB2.V8R1.SDSNLOAD
XXSYSLIN DD DSN=&&PLKSET,DISP=(OLD,DELETE)
XX DD DDNAME=SYSIN
//LKED.SYSLMOD DD DISP=SHR,DSN=PBELY.TEMP.LOAD(&MEM.)
IEFC653I SUBSTITUTION JCL - DISP=SHR,DSN=PBELY.TEMP.LOAD(CSEARCH)
X/SYSLMOD DD DSN=&USER..RUNLIB.LOAD(&MEM),
X/ DISP=SHR
IEFC653I SUBSTITUTION JCL - DSN=USER.RUNLIB.LOAD(CSEARCH),DISP=SHR
XXSYSPRINT DD SYSOUT=*
XXSYSUT1 DD SPACE=(32000,(30,30)),UNIT=SYSDA
XX*DSNHSQL PEND REMOVE * FOR USE AS INSTREAM PROCEDURE
//LKED.SYSIN DD *

Any other suggestions, mates?

IBM Mainframe Forum

DB2 for z/Os SQL stored procedure. RC=12

DB2 for z/Os SQL stored procedure. RC=12

Re: DB2 for z/Os SQL stored procedure. RC=12

Re: DB2 for z/Os SQL stored procedure. RC=12