MVSFORUMS.com

Rama_Prayaga · Beginner Joined: 20 May 2003 Posts: 45 Topics: 15

Hi,

I am doing a mass update in my Application program.I am worried if there any deadlock sitution then my program will abend.Is there any way I can ignore these record which are under deadlock situation and update the rest. will DB2 will handle this case by itself.

kolusu · Posted: Thu Jun 30, 2005 5:26 am Post subject:

Rama_Prayaga,

You can check for the sqlcodes -911 and -913 and re-try/skip your update.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Rama_Prayaga · Beginner Joined: 20 May 2003 Posts: 45 Topics: 15

Hi Kolusu,

Thanks for your reply.I have handled that condition.But My query is somewhat different.Actually I wanted to know if my other records are updated?

schintala · Posted: Thu Jun 30, 2005 7:40 am Post subject:

Did you have commit in your application? I guess it shouldn't be problem as long as you have commit logic after 'n' number of records updated.

kolusu · Posted: Thu Jun 30, 2005 7:42 am Post subject:

Rama_prayaga,

It depends on your pgm logic. Let us say you read a file and perform a mass update and you commit for every 10 records.

read first record
mass update 1
update successful

read next record
mass update 2
update successful

read next record
mass update 3
update failed

Since you haven't committed the prior 2 updates both of them will get rolled back provided that your program abended. If you handled -911 and -913 in the program , and you have issued a commit after 10 records ,then you will only miss the updates which failed due to deadlock.

PS: I would actually open a cursor and fetch each record and perform an update using WHERE CURRENT OF

Check this link which explains about using positioned UPDATE statements

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/2.2.1.6?SHELF=&DT=20010710165542&CASE=

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Rama_Prayaga · Beginner Joined: 20 May 2003 Posts: 45 Topics: 15

HI Kolusu,schintala

Below is the SQL query

kolusu · Posted: Tue Jul 05, 2005 7:38 am Post subject:

Rama_Prayaga,

Just check the sqlcode after the execuetion of the above statement and just continue with pgm no matter what the sqlcode is. By doing so you will skip the updates for this particular key. A sqlcode of -911 or -913 or -904 is an indication of unavailable resource.

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Rama_Prayaga · Beginner Joined: 20 May 2003 Posts: 45 Topics: 15

Hi Kolusu,

executed the above stament and receive SQLCODE -911 , But it did not run as I exepected it to be.I mean "none of the records" got updated.I shall show by example my requierement.

Manas Biswal · Posted: Wed Jul 20, 2005 2:22 pm Post subject:

Rama_Prayaga,

What you are asking cannot be done with a stand-alone update query. Whenever, a deadlock is detected, it will rollback all the updates done on that query. You can either define a "for update of" cursor for the update statement and do the updates programatically or lock the table exclusively by using the "LOCK TABLE" SQL statement before running your update statement.

Regards,
Manas
Hint - Check the DB2MSTR for the process that your update statement is contending it. That way you can perhaps work around the timings of that other process against which you are deadlocking.
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

Rama_Prayaga · Beginner Joined: 20 May 2003 Posts: 45 Topics: 15

Hi Manas,

Thank you.I shall do it by using a Cursor.

Regards,
Rama Krishnna Prayaga