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

how to avoid DB2 Deadlock


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 14
Location: New Jersey

PostPosted: Fri Mar 18, 2011 11:42 am
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to avoid duplicating a CICS Web S... CICS 0
No new posts DB2 deadlock DB2 2
No new posts Display ZEDLMSG without Pressing F1 &... CLIST & REXX 8
No new posts -913/-911 Deadlock during UPDATE stat... DB2 5
No new posts Can a select query participate in a d... DB2 1
Search our Forums:

Back to Top