Investigating Locking Events in Db2

The purpose of this article is to provide guidance on the various ways available for getting basic information about locking events in a Db2 database. Where possible the information gathered allows for targeting a specific table. The information provided by these methods is only for starting investigations into locking issues. Links are provided after each method for additional information.

Conventions

Text in statements that are wrapped in brackets [sample], signifies a variable to be replaced with the value specific to your situation. This article also uses the schema DBA for event monitors. This can be changed as desired. If changed, replace each occurrence of DBA with your chosen schema.

The db2pd Utility

The db2pd utility is a troubleshooting tool that collects information without acquiring any latches or using any engine resources.

To get a list of the details for all current locks use:

$ db2pd -db [databaseName] -wlocks detail

For more information:

https://www.ibm.com/docs/en/db2/11.5?topic=tools-db2pd#c0054595

Table Functions

You can retrieve information about locks using table functions. Information about locks is always available from the database manager. You do not need to enable the collection of this information.

To see a list of applications holding locks on a specific table, use this statement:

SELECT APPLICATION_HANDLE, LOCK_NAME, LOCK_STATUS FROM TABLE (mon_get_locks(CLOB('<table_schema>[schema]</table_schema> <table_name>[table]</table_name>'),-2));

For more information:

https://www.ibm.com/docs/en/db2/11.5?topic=mpf-mon-get-locks-table-function-list-all-locks-in-currently-connected-database

Event Monitors

Pulling information from table functions and the database’s memory sets returns the values of monitor elements at the specific point in time the routine is run. Event monitors allow you to view events historically. Event monitors capture data when an event occurs for later analysis. This means that event monitors need to be created and activated prior to the events occurring.

The following process explains how to create a locking event monitor that writes to unformatted event tables.

Prerequisite Setup

The locking event monitor performs best with a 32K tablespace, temp tablespace, and bufferpool. It is recommended that you create these if they do not already exist. Here are the commands to create them:

CREATE BUFFERPOOL BUFF32K IMMEDIATE 
   SIZE 2500 AUTOMATIC 
   PAGESIZE 32 K;

CREATE LARGE TABLESPACE TAB32K 
   PAGESIZE 32 K 
   BUFFERPOOL BUFF32K 
   DROPPED TABLE RECOVERY ON;

CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS32K 
   PAGESIZE 32 K 
   BUFFERPOOL BUFF32K;

The locking event monitor also requires certain parameters to be enabled for collection of needed details. Below are the parameters with their suggested setting:

MON_LOCKTIMEOUT WITHOUT_HIST
MON_DEADLOCK WITHOUT_HIST
MON_LOCKWAIT NONE
MON_LW_THRESH 5000000
MON_LCK_MSG_LVL 2

Event Monitor Creation

The following command creates the event monitor MY_LOCKS for locking events and writes the details of those events to the unformatted event table DBA.MY_LOCKS.

CREATE EVENT MONITOR MY_LOCKS FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE (TABLE DBA.MY_LOCKS IN TAB32K) AUTOSTART;

To activate the event monitor run:

SET EVENT MONITOR MY_LOCKS STATE = 1;

To confirm that the event monitor is active use the command below (a state of 1 = active):

SELECT SUBSTR(EVMONNAME,1,30) AS EVMONNAME 
  ,EVENT_MON_STATE(EVMONNAME) AS STATE 
FROM SYSCAT.EVENTMONITORS 
WHERE EVMONNAME = 'MY_LOCKS' 
WITH UR;

Parsing the Output

To view the records for locking events, the unformatted data held in the table DBA.MY_LOCKS will need to be converted to a human readable form. To format the data that has been generated in the last 2 hours use:

CALL EVMON_FORMAT_UE_TO_TABLES ('LOCKING', NULL, NULL, NULL, 'DBA', NULL, NULL, -1, 'SELECT * FROM DBA.MY_LOCKS WHERE event_timestamp between CURRENT TIMESTAMP - 2 hours  AND CURRENT TIMESTAMP ORDER BY EVENT_TIMESTAMP');

The formatted data will be stored in several new tables. Each can be queried to learn various details about locking events. The tables created are:

DBA.LOCK_ACTIVITY_VALUES
DBA.LOCK_EVENT
DBA.LOCK_PARTICIPANTS
DBA.LOCK_PARTICIPANT_ACTIVITIES

Reviewing Table Locks

To summarize locking events for a specific table you can run the following query after parsing the output:

SELECT SUBSTR(LP.TABLE_SCHEMA,1,18) AS TABLE_SCHEMA
     , SUBSTR(LP.TABLE_NAME,1,30) AS TABLE_NAME
     , SUBSTR(LE.EVENT_TYPE,1,18) AS LOCK_EVENT
     , APPLICATION_HANDLE AS APP_HANDLE
FROM DBA.LOCK_PARTICIPANTS LP, DBA.LOCK_EVENT LE
WHERE LP.XMLID=LE.XMLID
     AND EVENT_TIMESTAMP BETWEEN CURRENT TIMESTAMP - 2 HOURS
     AND CURRENT TIMESTAMP
     AND LP.TABLE_SCHEMA = '[schema]'
     AND LP.TABLE_NAME = '[table]'
GROUP BY EVENT_TIMESTAMP
     , EVENT_TYPE
     , APPLICATION_HANDLE
     , TABLE_SCHEMA
     , TABLE_NAME
ORDER BY EVENT_TIMESTAMP  
WITH UR;

With both statements, you can adjust the starting and ending timestamps to meet your specific requirements.

For more information:

https://datageek.blog/en/2012/01/23/analyzing-deadlocks-the-new-way/

https://www.ibm.com/docs/en/db2/11.5?topic=monitoring-event-monitors