Time the locks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mohit - 24 Feb 2008 06:24 GMT

Version: IDS 10

I asked this question in earlier post, and after that I tried to look
at CDI archives but didn't get the answer. I want to know if there is
any way to find out which lock is being held over certain period of
time on certain resource. I have below query to find out about the
locks:

select sysdatabases.name database, -- Database Name
syssessions.username, -- User Name
syssessions.hostname, -- Workstation
syslocks.owner sid -- Informix Session ID
syslocks.tabname
from syslocks, sysdatabases , outer syssessions
where syslocks.tabname = "sysdatabases" -- Find locks on
sysdatabases
and syslocks.rowidlk = sysdatabases.rowid -- Join rowid to
database
and syslocks.owner = syssessions.sid -- Session ID to get
user info
order by 1;

Reply to this Message

Mohit - 24 Feb 2008 20:20 GMT

> Version: IDS 10
>
[quoted text clipped - 17 lines]
> user info
> order by 1;

After little bit of work and pieces of information I got, I have this
query. Is this query correct ?

select r.sid,r.address,l.owner,
dbinfo('utc_to_datetime', l.grtime),dbinfo('utc_current')-l.grtime,
s.pid,s.hostname, dbinfo('dbspace', l.partnum),st.tabname, s.progname
from
systxptab t, sysrstcb r,syslcktab l,flags_text f,sysscblst s,
systabnames st
where dbinfo('utc_current')-l.grtime > $SECS_OLD and t.address =
l.owner
and r.address = t.owner and f.tabname = "syslcktab" and l.type =
f.flags
and s.sid = r.sid and r.address = s.address and st.partnum =
l.partnum;

This query looks correct, but when I run, I don't get any rows. If I
remove r.address = s.address then it seems to be working, but I added
r.address = s.address so that I get only specific information about
lock associated with that table.

Reply to this Message

jprenaut@yahoo.com - 25 Feb 2008 17:18 GMT

> > Version: IDS 10
>
[quoted text clipped - 38 lines]
> r.address = s.address so that I get only specific information about
> lock associated with that table.

r.address will never = s.address

r.address is the address of the rstcb, s.address is the address of the
scb, they are two different structures.

I'm not sure what you mean when you say you added it so that you get
only specific information about lock associated with that table...umm
which table? Are you trying to find locks held on a certain table?

Reply to this Message

Mohit - 25 Feb 2008 21:30 GMT

On Feb 25, 9:18 am, jpren...@yahoo.com wrote:

> > > Version: IDS 10
>
[quoted text clipped - 49 lines]
>
> - Show quoted text -

So if I rework above query would I get information about only those
locks that are over certain age:

select r.sid,r.address,l.owner,
dbinfo('utc_to_datetime', l.grtime),dbinfo('utc_current')-l.grtime,
s.pid,s.hostname, dbinfo('dbspace', l.partnum),st.tabname, s.progname
from
systxptab t, sysrstcb r,syslcktab l,flags_text f,sysscblst s,
systabnames st
where dbinfo('utc_current')-l.grtime > $SECS_OLD and t.address =
l.owner
and r.address = t.owner and f.tabname = "syslcktab" and l.type =
f.flags
and s.sid = r.sid and st.partnum =
l.partnum;

Reply to this Message