Previous Discussion	Next Discussion

DB2 Stored Proc resultset NOT sent back to .Net App We have a COBOL stored procedure in DB2 on the mainframe using a cursor.   When the SP cursor finishes and is closed the resultset is NOT sent back to the .Net client (ASP, VS2005).  If the cursor is left open then a resultset is sent back to the client but we need to somehow send a msg back to  the SP on the mainframe to close the open cursor OR find out why the resultset is not being sent back to the client. Technical Info: IBM DB2 Connect v8.2 is used to connect .Net and Mainframe DB2 on z/OS; When registering a procedure with CREATE PROCEDURE statement, we're indicating the the number of resultsets to be returned with DYNAMIC RESULT SETS CLAUSE. Also, we're using WITH RETURN TO clause to return the result set to the invoker; We did tried closing the cursor at various pertinent return points in stored procedures but that always resulted in disconnect between client and DB Server information exchange Any Ideas?? You may also contact me at thomas.s.gow@bankofamerica.com Thx Started By thomasgow on Sep 22, 2006 at 1:53:30 PM

2 Response(s) | Reply

Earlier Replies | Replies 1 to 2 of 2 | Later Replies

Monte on Sep 22, 2006 at 2:01:22 PM (# 1) What's the cursor look like? What's the code look like that calls the stored procedure? thomasgow on Sep 22, 2006 at 4:02:59 PM (# 2) Monte, below is the cobol sp with the results sent back to the client... IDENTIFICATION DIVISION. 00000010 PROGRAM-ID. DAPSPR05. 00000020 INSTALLATION. BANK OF AMERICA. 00000030 DATE-WRITTEN. MAY 12,2006 00000050 DATE-COMPILED. 00000060 ******************************************************************00000070 * PROGRAM NARRATIVE *00000080 * REGCC *00000090 * STORED PROCEDURE - DAPSPR05 *00000091 * -------------------------------------------------------------- *00000093 * FUNCTION : THIS PROGRAM LISTS THE TRANSACTIONS WHICH OCCURED *00000095 * DURING THE PAST 7 DAYS FROM CURRENT DATE. IT DOES *00000096 * NOT INCLUDE THE IMAGE TRANSACTIONS. *00000098 * *00000099 * INPUTS : INPUT PARAMETERS FROM WEBSERVICE *00000100 * *00000101 * OUTPUTS : WARNING MESSAGES *00000102 * *00000103 * RESULTSET: SELECTED ROWS FROM ATM_DEP_TRAN. *00000104 * *00000110 * -------------------------------------------------------------- *00000140 ******************************************************************00000150 ******************************************************************00000160 * PROGRAM CHANGE LOG *00000170 * *00000180 * DATE PGMR PACKAGE DESCRIPTION *00000190 * ________ ___ __________ ______________________________________*00000191 * 05/12/06 TCS DAP0000214 INITIAL VERSION. *00000192 * *00000193 * 09/07/06 TCS DAP0000237 Recompilation for the change in the *00000194 * Image tables. *00000195 * *00000196 * 09/20/06 TCS DAP0000255 Changed the QUERY to improve *00000197 * performance. *00000198 ******************************************************************00000199 00000200 ENVIRONMENT DIVISION. 00000210 00000220 CONFIGURATION SECTION. 00000230 *SOURCE-COMPUTER. IBM-370 WITH DEBUGGING MODE. 00000240 SOURCE-COMPUTER. 00000241 OBJECT-COMPUTER. IBM-370. 00000250 00000260 DATA DIVISION. 00000270 00000280 *----------------------------------------------------------*------00000290 * W O R K I N G S T O R A G E S E C T I O N * 00000291 *----------------------------------------------------------*------00000292 WORKING-STORAGE SECTION. 00000293 00000294 01 WS-MODULE-ID PIC X(8) VALUE 'DAPSPR05'. 00000296 00000297 01 WS-ATM-ID PIC X(08) VALUE SPACES. 00000298 01 WS-TRAN-ID PIC S9(04) USAGE COMP. 00000299 01 WS-POST-DATE PIC X(10) VALUE SPACES. 00000302 D255 01 WS-DATE-COMPARE PIC X(10) VALUE SPACES. 00000303 01 WS-ERROR-MSG-FIN PIC X(100) VALUE SPACES. 00000311 01 WS-ERROR-MSG-COM. 00000312 05 WS-PARA-NAME PIC X(25) VALUE SPACES. 00000313 05 WS-ERROR-MSG PIC X(100) VALUE SPACES. 00000314 00000315 00000320 * Variables to handle SQLCODE and ERRORCODE 00000330 00000340 01 WS-CURSOR-ERR-FIELDS. 00000350 10 WS-UNCONV-SQLCODE PIC S9(10) VALUE +0. 00000360 10 WS-CONV-SQLCODE PIC +9(10) VALUE SPACES. 00000370 10 WS-ERROR-CODE REDEFINES WS-CONV-SQLCODE 00000380 PIC X(11). 00000390 00000396 ******************************************************************00000397 * DB2 S Q L C O M M U N I C A T I O N A R E A *00000398 ******************************************************************00000399 00000400 EXEC SQL INCLUDE 00000410 SQLCA 00000420 END-EXEC. 00000430 00000440 ******************************************************************00000441 * DB2 TABLE DCLGEN - HOST VARIABLES *00000442 ******************************************************************00000443 * ATM_DEP_TRAN TABLE *00000445 ******************************************************************00000446 00000447 EXEC SQL INCLUDE 00000448 DCLATMDT 00000449 END-EXEC. 00000450 00000451 ******************************************************************00000452 * DEP_IMG_TRAN TABLE *00000453 ******************************************************************00000454 00000455 D237 * EXEC SQL INCLUDE 00000456 D237 * DCLIMGTR 00000457 D237 * END-EXEC. 00000458 00000459 ******************************************************************00000460 00000461 EXEC SQL 00000470 DECLARE TRANLIST CURSOR WITH RETURN FOR 00000490 00000491 SELECT A.ATM_ID_GRP 00000492 , A.MSG_SER_NO 00000493 , A.TRAN_BUS_DT 00000494 , A.TRAN_ST_IND 00000495 00000496 FROM ATM_DEP_TRAN A 00000497 WHERE A.ATM_ID_GRP = :WS-ATM-ID 00000500 D255 * AND A.MSG_SER_NO = :WS-TRAN-ID 00000600 D255 * AND A.TRAN_BUS_DT > CURRENT DATE - 7 DAYS 00001050 D255 AND A.TRAN_BUS_DT > :WS-DATE-COMPARE 00001051 D255 AND A.MSG_SER_NO = :WS-TRAN-ID 00001052 ORDER BY A.TRAN_BUS_DT DESC 00001056 FOR FETCH ONLY 00001060 END-EXEC. 00001080 00001090 LINKAGE SECTION. 00001091 00001092 01 ATM-ID PIC X(10) VALUE SPACES. 00001093 01 TRAN-ID PIC S9(04) USAGE COMP. 00001094 01 POST-DATE PIC X(10) VALUE SPACES. 00001096 01 ERROR-CODE PIC X(11) VALUE SPACES. 00001097 01 ERROR-MSG PIC X(100) VALUE SPACES. 00001098 00001099 PROCEDURE DIVISION USING ATM-ID , 00001100 TRAN-ID , 00001101 POST-DATE , 00001102 ERROR-CODE , 00001103 ERROR-MSG. 00001110 00001120 0000-MAIN-PARA-START. 00001130 00001140 PERFORM 1000-INITIALIZE-PARA 00001150 THRU 1000-EXIT. 00001160 00001170 D255 PERFORM 1500-GET-PAST-7DAYS 00001171 D255 THRU 1500-EXIT. 00001172 00001173 PERFORM 2000-HANDLING-CURSOR 00001180 THRU 2000-EXIT. 00001190 00001191 GOBACK. 00001192 . 00001193 0000-EXIT. 00001194 EXIT. 00001195 00001196 1000-INITIALIZE-PARA. 00001197 MOVE '1000-INITIALIZE-PARA' TO WS-PARA-NAME 00001198 D DISPLAY WS-PARA-NAME 00001199 D DISPLAY WS-MODULE-ID 00001200 00001201 MOVE SPACES TO WS-ATM-ID , 00001202 WS-POST-DATE , 00001203 WS-ERROR-CODE , 00001204 WS-ERROR-MSG 00001210 MOVE ZEROS TO WS-TRAN-ID 00001211 00001220 INITIALIZE WS-CURSOR-ERR-FIELDS 00001230 INITIALIZE WS-ERROR-MSG-COM 00001231 00001240 MOVE ATM-ID TO WS-ATM-ID 00001250 MOVE TRAN-ID TO WS-TRAN-ID 00001270 MOVE POST-DATE TO WS-POST-DATE 00001271 D DISPLAY ' WS-ATM-ID' WS-ATM-ID 00001272 D DISPLAY ' WS-TRAN-ID' WS-TRAN-ID 00001273 00001274 . 00001280 1000-EXIT. 00001290 EXIT. 00001291 00001292 D255 1500-GET-PAST-7DAYS. 00001296 D255 MOVE '1500-GET-PAST-7DAYS ' TO WS-PARA-NAME 00001297 D255 D DISPLAY WS-PARA-NAME 00001298 D255 00001299 D255 EXEC SQL 00001300 D255 SELECT CURRENT DATE - 7 DAYS 00001301 D255 INTO :WS-DATE-COMPARE 00001302 D255 FROM SYSIBM.SYSDUMMY1 00001303 D255 END-EXEC. 00001304 D255 . 00001305 D255 1500-EXIT. 00001306 D255 EXIT. 00001307 00001308 2000-HANDLING-CURSOR. 00001309 ******************************************************************00001310 * THE 2000-HANDLING-CURSOR OPENS THE CURSOR. 00001311 ******************************************************************00001312 00001313 MOVE '2000-HANDLING-CURSOR' TO WS-PARA-NAME 00001314 D DISPLAY WS-PARA-NAME 00001315 00001316 EXEC SQL 00001317 OPEN TRANLIST 00001320 END-EXEC. 00001330 00001340 PERFORM 3000-TRANLIST-CSR-SQLCODE. 00001350 . 00001360 2000-EXIT. 00001370 EXIT. 00001380 00001390 3000-TRANLIST-CSR-SQLCODE. 00001391 00001392 MOVE '3000-TRANLIST-CSR-SQLCODE' TO WS-PARA-NAME 00001393 D DISPLAY WS-PARA-NAME 00001394 00001395 IF SQLCODE NOT EQUAL 0 00001396 MOVE SQLCODE TO WS-UNCONV-SQLCODE 00001397 MOVE WS-UNCONV-SQLCODE TO WS-CONV-SQLCODE 00001398 MOVE WS-ERROR-CODE TO ERROR-CODE 00001399 MOVE 'ERROR OCCURED WHILE OPENING TRANLIST CURSOR' 00001402 TO WS-ERROR-MSG 00001410 MOVE WS-ERROR-MSG-COM TO WS-ERROR-MSG-FIN 00001431 MOVE WS-ERROR-MSG-FIN TO ERROR-MSG 00001432 D DISPLAY 'WS-MODULE-ID' WS-MODULE-ID 00001437 D DISPLAY 'WS-ERROR-CODE' WS-ERROR-CODE 00001438 D DISPLAY 'WS-ERROR-MSG' WS-ERROR-MSG-FIN 00001439 END-IF. 00001440 . 00001450 3000-EXIT. 00001460 EXIT. 00001470 

Earlier Replies | Replies 1 to 2 of 2 | Later Replies

To respond to a discussion, you must first logon.

If you are not registered, please register yourself to become a member of the SiteExperts.community.