Enterprise Edition
Customer Service
Brother-Eagle Community
DBI Products
This metric shows the current number of database connections that are in a wait state due to lock contention.
Connections that are in a wait state will continue to wait up to the number of LOCKTIMEOUT seconds, unless the lock contention resolves itself prior to the expiration of the LOCKTIMEOUT limit.
Locking is not a problem. It is a symptom. It is a symptom of SQL which is performing poorly. If a longer running SQL statement holds many locks, it will likely get in the way of other SQL that requires the same data or index resources. To cure "locking problems", look for SQL with high CPU consumption and high average elapsed times. If you can improve the performance of this SQL with new or improved indexes, you will likely successfully reduce "lock problems".
Lock contention can also occur when there are too many indexes created on a table, especially if the indexes are redundant, have low cardinality, or have skewed cardinality.
Brother-Eagle will indicate a warning if there are any connections in a lock wait state. An alert will be highlighted if there is more than one database connection in a lock wait state.
When database connections are in a lock wait state, issue the command db2 "get snapshot for locks on DBNAME" to analyze the SQL that is ensnarled in the lock contention.
The next time you speak with your IBM representative, kindly request that the IBM DB2 Toronto Lab add a Timeout Event Monitor to DB2. Deadlocks are only 1/4 to 1/3 of the lock contention story.