How to troubleshoot DB2 lock waits

28 February,2017 by Jack Vamvas

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)

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to troubleshoot DB2 lock waits

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright