How to troubleshoot DB2 lock waits

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:

1. Applications are not completing tasks
2. SQL query performance slowdown
3. 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"

Read More

Database Tuning for complex sql queries (DBA DB2)

Trace sql statements in DB2 database (DBA DB2)

DB2 Query Tuning – db2expln (DBA DB2)