DB2 SQL CURSOR : POSITIONED UPDATE & DELETE

When processing a CURSOR, DB2 SQL gives the option to update or delete data in the base table based on CURSOR's current position in the result table. This is called POSITIONED UPDATE or POSITIONED DELETE.

Let say there is a table EXAMPLETABLE like

CREATE TABLE EXAMPLETABLE (id numeric, name char(10), processedFlag char(1));

You need to write some processing logic for each record of this table that is not processed yet(means processedFlag <> ‘Y’), and when processing for a record is completed, processedFlag should be updated to ‘Y’.

Solution 1: Without using POSITIONED UPDATE

DECLARE CURSOR

DECLARE @C1 CURSOR for 
  (Select id,name from EXAMPLETABLE where processedFlag <>'Y')

FETCH and Process Data

FETCH @C1 into :localID, :localName
   DoW(SQLCODE = 0)
        ---- PROCESSING LOGIC------    

        UPDATE EXAMPLETABLE set processedFlag ='Y'
              WHERE id =localID and  name = localName

     FETCH @C1 into :localID, :localName
   EndDO

This will work fine if the EXAMPLETABLE table has unique records for id and name, and you have to fetch all the columns of the row, which make that row unique to use in WHERE clause of UPDATE statement.

If the id and name are not unique, it may update duplicate records that are not processed yet. Like in this case (id = 1 and name = SUMIT) is not unique, so when 1st record of the result table is processed, it will also update 3rd record without processing it.

Solution 2: Using POSITIONED UPDATE

DECLARE CURSOR

DECLARE @C1 CURSOR for 
  (Select id,name from EXAMPLETABLE where processedFlag <>'Y')
   FOR UPDATE

“FOR UPDATE” tells the system that this CURSOR can be used to update the base table (EXAMPLETABLE).

By default, “FOR UPDATE” allow you to update any column of the table. It also gives the option to define a specific column(s) allowed to update using “FOR UPDATE of column1,column2, …. “

DECLARE @C1 CURSOR for 
  (Select id,name from EXAMPLETABLE where processedFlag <>'Y')
   FOR UPDATE OF processedFlag ;

Result table for this Cursor

In this case, only the processedFlag column of EXAMPLETABLE can be updated using POSITIONED UPDATE. No other column is allowed to update.

FETCH and Process Data

FETCH @C1 into :localID, :localName
   DoW(SQLCODE = 0)
        ---- PROCESSING LOGIC------    

        UPDATE EXAMPLETABLE set processedFlag ='Y'
              WHERE CURRENT OF @C1 

     FETCH @C1 into :localID, :localName
   EndDO

1. Now, the WHERE clause of the UPDATE statement has “CURRENT OF @C1”. This “CURRENT OF @C1” tells the system to update EXAMPLETABLE based on the current position of the CURSOR @C1.
2. When CURSOR reads 1st record, it will update only 1st record as CURSOR is pointed on 1st record, 3rd record will not be updated even id and name are duplicate.

POSITIONED DELETE

In the same way, we can delete records from the table using POSITIONED DELETE

DELETE FROM EXAMPLETABLE WHERE CURRENT OF @C1

Restrictions

There are few cases where you cannot use positioned update/delete.

CURSOR’s main select statement(Outermost also called OUTER FULLSELECT):

1. must contain only one base table means no SQL JOINS are allowed
2. must not contain GROUP BY clause or HAVING clause
3. must not contain column functions in the select list
4. must not contain SET operations (UNION, EXCEPT, or INTERSECT)
5. must not contain DISTINCT
6. must not contain ORDER BY or FOR READ ONLY clause

You can check this link for more details.