Blocking Agent ID for Locks in SNAPSHOT_*

[nielsgron]

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_LOCKWA IT table functions. The problem I am
having is that the SNAPSHOT_LOCKWA IT 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_LOCKWA IT.

thanks
-Niels
select *
FROM TABLE(SNAPSHOT_ LOCK('SAMPLE', 0)) AS S
-------------------------------------------

SNAPSHOT_TIMEST AMP AGENT_ID TABLE_FILE_ID LOCK_OBJECT_TYP E
LOCK_MODE LOCK_STATUS LOCK_OBJECT_NAM E PARTITION_NUMBE R
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('SAMPL E', 0)) AS s
--------------------------------------------------------

SNAPSHOT_TIMEST AMP AGENT_ID SUBSECTION_NUMB ER LOCK_MODE
LOCK_OBJECT_TYP E AGENT_ID_HOLDIN G_LK LOCK_WAIT_START _TIME
LOCK_MODE_REQUE STED PARTITION_NUMBE R LOCK_ESCALLATIO N
TABLE_NAME TABLE_SCHEMA TABLESPACE_NAME APPL_ID_HOLDING _LK

--------------------- ----------- -------------------- ------------
------------------- ---------------------- -----------------------
---------------------- ------------------- -------------------
------------- --------------- ------------------
---------------------

0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]