DB2 stored procedure

[yulic]

Calling the DB2-Cobol storprocedure from my Java program I had the table unavailability problems and decided to set COMMIT ON RETURN parameter to YES. Resource unavailability problem is gone, but also I've lost my result set:
"[NEON][SCODBCTS DLL]No result set available to fetch from"

Any help is greately appreciated.
Michael.

 

[cdlvj]

Does the Cobol SP close the cursor on the result set?
It needs to leave it open and return?

 

[yulic]

Here is my cursor:
EXEC SQL
DECLARE DISP CURSOR WITH RETURN FOR
SELECT BATCH_NO,
CRE_TMSTMP
FROM MYTABLE
WHERE BATCH_NO = :WS-BATCH-NO
FOR FETCH ONLY
END-EXEC

And here is how I process it:
EXEC SQL
OPEN DISP
END-EXEC

EVALUATE TRUE
WHEN SQL-SUCCESSFUL-EXECUTION
CONTINUE
WHEN OTHER
MOVE SQL-CODE TO WS-DB2-STATUS
DISPLAY 'BAD RETURN ' WS-DB2-STATUS
SET SQL-ERR-FLAGGED TO TRUE
GO TO 1300-EXIT
END-EVALUATE

 

[cdlvj]

That looks right, shouldn't the CONTINUE be a RETURN or EXIT.

What are the contents of the Create Stored Procedure statement?

 

[yulic]

OK, I found the problem - with hold option:

EXEC SQL
DECLARE DISP CURSOR WITH HOLD
WITH RETURN FOR
SELECT BATCH_NO,
CRE_TMSTMP
FROM MYTABLE
WHERE BATCH_NO = :WS-BATCH-NO
FOR FETCH ONLY
WITH UR
END-EXEC

 

[pkganapathi]

YULIC,

The WITH HOLD is needed due to the COMMIT ON RETURN YES clause. Cursors get closed the moment there is a COMMIT unless you specify WITH HOLD.

Unless your application needs it, users generally use COMMIT ON RETURN NO, and let the calling program handle the commit.

Hope that helps.

-PK.Ganapathy
DB2 DBA

Assistant Consultant
Tata Consultancy Services