Need to locate locked tables by user
InfoJones asked for a sysmaster query to show which tables were locked, by whom, and the lock type. Jeff Larsen posted a ksh script joining syssessions and syslocks on sid = owner, excluding sysdatabases. Fabio Antunes gave a similar select unique query. InfoJones confirmed it worked. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I need an sql that will query the sysmaster to help identify which tables are locked and by which user. The type of lock would also be nice. or Just give me a heads up on tables that I will need and I can do the rest. InfoJones
Here's a script I wrote:
#!/bin/ksh
#
# dblock - list locks held by user threads
#
dbaccess sysmaster - 2>/dev/null <<EOF
select s.username, s.sid, l.dbsname, l.tabname, l.rowidlk
from syssessions s, syslocks l
where s.sid = l.owner
and l.tabname != "sysdatabases" -- every session holds a lock here
and s.sid != dbinfo('sessionid') -- exclude this session
order by s.username;
EOF
# end script
You may want to drop the rowidlk column and perhaps
add a "unique" or "count(*)" to get just the tables.
Jeff
sbailey@dallas.clarkbardes.com (InfoJones) wrote:
>
> I need an sql that will query the sysmaster to help identify which
>tables are locked and by which user. The type of lock would also be
>nice.
> or
>
>Just give me a heads up on tables that I will need and I can do the
>rest.
>
>InfoJones
>
In article <38b547b2.5053186@www.informix.com>,
sbailey@dallas.clarkbardes.com (InfoJones) wrote:
>
> I need an sql that will query the sysmaster to help identify which
> tables are locked and by which user. The type of lock would also be
> nice.
> or
>
> Just give me a heads up on tables that I will need and I can do the
> rest.
>
> InfoJones
>
>
select unique dbsname,tabname,username,sid,uid,pid,type
from sysmaster@dbserver:syssessions,sysmaster@dbserver:syslocks
where owner=sid
--F'bio Antunes
Fanix Consultoria Ltda.
Brasil - SP
Sent via Deja.com http://www.deja.com/
Before you buy.
Works great thanks
On Thu, 24 Feb 2000 16:10:39 GMT, fabioantunes@my-deja.com wrote:
>In article <38b547b2.5053186@www.informix.com>,
> sbailey@dallas.clarkbardes.com (InfoJones) wrote:
>>
>> I need an sql that will query the sysmaster to help identify which
>> tables are locked and by which user. The type of lock would also be
>> nice.
>> or
>>
>> Just give me a heads up on tables that I will need and I can do the
>> rest.
>>
>> InfoJones
>>
>>
>select unique dbsname,tabname,username,sid,uid,pid,type
>from sysmaster@dbserver:syssessions,sysmaster@dbserver:syslocks
>where owner=sid
>-->F'bio Antunes
>Fanix Consultoria Ltda.
>Brasil - SP
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.