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
 

 

how to avoid DB2 Deadlock

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

New User


Joined: 13 Feb 2009
Posts: 20
Location: United States of america

PostPosted: Fri Mar 18, 2011 11:04 am    Post subject: how to avoid DB2 Deadlock
Reply with quote

I have a mainframe job which updates the DB2 table. Sometimes users runs query againgst this DB2 table and mainframe job will wait for locks and finally abends due to time out error. Then we ask DBA to purge the user's session and i will restart job.


Is it possible that before mainframe job starts, i can put some check if any user is using table and purge the particular session.
Back to top
View user's profile Send private message

Tushar Sood

New User


Joined: 26 Oct 2008
Posts: 9
Location: New Jersey

PostPosted: Fri Mar 18, 2011 11:42 am    Post subject:
Reply with quote

Hi,

I don't have a solution to your problem, but wanted to share a similar problem happening at our place.

After which we (the users) started using the DB2 queries with "With UR" clause. This prevented the deadlocks.

Though we had a limitation of just using the Select Queries.
Back to top
View user's profile Send private message
Peter Nancollis

New User


Joined: 15 Mar 2011
Posts: 47
Location: UK

PostPosted: Sat Mar 19, 2011 6:33 am    Post subject:
Reply with quote

You dont say what is being used for the query. Some of the options in, say SPUFI, can be quite restrictive in a "sharing" environment - even when the query has completed!
UR will give relief of "dirty reads" are acceptable
Problem with checking first is what happens if something sneaks in between the check and the job ?
If it a critical updater consider LOCKing the table ... but may cause more problems than it solves!
Back to top
View user's profile Send private message
devd

New User


Joined: 19 Mar 2009
Posts: 11
Location: Chennai

PostPosted: Sat Mar 19, 2011 11:37 am    Post subject:
Reply with quote

You need to do specify WITH UR within your SQL query or even you can go for certain changes in your schid using the scheduler so that both jobs aren't being run at same time.
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Mon Mar 21, 2011 2:24 am    Post subject:
Reply with quote

Better ask your DBA abt using UR.......Deadlock problem can be solved or reduced by scheduling the jobs in much organised way.....

We had a similar problem sometime back and we used partitioned table.....so that till todays date data get inserted......last updated date data remains active in one partition and after completion of insertion of data in next partition last date data becomes inactive and new one as active......but this all depends upon architecture of your application
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 Display ZEDLMSG without Pressing F1 &... anandgbe CLIST & REXX 8 Thu Dec 15, 2016 4:20 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts Can a select query participate in a d... apvardhini DB2 1 Fri May 29, 2015 12:39 pm
No new posts Related to Deadlock issue sivareddy123 DB2 2 Fri Jan 30, 2015 3:59 pm
No new posts How to avoid/Omit Addtional qualifer ... cmsmoon TSO/ISPF 2 Thu Nov 27, 2014 6:31 pm


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