When we use the "for update of clause" for update

SSR · New User Joined: 26 Feb 2006 Posts: 38

hi,

My requirement is to increase the salary of all the employees (salary is also a part of emp table) in the emp table by 10 %.

Could anyone let me know when we use the "for update of clause" for update instead of normal update using a cursor.

EXEC SQL

declare cursor salary1 for
select salary from emp

End-Exec

EXEC SQL

declare cursor salary1 for
select salary from emp for update of salary

End-Exec

I think both type of cursors can be used for update of the column salary in the Emp table, could anyone let me know the difference and which one should be used.

Regards
SSR

guptae · Posted: Wed Sep 20, 2006 2:51 pm

Hi SSR,

This will be useful

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/bjnqmstr103.htm

SSR · New User Joined: 26 Feb 2006 Posts: 38

Hi guptae,

Thanks for your update, i could understand that there are two kinds of update cursors we have but my basic question is when will we use the Declare cursor query without the update verb and we write the update query like

Exec SQL

Update cursor1 set salary = new-salary;

End-Exec

Here am not using any For update of or for update clause, is this ok

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

whenever your application intends to update a table , it is always a good idea to declare your cursor with update clause instead of an ambiguous cursor( which does not have for update of clause). What then happens when you use cursor with UPDATE clause is that, depending on the locksize parameter levels set in db2 which may be a tablespace, table, page or row an intent exclusive lock is obtained on the above mentioned units. So, once an intent exclsive lock is obtained, your application will be the first one to get the exclusive lock, when the lock escalation happens.Once the exclusive lock is obtained , no other applcaition will be able to modify the data you are modifying until the lock is released.

SSR · New User Joined: 26 Feb 2006 Posts: 38

hi anamikak ,

Thanks for your help.

Regards
Sundar