MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Mass update Ignoring deadlock

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Rama_Prayaga
Beginner


Joined: 20 May 2003
Posts: 45
Topics: 15

PostPosted: Thu Jun 30, 2005 5:24 am    Post subject: Mass update Ignoring deadlock Reply with quote

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.
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu Jun 30, 2005 5:26 am    Post subject: Reply with quote

Rama_Prayaga,

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

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Rama_Prayaga
Beginner


Joined: 20 May 2003
Posts: 45
Topics: 15

PostPosted: Thu Jun 30, 2005 6:54 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message Yahoo Messenger
schintala
Beginner


Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA

PostPosted: Thu Jun 30, 2005 7:40 am    Post subject: Reply with quote

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


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu Jun 30, 2005 7:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Rama_Prayaga
Beginner


Joined: 20 May 2003
Posts: 45
Topics: 15

PostPosted: Tue Jul 05, 2005 7:23 am    Post subject: Reply with quote

HI Kolusu,schintala

Below is the SQL query
Code:

EXEC SQL                                                                   
  UPDATE VTXX10A1V                                                         
   SET DELETE_FLAG   = '1'                                                 
      ,DELETE_DATE   = :DCLVTN100A2V.DELETE_DATE                           
      ,DELETE_USER   = 'TN5004'                                             
      ,INSTR_STATUS  = 'S'                                                 
      ,UPD_USER      = 'TN5004'                                             
      ,UPD_TMSTMP    =  CURRENT TIMESTAMP                                   
 WHERE                                                                     
    EXP_DATE < CURRENT DATE                                           
    AND DELETE_FLAG ^= '1';                                                 

I want is know if some rows are locked here , then it can be skipped.Is it possible anyways.

Regards,
Rama krishna Prayaga
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Tue Jul 05, 2005 7:38 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Rama_Prayaga
Beginner


Joined: 20 May 2003
Posts: 45
Topics: 15

PostPosted: Wed Jul 20, 2005 7:02 am    Post subject: Reply with quote

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.


Code:
rowno   EXP_DATE       DELETE_FLAG  DELETE_DATE  DELETE_USER   UPD_USER    UPD_TMSTMP
1           01.01.2004         0
2           01.01.2004         0
3           01.01.2004         0
4           01.01.2004         0
5           01.01.2004         0
6           01.01.2004         0
7           01.01.2004         0
8           01.01.2004         0
9           01.01.2004         0

Now, the above records are satisfying the given condition.Let us assume rowno 1 3 5 6 are in hold by some other program.Then I should ignore those records and update the rest of the ROWS.

Code:
rowno   EXP_DATE       DELETE_FLAG  DELETE_DATE  DELETE_USER   UPD_USER    UPD_TMSTMP
1           01.01.2004         0       
2           01.01.2004         1    01.01.2004   RAMA          RAMA        2005-01-05-10.05.39.682024
3           01.01.2004         0                       
4           01.01.2004         1    01.01.2004   RAMA          RAMA        2005-01-05-10.05.39.682024
5           01.01.2004         0       
6           01.01.2004         0     
7           01.01.2004         1    01.01.2004   RAMA          RAMA        2005-01-05-10.05.39.682024
8           01.01.2004         1    01.01.2004   RAMA          RAMA        2005-01-05-10.05.39.682024
9           01.01.2004         1    01.01.2004   RAMA          RAMA        2005-01-05-10.05.39.682024


I mean I do not mind if the above rowno 1 3 5 6 (which are in hold) are ignored but rest should get updated.My above query which I given is not updating the unhold records.

MY update should be somewhat similar as SELECT WITH UR condition.

Hope I did not confuse with my stuff.

Kindly, help me in this regards,

Regards,
Rama Krishna Prayaga
Kindly , help me in this regrad
Back to top
View user's profile Send private message Yahoo Messenger
Manas Biswal
Intermediate


Joined: 29 Nov 2002
Posts: 382
Topics: 27
Location: Chennai, India

PostPosted: Wed Jul 20, 2005 2:22 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Rama_Prayaga
Beginner


Joined: 20 May 2003
Posts: 45
Topics: 15

PostPosted: Thu Jul 21, 2005 3:19 am    Post subject: Reply with quote

Hi Manas,

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

Regards,
Rama Krishnna Prayaga
Back to top
View user's profile Send private message Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group