Wow, you've got some fun work ahead of you.
The other thing you need to map out is how many threads/applications can be executing these queries. Clearly, the one on the 30 second interval timer is a key one.
The other thing to consider which I didn't even think about, and admit I know even less about (and nothing when it comes to DB2), is that when updating indexed fields, I believe that the index itself is locked. Imagine two queries where query 1 queries column A and updates column B and query 2 queries column B and updates column A (A and B are indexed).Query 1 read-locks index A and performs query.
Query 2 read-locks index B performs query.
Query 1 write-locks index B -- waits for lock.
Query 2 write-locks index A -- waits for lock -- deadlock.I can't say I'm positive that's how it would work, but I think it does. This is very similar to the row-locking I laid out above.
To track down which queries are causing this, you might want to try isolating them. Put two of the likeliest queries into timed threads to run as often as possible and see if they lock up. Then introduce a third one. I know, this isn't easy since you need to pick values that are realistic, but that's where I'd start.
I'm pretty slammed here with porting to Hibernate. If I have some free time later I'll look over those queries a bit more. For now I'd look at the index-locking issue I raised above. I'm surprised DB2 isn't more helpful in this regard, but perhaps there's a different tool or logging level yoou can turn on. In Oracle you can see the query execution plan with all of the locks that it would acquire, IIRC.
Originally posted by Pat Villa:
The SQLCODE is "-911".
Brilliant!