LUW 9.1 on AIX

Hey all,

I'm trying to track down some lock wait/timeout issues. I'm operating under the following premise:

I have applications that take out some exclusive locks on a table. I then have selects against those tables. The Optimizer is choosing to ignore the index on the predicate of this table and thereby doing a table scan.
This tablescan is blocked by the lock...

Does this sound realistic? If so how can I force the optimizer to use the index (I have no control over the SQL).

Any other tips?

as always - make runstats current - check if reorg needed -
do db2advis to check objects needed - analyze sql
exclusive lock - why - batch or online - long running job ??

Like Guy, I would start with the exclusive lock as well. What do you need this for? We are asking because typically, such constructs are used due to inexperience with database systems and not because there is a real requirement behind it.

Couple of points from me as well

1> resize LOCKLIST and MAXLOCKs (if it is still set as the default).
2> ask your SQL developers to use WITH UR (if possible and the business allows that).
3> Run a Full REORG on the TABLE and the key indexes followed by a RUNSTATs.
4> Check the CLUSTERFACTOR/CLUSTERRATIO of the Indexes to see whether they are good enough (> 80%) so that the queries prefer to use them.
5>use db2advis to see if db2 is suggesting significant percentage gain over using of any particular recommended index.

what makes you think DB2 is using tablescan rather than the index? Have you run explain on the SQL to see if it is using the index or not? Also, if the table is under an exclusive lock then you aren't going to get anywhere by using an index, the table is still locked. You have to go after the process(es) that are locking the table and get them changed to allow transactional processing.

Dave

Yes the EXPLAIN showed a table scan despite an apparently suitable index being in existence. I'm not asking if my scenario is reasonable with TABLE X Locks but with Exclusive ROW level locks preventing a TABLESCAN from being performed.

So my thinking is that if I'm not doing a table scan that I'm far less likely to get into locking contention with the relatively small number of exclusive row locks.

1> Both are set to AUTOMATIC
2> The SQL is automatically generated by an application, changes to the SQL generation is not possible at least in the short-med term
3> I'll do this today
4> db2advis doesn't suggest any additional indexes

Cheers

I am not sure why there is a table scan being done, but try to alter the table to volatile and see if that helps.

The application generates pretty poor SQL at the best of times. I also see things like applications sitting idle for like 10+ mins while still holding IX table locks and X row locks :/

The the poor performing SQL may be because of lockwaits. There are a number of ways to determine that, but for starters turn on the lock monitor at dbm level, and look at lockwait time in db snapshot.

Will try some reorgs, db2advis gives me nothing... the locks are being generated by normal OLTP activity

Holding locks for 10 minutes is not normal OLTP activity.

What is the application that is holding LOCK is doing?

Just because you have an index does not mean it will be used. Based on what you said there could be couple reasons why it is not used.

Care to share the structure of your table?

I believe the application updates some rows in a unch of tables, then it does a select on one of those tables before it does a commit. The select does a tablescan and I THINK fails because a table scan can't complete if there is an exclusive row lock.... am I correct in this assumption?

You need to update your db config to change from infinite lockwait (the default) to some reasonable number appropropriate for OLTP. You may be getting more locktimeouts (-911 reason code 68) but it will alert you to the problems when they occur.

db2 update db cfg for <db-name> using locktimeout 30

You may have disconnect all applications before it takes effect.

While you are at it, you should update your locklist size to avoid automatic lock escalation from row level to table locks:

db2 update db cfg for <db-name> using locklist 4096