Hi,
I am trying to find a way to query for the (lock) blocking connection
on my DB2 server. I am using DB2 for LUW 8.1 and 8.2. I have two
connections, one which has an exclusive lock, and the other which is
being blocked by the connection with the exclusive lock. I want to be
able to query for the locks on the system, the agent id of each
connection and which connection is blocking which. The problem I am
having is finding the blocking connection. I am using the
SNAPSHOT_LOCK and SNAPSHOT_LOCKWAIT table functions. The problem I am
having is that the SNAPSHOT_LOCKWAIT table function is not returning
any values, but that is where the blocking ID column is defined.
Does anyone know how to find the blocking Agent ID of a connection?
Below are the results from a query on SNAPSHOT_LOCK and
SNAPSHOT_LOCKWAIT.
thanks
-Niels
select *
FROM TABLE(SNAPSHOT_LOCK('SAMPLE', 0)) AS S
-------------------------------------------
SNAPSHOT_TIMESTAMP AGENT_ID TABLE_FILE_ID LOCK_OBJECT_TYPE
LOCK_MODE LOCK_STATUS LOCK_OBJECT_NAME PARTITION_NUMBER
LOCK_ESCALATION TABLE_NAME TABLE_SCHEMA TABLESPACE_NAME
--------------------- ----------- ----------------
------------------- ------------ -------------- -------------------
------------------- ------------------ ------------- ---------------
------------------
11/15/2004 4:03:30 PM 261 18 2
3 1 4099 (null)
0 SYSROUTINES SYSIBM SYSCATSPACE
11/15/2004 4:03:30 PM 261 18 2
3 1 4104 (null)
0 SYSROUTINES SYSIBM SYSCATSPACE
11/15/2004 4:03:30 PM 261 18 1
1 1 18 (null)
0 SYSROUTINES SYSIBM SYSCATSPACE
11/15/2004 4:03:30 PM 261 0 9
3 1 0 (null)
0 (null) (null) (null)
11/15/2004 4:03:30 PM 261 0 8
3 1 0 (null)
0 (null) (null) (null)
11/15/2004 4:03:30 PM 260 17 1
2 1 17 (null)
0 CUSTOMER DB2ADMIN USERSPACE1
11/15/2004 4:03:30 PM 260 0 9
3 1 0 (null)
0 (null) (null) (null)
11/15/2004 4:03:30 PM 260 0 8
3 1 0 (null)
0 (null) (null) (null)
11/15/2004 4:03:30 PM 259 17 2
5 1 4 (null)
0 CUSTOMER DB2ADMIN USERSPACE1
11/15/2004 4:03:30 PM 259 17 1
2 1 17 (null)
0 CUSTOMER DB2ADMIN USERSPACE1
11/15/2004 4:03:30 PM 259 0 8
3 1 0 (null)
0 (null) (null) (null)
11 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
select *
FROM TABLE(SNAPSHOT_LOCKWAIT('SAMPLE', 0)) AS s
--------------------------------------------------------
SNAPSHOT_TIMESTAMP AGENT_ID SUBSECTION_NUMBER LOCK_MODE
LOCK_OBJECT_TYPE AGENT_ID_HOLDING_LK LOCK_WAIT_START_TIME
LOCK_MODE_REQUESTED PARTITION_NUMBER LOCK_ESCALLATION
TABLE_NAME TABLE_SCHEMA TABLESPACE_NAME APPL_ID_HOLDING_LK
--------------------- ----------- -------------------- ------------
------------------- ---------------------- -----------------------
---------------------- ------------------- -------------------
------------- --------------- ------------------
---------------------
0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]