Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Calling DB2 Stored Procedures
509 views
Skip to first unread message
John Crosbie
Jul 31, 2000, 3:00:00 AM7/31/00
to
We are having problems trying to test a call to a DB2 stored procedure.
We have a CICS program 'RCTESTO' executing the SQL CALL statement.
==========================================
Unexpected Program Failure
An unexpected input/output or program error occurred in the
program you were running. Please make a note of the program
name, date, time, and initial error messages and report them to your
system administrator.
Program name ... RCTESTO
Date ........... 07/31/00
Time ........... 15:40:41
Error Messages:
ELA00093I An error occurred in program RCTESTO, process or group DB2-TEST
ELA00073P SQL error, command = SQLEXEC, SQL code = -471
ELA00074I SQL error message: RCSPROC ,00E79006.
PF3=EXIT
===========================================
The program has been generated using VAGen 4.0 with fp1
compiled using COBOL for MVS 1.1
bound to DB2 for MVS 5.2
and it is running under LE/370 1.8.
The stored procedures address space is NOT started under WLM control.
The messages for the stored procedures started task read:
===========================================
+DSNX964I DSNX9STP THE DB2-ESTABLISHED STORED PROCEDURES ADDRESS SPACE
FOR SUBSYSTEM DB2D IS STARTED
+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
RETURN CODE '0000000C'X FOR FUNCTION ADD_ENTRY SSN= DB2D PROC=
DB2DSPAS ASID= 0079 WLM_ENV=
===========================================
Program map reads :
. . . . . . . . . . . . . . . . . . . . . . . . . . .
SYSVIEW 7.2 MVSB -------------- Load Module Map ------------- 31/07/00 16:15:24
Command ====> Scroll *===> CSR
------------------------------------------------- Lvl 5 Row 1-13/38 Col 1-66/66
Dsn ASIM.V20.BATCH.LOADLIB Volser DEV004 Unit 3390-3
Mbrs 136 Dirblk 300 Used 20 Lrecl 0 Blksize 13030 Recfm U
PDS load module RCSPROC
-------------------------------------------------------------------------------
Cmd Sec-Name Sec-Len Sec-Type Ent-Name Ent-Type Sec-Loc Mod-Loc
RCSPROC 00003CC0 SD RCSPROC 00000000 00000000
DSNAA 00000100 SD DSNAA 00000000 00003CC0
. . . DS#AA 000000E4 00003DA4
. . . DSNAA@ 000000FC 00003DBC
DSNALI 00000308 SD DSNALI 00000000 00003DC0
. . . DSNASP 00000018 00003DD8
. . . DSNHLI2 0000001E 00003DDE
. . . DSNHLI 00000024 00003DE4
. . . DSNWLI2 0000002A 00003DEA
. . . DSNWLI 00000030 00003DF0
ELARMAIN 00000130 SD ELARMAIN MAIN 00000000 000040C8
ELAASADR 00000080 SD ELAASADR 00000000 000041F8
. . . ELAASC31 00000028 00004220
ELAASSGN 000000B0 SD ELAASSGN 00000000 00004278
. . . ELAFXNUM 00000070 000042E8
ELARSINT 00000260 SD ELARSINT 00000000 00004328
. . . ELAASTRM 0000001A 00004342
ELARSVCS 00000050 SD ELARSVCS 00000000 00004588
ELASTB07 00000210 SD ELASTB07 00000000 000045D8
. . . CBLTDLI 00000020 000045F8
CEESG005 00000018 SD CEESG005 00000000 000047E8
DSNHADDR 00000058 SD DSNHADDR 00000000 00004800
. . . DS#HADDR 0000000A 0000480A
DSNHMVHW 00000020 SD DSNHMVHW 00000000 00004858
CEEBETBL 00000024 SD CEEBETBL 00000000 00004878
CEESTART 0000007C SD CEESTART 00000000 000048A0
IGZCBSN 00000560 SD IGZCBSN 00000000 00004920
CEEARLU 000000B8 SD CEEARLU 00000000 00004E80
CEEBPIRA 000002D0 SD CEEBPIRA 00000000 00004F38
. . . CEEINT 00000000 00004F38
. . . CEEBPIRB 00000000 00004F38
. . . CEEBPIRC 00000000 00004F38
CEECPYRT 000000F0 SD CEECPYRT 00000000 00005208
CEEBPUBT 00000070 SD CEEBPUBT 00000000 000052F8
CEEBTRM 000000AC SD CEEBTRM 00000000 00005368
CEEBLLST 0000005C SD CEEBLLST 00000000 00005418
. . . CEELLIST 00000010 00005428
CEEBINT 00000008 SD CEEBINT 00000000 00005478
********************************* End of Data *********************************
The sysibm.sysprocedures values are
001 PROCEDURE CHAR(18) RCSPROC
002 AUTHID CHAR(8)
003 LUNAME CHAR(8)
004 LOADMOD CHAR(8) RCSPROC
005 LINKAGE CHAR(1)
006 COLLID CHAR(18) RCSPROC
007 LANGUAGE CHAR(8) COBOL
008 ASUTIME INTEGER 0
009 STAYRESIDENT CHAR(1)
010 IBMREQD CHAR(1) N
011 PARMLIST VC(3000) EZEI1 CHAR(11) IN, EZERCD CHAR(42) OUT
012 RESULT_SETS SMALLINT 1
013 WLM_ENV CHAR(18)
014 PGM_TYPE CHAR(1) M
015 EXTERNAL_SECURITY CHAR(1) N
016 COMMIT_ON_RETURN CHAR(1) N
. . . . . . . . . . . . . . . . . . . . . . . . . . .
The linkage entry in the RCSPROC follows :
LINKAGE SECTION.
* RTS NLS-DEPENDENT INSTALLATION OPTIONS CONTROL BLOCK
COPY ELARHIOE.
* RTS NLS-INDEPENDENT INSTALLATION OPTIONS CONTROL BLOCK
COPY ELARHIOP.
* RESOURCE CONTROL BLOCK
COPY ELARHRSC.
* STATIC CONTROL BLOCK
COPY ELARHSCB.
* ITEM-ID WAS RENAMED TO EZEI-1
01 EZEI-1 PIC X(11).
*-----------------------------------------------------------------
* RECORD NAME : RCS-RETURN
* FILE ORGANIZATION : WORKSTOR
* MODIFICATION DATE : 7/11/00
* MODIFICATION TIME : 10:57:28
* RECORD PROLOGUE :
*
*-----------------------------------------------------------------
* RCS-RETURN WAS RENAMED TO EZER-1
01 EZERCD-EZER-1-GP.
02 EZER-1.
* OUTPUT-LEN WAS RENAMED TO EZEI-2
05 EZEI-2 PIC S9(4) COMP.
* ITEM-DESC WAS RENAMED TO EZEI-3
05 EZEI-3 PIC X(40).
EXEC SQL
DECLARE EZE001001RCSPROC CURSOR
FOR
SELECT
ITEM_ID ........ rah rah FROM
CORP_ITEM T1
WHERE ITEM_ID =
:EZESQL-EZER-2-H.EZEI-4:EZESQL-EZER-2-I.EZEI-4
END-EXEC.
*-----------------------------------------------------------------
* SQL CURSOR CONTROL BLOCKS
*-----------------------------------------------------------------
01 EZECRS-CURSOR-BLOCKS.
02 EZECRS-ID PIC X(8).
02 EZECRS-CNT PIC S9(4) COMP.
*-----------------------------------------------------------------
* CURSOR CONTROL BLOCK FOR SQL ROW EZER-2
*-----------------------------------------------------------------
02 EZECRS-EZER-2-CB.
05 EZECRS-EZER-2-TYP PIC X(4).
88 EZECRS-EZER-2-SETI VALUE "SETI".
88 EZECRS-EZER-2-SETU VALUE "SETU".
88 EZECRS-EZER-2-SIWH VALUE "SIWH".
88 EZECRS-EZER-2-SUWH VALUE "SUWH".
88 EZECRS-EZER-2-UPDT VALUE "UPDT".
88 EZECRS-EZER-2-INQU VALUE "INQU".
05 EZECRS-EZER-2-ID PIC S9(4) COMP.
88 EZECRS-EZER-2-CLOS VALUE 0.
PROCEDURE DIVISION USING
EZEI-1,
EZERCD-EZER-1-GP.
*-----------------------------------------------------------------
* MAIN PROCESS
*-----------------------------------------------------------------
EZEMAIN-PROCESS SECTION.
PERFORM EZECONTROL
GOBACK.
*-----------------------------------------------------------------
* BEGIN PROCESS
*-----------------------------------------------------------------
EZEBEGIN-PROCESSES SECTION.
CONTINUE.
*-----------------------------------------------------------------
* SQL PROCESS OPTIONS BYPASS
*-----------------------------------------------------------------
===========================================================
We get some linkedit warnings :
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARSVCS
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELAASADR
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARSINT
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARMAIN
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELAASSGN
similar to an earlier postings on this site, but our sys prog assures me that these warnings
are not contributing to the problem.
We have a CICS program 'RCTESTO' executing the SQL CALL statement.
==========================================
Unexpected Program Failure
An unexpected input/output or program error occurred in the
program you were running. Please make a note of the program
name, date, time, and initial error messages and report them to your
system administrator.
Program name ... RCTESTO
Date ........... 07/31/00
Time ........... 15:40:41
Error Messages:
ELA00093I An error occurred in program RCTESTO, process or group DB2-TEST
ELA00073P SQL error, command = SQLEXEC, SQL code = -471
ELA00074I SQL error message: RCSPROC ,00E79006.
PF3=EXIT
===========================================
The program has been generated using VAGen 4.0 with fp1
compiled using COBOL for MVS 1.1
bound to DB2 for MVS 5.2
and it is running under LE/370 1.8.
The stored procedures address space is NOT started under WLM control.
The messages for the stored procedures started task read:
===========================================
+DSNX964I DSNX9STP THE DB2-ESTABLISHED STORED PROCEDURES ADDRESS SPACE
FOR SUBSYSTEM DB2D IS STARTED
+DSNX962I DSNX9SER THE LE/370 CEEPIPI SERVICE RETURNED AN UNEXPECTED
RETURN CODE '0000000C'X FOR FUNCTION ADD_ENTRY SSN= DB2D PROC=
DB2DSPAS ASID= 0079 WLM_ENV=
===========================================
Program map reads :
. . . . . . . . . . . . . . . . . . . . . . . . . . .
SYSVIEW 7.2 MVSB -------------- Load Module Map ------------- 31/07/00 16:15:24
Command ====> Scroll *===> CSR
------------------------------------------------- Lvl 5 Row 1-13/38 Col 1-66/66
Dsn ASIM.V20.BATCH.LOADLIB Volser DEV004 Unit 3390-3
Mbrs 136 Dirblk 300 Used 20 Lrecl 0 Blksize 13030 Recfm U
PDS load module RCSPROC
-------------------------------------------------------------------------------
Cmd Sec-Name Sec-Len Sec-Type Ent-Name Ent-Type Sec-Loc Mod-Loc
RCSPROC 00003CC0 SD RCSPROC 00000000 00000000
DSNAA 00000100 SD DSNAA 00000000 00003CC0
. . . DS#AA 000000E4 00003DA4
. . . DSNAA@ 000000FC 00003DBC
DSNALI 00000308 SD DSNALI 00000000 00003DC0
. . . DSNASP 00000018 00003DD8
. . . DSNHLI2 0000001E 00003DDE
. . . DSNHLI 00000024 00003DE4
. . . DSNWLI2 0000002A 00003DEA
. . . DSNWLI 00000030 00003DF0
ELARMAIN 00000130 SD ELARMAIN MAIN 00000000 000040C8
ELAASADR 00000080 SD ELAASADR 00000000 000041F8
. . . ELAASC31 00000028 00004220
ELAASSGN 000000B0 SD ELAASSGN 00000000 00004278
. . . ELAFXNUM 00000070 000042E8
ELARSINT 00000260 SD ELARSINT 00000000 00004328
. . . ELAASTRM 0000001A 00004342
ELARSVCS 00000050 SD ELARSVCS 00000000 00004588
ELASTB07 00000210 SD ELASTB07 00000000 000045D8
. . . CBLTDLI 00000020 000045F8
CEESG005 00000018 SD CEESG005 00000000 000047E8
DSNHADDR 00000058 SD DSNHADDR 00000000 00004800
. . . DS#HADDR 0000000A 0000480A
DSNHMVHW 00000020 SD DSNHMVHW 00000000 00004858
CEEBETBL 00000024 SD CEEBETBL 00000000 00004878
CEESTART 0000007C SD CEESTART 00000000 000048A0
IGZCBSN 00000560 SD IGZCBSN 00000000 00004920
CEEARLU 000000B8 SD CEEARLU 00000000 00004E80
CEEBPIRA 000002D0 SD CEEBPIRA 00000000 00004F38
. . . CEEINT 00000000 00004F38
. . . CEEBPIRB 00000000 00004F38
. . . CEEBPIRC 00000000 00004F38
CEECPYRT 000000F0 SD CEECPYRT 00000000 00005208
CEEBPUBT 00000070 SD CEEBPUBT 00000000 000052F8
CEEBTRM 000000AC SD CEEBTRM 00000000 00005368
CEEBLLST 0000005C SD CEEBLLST 00000000 00005418
. . . CEELLIST 00000010 00005428
CEEBINT 00000008 SD CEEBINT 00000000 00005478
********************************* End of Data *********************************
The sysibm.sysprocedures values are
001 PROCEDURE CHAR(18) RCSPROC
002 AUTHID CHAR(8)
003 LUNAME CHAR(8)
004 LOADMOD CHAR(8) RCSPROC
005 LINKAGE CHAR(1)
006 COLLID CHAR(18) RCSPROC
007 LANGUAGE CHAR(8) COBOL
008 ASUTIME INTEGER 0
009 STAYRESIDENT CHAR(1)
010 IBMREQD CHAR(1) N
011 PARMLIST VC(3000) EZEI1 CHAR(11) IN, EZERCD CHAR(42) OUT
012 RESULT_SETS SMALLINT 1
013 WLM_ENV CHAR(18)
014 PGM_TYPE CHAR(1) M
015 EXTERNAL_SECURITY CHAR(1) N
016 COMMIT_ON_RETURN CHAR(1) N
. . . . . . . . . . . . . . . . . . . . . . . . . . .
The linkage entry in the RCSPROC follows :
LINKAGE SECTION.
* RTS NLS-DEPENDENT INSTALLATION OPTIONS CONTROL BLOCK
COPY ELARHIOE.
* RTS NLS-INDEPENDENT INSTALLATION OPTIONS CONTROL BLOCK
COPY ELARHIOP.
* RESOURCE CONTROL BLOCK
COPY ELARHRSC.
* STATIC CONTROL BLOCK
COPY ELARHSCB.
* ITEM-ID WAS RENAMED TO EZEI-1
01 EZEI-1 PIC X(11).
*-----------------------------------------------------------------
* RECORD NAME : RCS-RETURN
* FILE ORGANIZATION : WORKSTOR
* MODIFICATION DATE : 7/11/00
* MODIFICATION TIME : 10:57:28
* RECORD PROLOGUE :
*
*-----------------------------------------------------------------
* RCS-RETURN WAS RENAMED TO EZER-1
01 EZERCD-EZER-1-GP.
02 EZER-1.
* OUTPUT-LEN WAS RENAMED TO EZEI-2
05 EZEI-2 PIC S9(4) COMP.
* ITEM-DESC WAS RENAMED TO EZEI-3
05 EZEI-3 PIC X(40).
EXEC SQL
DECLARE EZE001001RCSPROC CURSOR
FOR
SELECT
ITEM_ID ........ rah rah FROM
CORP_ITEM T1
WHERE ITEM_ID =
:EZESQL-EZER-2-H.EZEI-4:EZESQL-EZER-2-I.EZEI-4
END-EXEC.
*-----------------------------------------------------------------
* SQL CURSOR CONTROL BLOCKS
*-----------------------------------------------------------------
01 EZECRS-CURSOR-BLOCKS.
02 EZECRS-ID PIC X(8).
02 EZECRS-CNT PIC S9(4) COMP.
*-----------------------------------------------------------------
* CURSOR CONTROL BLOCK FOR SQL ROW EZER-2
*-----------------------------------------------------------------
02 EZECRS-EZER-2-CB.
05 EZECRS-EZER-2-TYP PIC X(4).
88 EZECRS-EZER-2-SETI VALUE "SETI".
88 EZECRS-EZER-2-SETU VALUE "SETU".
88 EZECRS-EZER-2-SIWH VALUE "SIWH".
88 EZECRS-EZER-2-SUWH VALUE "SUWH".
88 EZECRS-EZER-2-UPDT VALUE "UPDT".
88 EZECRS-EZER-2-INQU VALUE "INQU".
05 EZECRS-EZER-2-ID PIC S9(4) COMP.
88 EZECRS-EZER-2-CLOS VALUE 0.
PROCEDURE DIVISION USING
EZEI-1,
EZERCD-EZER-1-GP.
*-----------------------------------------------------------------
* MAIN PROCESS
*-----------------------------------------------------------------
EZEMAIN-PROCESS SECTION.
PERFORM EZECONTROL
GOBACK.
*-----------------------------------------------------------------
* BEGIN PROCESS
*-----------------------------------------------------------------
EZEBEGIN-PROCESSES SECTION.
CONTINUE.
*-----------------------------------------------------------------
* SQL PROCESS OPTIONS BYPASS
*-----------------------------------------------------------------
===========================================================
We get some linkedit warnings :
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARSVCS
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELAASADR
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARSINT
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELARMAIN
EW2646W 4B07 ESD RMODE(24) CONFLICTS WITH USER-SPECIFIED RMODE(ANY) FOR SECTION ELAASSGN
similar to an earlier postings on this site, but our sys prog assures me that these warnings
are not contributing to the problem.
Any help would be appreciated !!!!
Thanks John.
Dan Bruce
Aug 1, 2000, 3:00:00 AM8/1/00
to
John,
It looks like there is an incompatibility between your compiled stored
procedure and your LE envirionement. The error code for -471 states:
It looks like there is an incompatibility between your compiled stored
procedure and your LE envirionement. The error code for -471 states:
-471 SQL CALL FOR STORED PROCEDURE name FAILED DUE TO REASON rc
Explanation: DB2 received an SQL CALL statement for a stored procedure. The
CALL statement was not accepted because of DB2 reason code rc.
name
The name of the stored procedure specified on the SQL CALL statement.
rc
The DB2 reason code describing the cause of the failure. Possible values
are: 00E79000, 00E79001, 00E79002, 00E79003, 00E79004, 00E79005, 00E79006,
00E79007, 00E7900B, 00E7900C.
If you look up 00E79006:
00E79006
Explanation: DB2 received an SQL CALL statement for a stored procedure. The
CALL statement cannot be honored, because the stored procedure load module
is not compatible with Language Environment preinitialization environment.
System Action: The SQL CALL statement is rejected with SQLCODE -471 and this
reason code.
Programmer Response: Ensure the stored procedure is compiled with a level of
the compiler which generates a module that can be a target of a Language
Environment preinitialization CEEPIPI call. Ensure that the stored procedure
is link edited with the Language Environment runtime library.
Verify that the stored procedure is described correctly in
SYSIBM.SYSPROCEDURES as a main or sub program. This failure can occur if the
procedure options for the stored procedure indicate it is a subprogram and
the specification in the catalog indicates it is a main
program.
Message DSNX962I will also be displayed on the MVS system console. This
message indicates the specific Language Environment preinitialization
service that failed and the failing return code.
For the required levels of the compilers, see Application Programming and
SQL Guide.
Dan
John Crosbie
Aug 2, 2000, 3:00:00 AM8/2/00
to
Dan,
Thanks for the response !
We had success today.
The linkedit step had been defined incorrectly.
We previously had ELARMAIN as the entry point in the stored procedure.
Thanks John.
Thanks for the response !
We had success today.
The linkedit step had been defined incorrectly.
We previously had ELARMAIN as the entry point in the stored procedure.
Thanks John.
0 new messages