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.
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:
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:
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.
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:
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: