Time the locks
A user on IDS 10 wanted to find locks held longer than a given time, joining sysmaster tables (syslcktab, systxptab, sysrstcb, sysscblst, systabnames, flags_text) and using dbinfo('utc_current') - l.grtime to compute lock age. His query returned no rows. A respondent explained the cause: the join condition r.address = s.address can never match, since rstcb and scb are different structures with different addresses; linking sessions via s.sid = r.sid is enough. The poster reposted the query without that predicate, but no further confirmation or final answer is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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;
On Feb 23, 10:24 pm, Mohit <mohitanch...@gmail.com> wrote:
> 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 --InformixSession 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;
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.
On Feb 24, 2:20 pm, Mohit <mohitanch...@gmail.com> wrote:
> On Feb 23, 10:24 pm, Mohit <mohitanch...@gmail.com> wrote:
>
>
>
> > 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 --InformixSession 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;
>
> 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.
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?
On Feb 25, 9:18 am, jpren...@yahoo.com wrote:
> On Feb 24, 2:20 pm, Mohit <mohitanch...@gmail.com> wrote:
>
>
>
>
>
> > On Feb 23, 10:24 pm, Mohit <mohitanch...@gmail.com> wrote:
>
> > > 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 --InformixSession 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;
>
> > 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,sysrstcbr,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.
>
> 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?- Hide quoted text -
>
> - 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;