Getting SQLCODE -508 when updating the last record in table?

babu_hi · New User Joined: 11 Apr 2006 Posts: 93

In my program i am using two cursor and updating the same table based on conditions.Both cursor declarations having FOR UPDATE OF and WHERE CURRENT OF CURSOR in Update statement para.

Now i am fetching the records one by one and updating simulteniously.But at the time of Last record(End of Table),the update statement throws SQLCODE -508. Please tell me how to resolve this Problem?

Please see my code:

EXEC SQL DECLARE CNTL_TABLE_CSR1 CURSOR WITH HOLD FOR
SELECT CNTL_TBL_NME,
CNTL_DTE,
CNTL_FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG = 'A'
FOR UPDATE OF CNTL_FLAG
END-EXEC.

EXEC SQL
OPEN CNTL_TABLE_CSR1
END-EXEC.

IF SQLCODE NOT = +0
MOVE SQLCODE TO WS-DB2-CODE
DISPLAY ' ******************************************'
DISPLAY ' * ERROR ON OPEN CURSOR1 .'
DISPLAY ' * DB2 STATUS WAS ', WS-DB2-CODE
DISPLAY '******************************************'
MOVE '2' TO WS-ERROR-FLAG
MOVE +026 TO WS-RETURN-CODE
GO TO 2999-EXIT
END-IF.

PERFORM UNTIL END-OF-TABLE

EXEC SQL
FETCH CNTL_TABLE_CSR1
INTO :CNTL-TBL-NME,
:CNTL-DTE,
:CNTL-FLAG
END-EXEC

EVALUATE SQLCODE
WHEN +0
ADD +1 TO WS-CURSOR1-REC-COUNT
CONTINUE
WHEN +100
SET END-OF-TABLE TO TRUE
WHEN OTHER
DISPLAY 'ERROR IN FETCH'
MOVE +016 TO WS-RETURN-CODE
END-EVALUATE

MOVE 'H' TO WS-CNTL-FLAG

EXEC SQL
UPDATE ARW_CNTL_TABLE
SET CNTL_FLAG = :WS-CNTL-FLAG
WHERE CURRENT OF CNTL_TABLE_CSR1
END-EXEC

IF SQLCODE = +0
ADD +1 TO WS-UPDATE-CSR1-COUNT
END-IF

IF SQLCODE NOT = +0
MOVE SQLCODE TO WS-DB2-CODE
DISPLAY ' ******************************************'
DISPLAY '*** ERROR UPDATING ARW_CNTL_TABLE**********'
DISPLAY ' * DB2 STATUS WAS ', WS-DB2-CODE
DISPLAY ' * DB2 STATUS WAS ', WS-DB2-CODE
DISPLAY '******************************************'
MOVE '2' TO WS-ERROR-FLAG
MOVE +30 TO WS-RETURN-CODE
GO TO 4000-ROLLBACK
END-IF
END-PERFORM.

EXEC SQL
CLOSE CNTL_TABLE_CSR1
END-EXEC.

IF SQLCODE NOT = +0
DISPLAY '*** ERROR CLOSING THE CURSOR ***'
DISPLAY '*** FOR CNTL_TABLE_CSR1 ***'
GO TO 4000-ROLLBACK
END-IF.

Please tell me how to resolve this problem?

Gnanas N · Posted: Thu Apr 03, 2008 11:45 am

Have you looked into MVS QW for this?

dbzTHEdinosauer · Posted: Thu Apr 03, 2008 12:22 pm

you have an evaluate to set certain flags after the fetch. when you get a +100 (end of table) you set the flag and then you go ahead and update the table without checking the status of the previous fetch.

ashimer · Posted: Thu Apr 03, 2008 2:27 pm

you need to exit the perform loop when sqlcode =100 after the fetch ...