What is the purpose and usage of “WHERE CURRENT OF” clause in a COBOL-DB2 program?

DB2DatabaseBig Data Analytics

The “WHERE CURRENT OF” clause will place the exclusive lock on the row once the UPDATE statement is executed. The “WHERE CURRENT OF” clause will point to the most recently fetched row of the cursor.

We can update the rows in cursor using “WHERE CURRENT OF” in the following way.

  • CURSOR definition.

EXEC SQL
DECLARE ORDER_CUR CURSOR FOR
SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS
WHERE ORDER_DATE = ‘2020-07-28’
END-EXEC
  • OPEN cursor

EXEC SQL
OPEN ORDER_CUR
END-EXEC
  • FETCH cursor and Update row

SET WF-END-CURSOR-N TO TRUE
   PERFORM UNTIL WF-END-CURSOR-Y
   EXEC SQL
   FETCH ORDER_CUR INTO :ORDER-ID, :TRANSACTION-ID
END-EXEC
IF TRANSACTION-ID NOT = SPACES
   EXEC SQL
  UPDATE ORDERS SET IS_PAID = ‘YES’
WHERE CURRENT OF ORDER_CUR
END-EXEC
ELSE
CONTINUE
END-IF
END-PERFORM
raja
Published on 15-Sep-2020 14:08:26
Advertisements