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 Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm


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