Hi, About week a ago in our production one of the custom application hanged for couple minutes in point where application logs indicate there was stored procedure call (procedure makes select to few tables), we didn't find any other clear to this except that some tables in DB could have been locked and no commit/rollback was issued for those couple minutes (those tables are currently only updated by hand, apps. make just selects).

We reproduced the problem in test environment by locking one of those tables and the application logs showed again the same symptoms. Everybody that can access the DB directly ofcourse says that they weren't doing anything during that time...

In order to solve this in future (and perhaps even now ?), what do you think, what monitors (or logs) we should enabled in DB ? (for example one that would log all established DB connections when started/ended, one that would log locks that have been held for over 5 seconds, etc.)

Perhaps some of those information are already logged / monitored by default, but DB2 logs and monitoring is quite new area to me still

Thanks for your help

I would recommend you:

- tune your database: runstats, locklist, locktimeout, etc.,
- tune the application: commits, isolation levels, etc.
- investigate the locking behaviour.

For the investigation part, ensure your lock monitor is on: DFT_MON_LOCK, and do either:

db2 get snapshot for locks on sample
db2pd -locks -db sample

this should help you understand what is the root cause of the Locking (Note: also understand if it is a dead-locking or lock-timeout problem).

It also helps to have the statement monitor on - DFT_MON_STMT to be able to capture the SQL holding the locks.

Very good article on locking:

http://www-128.ibm.com/developerwork...m-0509schuetz/

Graham Martin
http://www-306.ibm.com/software/data/db2/migration/