Unanswered: DB connections & locks monitoring in problem solving
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