Monitor locks in DB2

Posted by David Yahalom under DB2 LUW

Since locking in DB2 can be a real problem if not managed correctly, it is very important to keep a close eye on row and table locks.

I know of two easy ways to monitor locks.

The easiest one is simply to execute the following query from the DB2 CLI:

SELECT SUBSTR(appl_name, 1,15) as APPLICATION_WAITING,
SMALLINT(agent_id) as USER_WAITING_ID,
SUBSTR(authid,1,15) AS USER_WAITING,
SMALLINT(agent_id_holding_lk) AS USER_HOLDING_ID,
lock_mode AS HOLDING,
lock_object_type AS TYPE,
lock_mode_requested AS REQUESTED_LOCK_MODE
FROM SYSIBMADM.LOCKWAITS

Then use this to drill down to a specific lock for more details:

SELECT appl_status,
SUBSTR(PRIMARY_AUTH_ID,1,10) AS USER_HOLDING_LOCK,
SUBSTR(appl_name, 1,15) AS APP_HOLDING_LOCK,
SUBSTR(client_nname,1,20) AS CLIENT_HOLDING_LOCK
FROM SYSIBMADM.SNAPAPPL_INFO
WHERE AGENT_ID = <>>

Another way to monitor locks which will result in more detail displayed is:

1) Allow DB2 to monitor locks in the database:

db2 update monitor switches using LOCK on

2) Run the following command which will output the monitoring statistics to a text file of your choice:

db2 get snapshot for locks on database_name > /tmp/locks.txt

3) Keep in mind that there is a certain amount of overhead involved while using the monitor locks option, this is why I always turn the locking monitor off when it is not needed.

db2 reset monitor all