How to find who locked what tables in 7.13 ?
Vijay Anisetti
Friends,
Is it possible to find the table names locked and by whom using the system
tables, sysmaster database or using any of the utilities of informix? I was
not able to map the session-id(s) (onstat) of a user to a table(s), this
might look like a wild mapping, but I just wanted to know if it is
possible.
Vijay
Mike South
On 4 Feb 1997 05:50:43 GMT, "Vijay Anisetti" <ram...@sprynet.com>
wrote:
The output of onstat -k will give you the partnum of the lock, this
maps directly to locked resource, partnums that end in 0 are tables
and reference the systables table. this is all from memory so look it
up in the Sysadmin manual, but it's there, including how to convert
partnum to a table identfier.
Michael
Mariusz Malogrosz
database sysmaster ;
select syssessions.username,
syslocks.tabname,
count(*)
from syslocks, outer syssessions # for some reason the outer is needed
Version 7.10.UC2 bug ???
where syslocks.owner = syssessions.sid
group by 1,2
The above select displays the username, tablename and the number of locks
held by that
user. Look at the structure of those tables there are other fileds you may
want to use
to narrow down your query or make a utility for lock searching.
The only bad thing is that the reading from syslocks is slow (it is a view
on 5 tables with
sequential scans, I run the set explain).
So if you need speed , onstat -k is your answer. Use the tblsnum (six)
column to link it
to the partnum in systables table of your dbs. You could make an awk script
which takes the $6 ,
links it to systables (or have systables dump to another ascii file) .
More on sysmaster ...
This next select will show you who is connected to which dbs.
I have this in a script called dbwho.
##### start dbwho
dbaccess sysmaster - <<END-OF-TEXT 2>/dev/null | pg
select syssessions.username,
sysdatabases.name dbname,
syssessions.hostname,
systcblst.name service,
syssessions.sid
from sysdatabases,
syslocks,
sysuserthreads,
systcblst,
outer syssessions
where syslocks.dbsname = "sysmaster"
and syslocks.tabname = "sysdatabases"
and sysdatabases.rowid = syslocks.rowidlk
and syslocks.owner = syssessions.sid
and sysuserthreads.us_sid = syssessions.sid
and systcblst.tid = sysuserthreads.us_tid
into temp dbwho ;
select * from dbwho
where username is not null
order by username
END-OF-TEXT
### end of dbwho
Mariusz Malogrosz
Performance Analyst Tecsys Inc. (www.tecsys.com)
mari...@tecsys.com
Vijay Anisetti <ram...@sprynet.com> wrote in article
<01bc125f$4e3f5de0$f0a8aec7@vijay>...
Irwin Goldstein
system
> tables, sysmaster database or using any of the utilities of informix? I
was
> not able to map the session-id(s) (onstat) of a user to a table(s), this
> might look like a wild mapping, but I just wanted to know if it is
> possible.
Check out the "Informix Tips and Information" pages at
http://www.objectsoft.com . You'll find a script there named "onlocks"
which queries the SMI tables in the sysmaster database for a more
user-friendly version of the listing which "onstat -k" provides. Real user
names and table names are displayed rather than hex addresses and you can
filter the list by user name, table name, database name, etc.
HTH
--
Irwin Goldstein
Objective Software Systems, Inc.
http://www.objectsoft.com
Rajeev SRK Nimmagadda
On 4 Feb 1997, Vijay Anisetti wrote:
>
> Friends,
>
> Is it possible to find the table names locked and by whom using the system
> tables, sysmaster database or using any of the utilities of informix? I was
> not able to map the session-id(s) (onstat) of a user to a table(s), this
> might look like a wild mapping, but I just wanted to know if it is
> possible.
>
> Vijay
>
>
try
select * from syslocks;
You will get database name, tablename, rowid, keynum and owner.
owner will be sesid. Lookit up using syssessions which gives the user
name for that ses id.
refer to INFORMIX-online DSA guide page 39-16.
for syslocks
and page 39-23 for syssessions
################################################################
Rajeev Nimmagadda SRK
raj...@jcdcrs4.jobcorps.org # (512)393-7204