IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 cursor update


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jan 02, 2008 11:07 am
Reply with quote

Hi,

I have a small query.

Q. Consider a high transaction density environment i.e. several users simultaneously updating data.
The requirement is to select some data; then update it after its validation.

SELECT Col1
FROM Table
WHERE Col2 = :given;

Perform validation on retrieved data: Col1.

UPDATE Table
SET Col1 = :value
WHERE Col2 = :given;

What could go wrong with the above 2 SQLs executed sequentially in a program?

And how to correct it using a cursor?
HINT: FOR UPDATE OF, WHERE CURRENT OF

That's all I have. Please help me out regarding this.

Thanks,
Sharad Srivastava
Back to top
View user's profile Send private message
ahalyah

New User


Joined: 13 Dec 2007
Posts: 25
Location: india

PostPosted: Wed Jan 02, 2008 2:20 pm
Reply with quote

Hi,
if you use UPDATE OF clause then u can get the exclusive lock over the column and you can update it. By this time no othe person can update it untill you issue the commit.
Please correct me if i am wrong.
Back to top
View user's profile Send private message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jan 02, 2008 2:39 pm
Reply with quote

Yes!...thank you.

But I am more concerned towards the first part of the query.
that is:

What could go wrong with the above 2 SQLs executed sequentially in a program?

I hope you would understand my requirement.

Thanks,
Sharad Srivastava
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 355
Location: New York

PostPosted: Wed Jan 02, 2008 2:44 pm
Reply with quote

Hi Sharad,

Is this question from any question bank? If no, what happened when you have executed?

If you tell what type of problem you have faced, forum can provide possible solution.
Back to top
View user's profile Send private message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jan 02, 2008 2:50 pm
Reply with quote

Hi,

This question was asked during a unit test and I was not able to anser.
That's why I am concerned about it.

Particularly the first part. (As written above)




Thanks,
Sharad Srivastava
Back to top
View user's profile Send private message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jan 02, 2008 4:08 pm
Reply with quote

Can anyone answer my query??


Thanks,
Sharad Srivastava
Back to top
View user's profile Send private message
ahalyah

New User


Joined: 13 Dec 2007
Posts: 25
Location: india

PostPosted: Wed Jan 02, 2008 5:05 pm
Reply with quote

Hi Sharad,
if you select the column without UPDATE OF clause then there may be chances to take exclusive access from some other during your select and update time. If somebody have taken the exclusive access then until they release it u cant update the column.
If u use it then nobody can take the access unless your process completes.
Back to top
View user's profile Send private message
sharad_shanu

New User


Joined: 03 Oct 2006
Posts: 40

PostPosted: Wed Jan 02, 2008 5:14 pm
Reply with quote

Ok...it seems good.

Thanks to all for help.

Regards,
Sharad Srivastava
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Read a flat file and update DB2 table JCL & VSAM 2
No new posts how to update an ISR appl var from an... TSO/ISPF 8
No new posts DB2 SQL query to read and update data... DB2 12
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts Group comparison/update between two f... DFSORT/ICETOOL 10
Search our Forums:

Back to Top