Monitoring lock waits is a great way of measuring query performance slowdown.
Lock waits are a normal part query processing but when the lock waits start taking longer than normal , it’s a sign of trouble.
According to the DB2 LUW documentation a lock wait is defined as “A lock wait occurs when one transaction (composed of one or more SQL
statements) tries to acquire a lock whose mode conflicts with a lock held by another transaction”
Common symptoms of lock waits taking longer than normal are:
- Applications are not completing tasks
- SQL query performance slowdown
- Lock escalations. A small amount is OK, but excessive counts are an issue
The ideal is to monitor continuously : Lock wait, Lock timeout and deadlock locking
To report on lock wait chains
Db2pd –locks waits –alldbs
Use this query with joins on views sysibmadm.snapappl_info which return information about applications from an application snapshot and sysibmadm.snapappl which reports on cumulative counts and the latest SQL statement executed . This query reports on useful wait stats details
db2 "SELECT ai.appl_name AS app_name , \ ai.primary_auth_id AS auth_id , ap.agent_id AS app_handle,\ ap.lock_waits AS lock_waits, ap.lock_wait_time / 1000 AS Total_Wait_S, \ (ap.lock_wait_time / ap.lock_waits ) AS Avg_Wait_ms \ FROM sysibmadm.snapappl_info ai, sysibmadm.snapappl ap \ WHERE ai.agent_id = ap.agent_id AND ap.lock_waits > 0"