-501 db2 error -important

Nithya.U.M · New User Joined: 11 Nov 2008 Posts: 21 Location: India

Hi ,
I am getting a -501 error -> cursor not open on fetch. When the program is run on debug mode this error comes in the second fetch of the cursor in a loop. No explicit roll back , commit or close cursor is performed after the first fetch and declare, open , fetch and close cursor statements are given in proper manner. I am unable to understand why this error occurred.Is there any other reason for this error ? Please help .

Regards,
Nithya

sushanth bobby · Posted: Fri Dec 05, 2008 1:27 pm

Nithya,

It would be very helpfull for other to suggest you, if you post part of your code.

Sushanth

Nithya.U.M · New User Joined: 11 Nov 2008 Posts: 21 Location: India

Sushanth,
here is a part of the code which has the cursor flow.

A1000-APPLICATION-PROCESS.
PERFORM A9000-READ-INPUT
PERFORM A9001-DECLARE-FACILITY-CURSOR
PERFORM A9006-DECLARE-SL-CMPNT-DTL
PERFORM A9008-FETCH-SL-CMPNT-CURSOR
PERFORM A9002-PROCESS-FACILITIES
UNTIL WS-IN-FILE-STATUS-EOF
PERFORM A9009-WRITE-TOTALS-FILE
PERFORM A9010-CLOSE-FACILITY-CURSOR
PERFORM A9011-CLOSE-SLCMPNT-CURSOR
PERFORM A9500-WRAP-UP.
***************************************************
A9002-PROCESS-FACILITIES.

PERFORM A9003-FETCH-FACILITY-CURSOR.
IF WS-FIRST-FLAG = 1
MOVE KY-FACIL-NO OF CU20TB09 TO WS-MAX-FACIL-NO
COMPUTE WS-FIRST-FLAG = WS-FIRST-FLAG + 1
END-IF
MOVE KY-FACIL-NO OF CU20TB09 TO WS-TAB-KY-FACIL-NO.
MOVE KY-PREM-NO OF CU20TB09 TO WS-TAB-KY-PREM-NO.
MOVE KY-BA OF CU20TB09 TO WS-TAB-KY-BA.
MOVE KY-GRID-NO-RFMS OF CU20TB09 TO WS-TAB-KY-GRID-NO-RFMS
WS-INT-KY-GRID-NO-RFMS.
and many more moves and logic included in this para.

PERFORM A9001-DECLARE-FACILITY-CURSOR .

A9001-DECLARE-FACILITY-CURSOR.
EXEC SQL
DECLARE FACILITY CURSOR FOR
SELECT
KY_FACIL_NO,
KY_PREM_NO,
KY_BA,
KY_GRID_NO_RFMS,
AD_LN,
AD_LN_2,
KY_CUST_NO(many more fields)
FROM
SL_FACILITIES
ORDER BY KY_FACIL_NO
END-EXEC

EXEC SQL
OPEN FACILITY
END-EXEC

A9003-FETCH-FACILITY-CURSOR.
EXEC SQL
FETCH FACILITY
INTO
:CU20TB09.KY-FACIL-NO,
:CU20TB09.KY-PREM-NO,
:CU20TB09.KY-BA,
:CU20TB09.KY-GRID-NO-RFMS,
:CU20TB09.AD-LN,
:CU20TB09.AD-LN-2 and many more fields
A9010-CLOSE-FACILITY-CURSOR.
EXEC SQL
CLOSE FACILITY
END-EXEC.

currently i am getting this error only while second fetch of the facility cursor inside the process-para.
hope this will make the situation clear. Please help in resolving this issue.

Gnanas N · Posted: Fri Dec 05, 2008 2:21 pm

Please check SQL RETURN CODE after each and every database call, ie, OPEN, FETCH, CLOSE cursors here.

Nithya.U.M · New User Joined: 11 Nov 2008 Posts: 21 Location: India

Gnanas,
I have debugged the entire code line by line and i am getting sqlcode as zero for first fetch of facility cursor but during second fetch it is -501 .

Thanks and regards,
Nithya

sushanth bobby · Posted: Fri Dec 05, 2008 2:55 pm

Nithya,

Are you doing any COMMIT or ROLLBACK operations.
Which might also close the CURSORS.

Sushanth

Nithya.U.M · New User Joined: 11 Nov 2008 Posts: 21 Location: India

Thanks everyone .
the system was performing an autocommit by updating a table during a read of another file in the same para. :-). This issue is resolved and thanks once again for the replies.