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

Query for Locks

P: n/a
Is there a SQL query that can be run against a database that will give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.

Thank you,
John
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
John Carroll wrote:
Is there a SQL query that can be run against a database that will give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.


Out of curiosity - what locks would you imagine the SQL query showing
all details of any locks havd to aquire to be able to deliver answer?

I would look rather into Snapshot Monitor and

db2 get snapshot for locks on <database_alias>

command.
Jan M. Nelken
Nov 12 '05 #2

P: n/a
"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message news:<40********@news1.prserv.net>...
Out of curiosity - what locks would you imagine the SQL query showing
all details of any locks havd to aquire to be able to deliver answer?


Jan, can you restate this - I'm having a hard time parsing it...
buck
Nov 12 '05 #3

P: n/a
Have a look at the snapshot monitor - it has it's own DB2 manual (System
Monitor and Reference manual). For SQL access, see this function:
SNAPSHOT_LOCK in the SQL Reference.

John Carroll wrote:
Is there a SQL query that can be run against a database that will give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.

Thank you,
John


Nov 12 '05 #4

P: n/a
John,

if you're on DB2 V8, take a look at the snapshot_lock table function.
You may want something along the lines of

select agent_id, lock_mode, lock_object_name from
table(snapshot_lock(<dbname>)) as lock

Documented in SQL ref vol 1 and Monitoring guide.

/T

"John Carroll" <or*******@yahoo.com> wrote in message news:<GD*******************@newssvr25.news.prodigy .com>...
Is there a SQL query that can be run against a database that will give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.

Thank you,
John

Nov 12 '05 #5

P: n/a
Hi John,

you can use the new V8 SQL table functions as well.
In this example Im monitoring logs, but you can select for locks as
well. See the monitoring guide for more details.

Cheers
Stefan

wanna see available fields:
db2 "describe SELECT * FROM TABLE( SNAPSHOT_DATABASE( '$SID', $NODE ))
as SNAPSHOT_DATBASE"
quick log monitoring (multi-partitioned):

while true
do
echo "\n\n ------------------------------------- `date %Y%m%d%H%M%S`"
for NODE in 0 1 2 3 4 5
do
db2 connect to $SID >/dev/null 2>&1
if [ $? -eq 0 ] ; then
db2 "SELECT SNAPSHOT_TIMESTAMP, COMMIT_SQL_STMTS,
TOT_LOG_USED_TOP, TOTAL_LOG_USED, TOTAL_LOG_AVAILABLE, substr(db_path,1,40)
FROM TABLE( SNAPSHOT_DATABASE( '$SID', $NODE )) as SNAPSHOT_DATBASE"
fi
done
sleep 60
done
Jan M. Nelken schrieb:
John Carroll wrote:
Is there a SQL query that can be run against a database that will give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.


Out of curiosity - what locks would you imagine the SQL query showing
all details of any locks havd to aquire to be able to deliver answer?

I would look rather into Snapshot Monitor and

db2 get snapshot for locks on <database_alias>

command.
Jan M. Nelken

Nov 12 '05 #6

P: n/a
Ive seen in your initial question "the owner" ...
For this you need the application snapshot.
There is one monitoring element within, which will show you how many
locks one application is holding.

Stefan

Hi John,

you can use the new V8 SQL table functions as well.
In this example Im monitoring logs, but you can select for locks as
well. See the monitoring guide for more details.

Cheers
Stefan

wanna see available fields:
db2 "describe SELECT * FROM TABLE( SNAPSHOT_DATABASE( '$SID', $NODE ))
as SNAPSHOT_DATBASE"
quick log monitoring (multi-partitioned):

while true
do
echo "\n\n ------------------------------------- `date %Y%m%d%H%M%S`"
for NODE in 0 1 2 3 4 5
do
db2 connect to $SID >/dev/null 2>&1
if [ $? -eq 0 ] ; then
db2 "SELECT SNAPSHOT_TIMESTAMP, COMMIT_SQL_STMTS,
TOT_LOG_USED_TOP, TOTAL_LOG_USED, TOTAL_LOG_AVAILABLE, substr(db_path,1,40)
FROM TABLE( SNAPSHOT_DATABASE( '$SID', $NODE )) as SNAPSHOT_DATBASE"
fi
done
sleep 60
done
Jan M. Nelken schrieb:
John Carroll wrote:
Is there a SQL query that can be run against a database that will
give me
all the details on any locks that are in place at the given time?
I am interested in find the lock type and owner.


Out of curiosity - what locks would you imagine the SQL query showing
all details of any locks havd to aquire to be able to deliver answer?

I would look rather into Snapshot Monitor and

db2 get snapshot for locks on <database_alias>

command.
Jan M. Nelken

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.