Be Specific When Updating With SQL Cursors

Published: January 7, 2009

by Ted Holt

To update a table (physical file) through an SQL cursor, use the FOR UPDATE OF clause in your UPDATE command. (I have written about FOR UPDATE OF before.) When you update through an SQL cursor, avoid two mistakes that can cause a performance problem.

First, don't forget to include FOR UPDATE OF. The SQL preprocessor does not require you to include this clause, and will not generate a message to warn you of its omission. If you do not specify FOR UPDATE OF, SQL assumes that you want to update all columns (fields).

Second, list only the columns that you will update. If you list columns that you don't update, SQL will not notice the inconsistency.

The possible performance problem occurs when SQL thinks you might update a column that is used in the WHERE clause. SQL will consider using access paths to speed up the update, but it will not use an access path over an updated column. When you tell SQL which columns will be updated, SQL knows that it can use access paths over other columns.

Here's an example of a good cursor declaration. Notice the FOR UPDATE OF clause in the cursor declaration.

D CustData      e ds                  extname(CustMaster)
D ChangeCode      s              1p 0

/free                              
     exec sql                       
        declare Customer cursor for 
           select * from CustMaster
            where state = 'TX'      
              for update of chgcod;

     exec sql                       
        open Customer;              
        // insert code to check for open error
                                    
     dow '1';                       
        exec sql                    
           fetch Customer into :CustData;
        if sqlstt = '02000';        
           leave;                   
        endif;                      
        // insert code to check for fetch error

        // insert calcs to calculate new Change Code here
        // eval whatever ...
        // call whatever ...
        // etc.

        // ChangeCode now has a new value for the customer.
        
     exec sql                       
           update CustMaster set chgcod = :ChangeCode
              where current of Customer;
     enddo;                         
     exec sql                       
        close Customer;             
        // insert code to check for close error
     return;                        

SQL knows that the program will not update STATE, so it can use an index built over STATE in order to select the rows (records) for Texas customers. Without FOR UPDATE OF, it will ignore indexes and read the entire table.

RELATED STORY

Updating through an SQL Cursor

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot