Be Specific When Updating With SQL Cursors
January 7, 2009 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
|
