Monitoring concurrency and locks

You can monitor the use of locks to improve concurrency and prevent problems such as contention, suspensions, timeouts, or deadlocks.

To monitor the use of locks by Db2, use any of the following approaches:

• Always run statistics classes 1, 3, and 4 and accounting classes 1 and 3.
  The statistics reports provide counters for timeouts, deadlocks and suspensions. Statistics class 3 includes IFCID 0172 (deadlocks) and IFCID 0196 (deadlocks). If deadlocks or timeouts are a concern, look at these detail records to investigate the situation during exceptional situations.
• Use the accounting reports.
  The accounting reports show the locking activity under the heading of LOCKING. The other key indication of locking problems are the class 3 suspensions LOCK/LATCH(DB2+IRLM). If locking and latching are increasing the elapsed time of your transactions or batch work, you might want to investigate further.
• Use the statistics trace to monitor the system-wide use of locks, the accounting trace to monitor locks used by a particular application process.
• Use EXPLAIN to monitor the locks required by a particular SQL statement, or all the SQL in a particular plan or package.
• Use performance trace classes 1, 2, 3, 6, and 7 and analyze the SQL and locking trace data.
  You can use IBM® OMEGAMON® for Db2 Performance Expert on z/OS® to generate SQL activity, locking, and record trace reports.