LOCKS in DB2

I found it not as easy as SQL Server to manage DB2 servers when it comes to administration.

Here I am briefing on how to find the locks on DB2 and how to configure the configuration manager so that we can capture the locks.

First we need to update the database manager for configuration to change the parameter DFT_MON_LOCK to ON.

UPDATE DATABASE MANAGER CONFIGURATION USING DFT_MON_LOCK ON

Then we need to update the monitor switches so that we can view the locks

UPDATE MONITOR SWITCHES USING LOCK ON
The above command will help you in updating the monitor switch.

Now we have all set so that we can check the locks on particular database.

GET SNAPSHOT FOR LOCKS ON DB_NAME will give us the list of all locks currently on that particular database.

We can kill the process by using the command

FORCE APPLICATION(APPLICATION_HANDLE)
APPLICATION_HANDLE is a unique number which we will get when we check the lock using GET SNAPSHOT FOR LOCKS ON DB_NAME along with the object name causing lock.

FORCE APPLICATION ALL will kill all the process on the particular database where we executing the command.

Here comes a handy script for viewing locks.This is very helpful as it gives a detailed information on application connecting,IP address,Victim etc.

--SCRIPT TO VIEW LOCKS
Select substr(ai.appl_name,1,10) as Application,
substr(ai.primary_auth_id,1,10) as AuthID,
int(ap.locks_held) as "# Locks",
int(ap.lock_escals) as "Escalations",
int(ap.lock_timeouts) as "Lock Timeouts",
int(ap.deadlocks) as "Deadlocks",
int(ap.int_deadlock_rollbacks) as "Dlock Victim",
substr(inbound_comm_address,1,15) as "IP Address"
from sysibmadm.snapappl ap, sysibmadm.snapappl_info ai
where ap.agent_id = ai.agent_id
;