By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,589 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,589 IT Pros & Developers. It's quick & easy.

Blocking Agent ID for Locks in SNAPSHOT_*

P: n/a
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]

Apr 6 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.