Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Wed Sep 20, 2006 2:25 pm    Post subject: When we use the "for update of clause" for update
Reply with quote

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
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Sep 20, 2006 2:51 pm    Post subject:
Reply with quote

Hi SSR,

This will be useful

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.apsg/bjnqmstr103.htm
Back to top
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Thu Sep 21, 2006 11:30 am    Post subject: for update of
Reply with quote

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
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Thu Sep 21, 2006 12:12 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
SSR
Warnings : 1

New User


Joined: 26 Feb 2006
Posts: 38

PostPosted: Fri Sep 22, 2006 2:27 pm    Post subject: Re: When we use the "for update of clause" for upd
Reply with quote

hi anamikak ,

Thanks for your help.

Regards
Sundar
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us