Query for Locks

[John Carroll]

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

[Jan M. Nelken]

Re: Query for Locks

John Carroll wrote:[color=blue]
> 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.
>[/color]

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

[Buck Nuggets]

Re: Query for Locks

"Jan M. Nelken" <Unknown.User@I nvalid.Domain> wrote in message news:<40aa5b10_ 3@news1.prserv. net>...
[color=blue]
> 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?[/color]

Jan, can you restate this - I'm having a hard time parsing it...


buck

[Blair Adamache]

Re: Query for Locks

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:
[color=blue]
> 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
>
>[/color]

[Tomas Hallin]

Re: Query for Locks

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_nam e from
table(snapshot_ lock(<dbname>)) as lock

Documented in SQL ref vol 1 and Monitoring guide.

/T

"John Carroll" <oraclekdd@yaho o.com> wrote in message news:<GDsqc.687 69$UW6.50085@ne wssvr25.news.pr odigy.com>...[color=blue]
> 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[/color]

[Stefan Kraemer]

Re: Query for Locks

Hi John,

you can use the new V8 SQL table functions as well.
In this example I´m 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_DATABA SE( '$SID', $NODE ))
as SNAPSHOT_DATBAS E"


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_TIMEST AMP, COMMIT_SQL_STMT S,
TOT_LOG_USED_TO P, TOTAL_LOG_USED, TOTAL_LOG_AVAIL ABLE, substr(db_path, 1,40)
FROM TABLE( SNAPSHOT_DATABA SE( '$SID', $NODE )) as SNAPSHOT_DATBAS E"
fi
done
sleep 60
done


Jan M. Nelken schrieb:[color=blue]
> John Carroll wrote:
>[color=green]
>> 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.
>>[/color]
>
> 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[/color]

[Stefan Kraemer]

Re: Query for Locks

I´ve 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

[color=blue]
> Hi John,
>
> you can use the new V8 SQL table functions as well.
> In this example I´m 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_DATABA SE( '$SID', $NODE ))
> as SNAPSHOT_DATBAS E"
>
>
> 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_TIMEST AMP, COMMIT_SQL_STMT S,
> TOT_LOG_USED_TO P, TOTAL_LOG_USED, TOTAL_LOG_AVAIL ABLE, substr(db_path, 1,40)
> FROM TABLE( SNAPSHOT_DATABA SE( '$SID', $NODE )) as SNAPSHOT_DATBAS E"
> fi
> done
> sleep 60
> done
>
>
> Jan M. Nelken schrieb:
>[color=green]
>> John Carroll wrote:
>>[color=darkred]
>>> 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.
>>>[/color]
>>
>> 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[/color][/color]