Embedded SQL

DB2 SQL CURSOR : POSITIONED UPDATE & DELETE


When processing a CURSOR, DB2 SQL give option to update or delete data in base table based on current position of CURSOR in 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));


id name processedFlag
1 SUMIT N
2 AMIT Y
3 JOHN N
4 ROHIT Y
1 SUMIT N

You need to write some processing logic for each record of this table which 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

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

    Result table for this Cursor

    id name processedFlag
    1 SUMIT N
    3 JOHN N
    1 SUMIT N
  2. 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 word fine if 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 id and name are not unique then it may update duplicate records which are not processed yet. Like in this case (id = 1 and name = SUMIT) is not unique so when 1st record of result table is processed it will also update 3rd record without processing it.

Solution 2: Using POSITIONED UPDATE

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

    “FOR UPDATE” tells system that this CURSOR can be use to update base table (EXAMPLETABLE).By default “FOR UPDATE” allow you to update any column of table, It also give option to define specific column 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

    id name processedFlag
    1 SUMIT N
    3 JOHN N
    1 SUMIT N

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

  2. 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
    

    Now WHERE clause of UPDATE statement have “CURRENT OF @C1”. This “CURRENT OF @C1” tell system to update EXAMPLETABLE based on current position of the CURSOR @C1.

    Now when CURSOR read 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.

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

DELETE FROM EXAMPLETABLE WHERE CURRENT OF @C1 
Restrictions

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

CURSOR’s main select statement(Outer most 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)[Still looking into UNION ALL]
  5. must not contain DISTINCT
  6. must not contain ORDER BY or FOR READ ONLY clause

You can check this link for more details.


IBM i developer.

View Comments
There are currently no comments.