Potential Long transactions
Mohit (IDS 10) wanted a sysmaster query to spot transactions heading toward long-transaction status, joining systxptab, sysrstcb and sysscblst and comparing (loguniq-logbeg) against sh_maxlogs, but didn't know how to read LTXHWM rather than hard-coding a threshold. Jack Parker pointed out ONCONFIG values are available via sysmaster:sysconfig (e.g. where cf_name like 'LTX%'), and Mohit reposted the query using cf_original for LTXHWM. He also questioned whether sysrstcb's upf_logspuse really is a percentage, since it returned values far above 100; no answer to that or further review of the query is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Version: IDS 10
I have written below query to find out those transactions that could
be potential long transactions. Assuming 50/60 is high water mark
values:
select s.txid,r.owner, r.sid, scb.pid
from systxptab s, sysrstcb r, sysscblst scb
where (loguniq-logbeg)/(select sh_maxlogs
from sysshmvals) > 40/100 { catch when it'sover 40% }
and logbeg > 0 { I saw lot of transactions owned by informix that
have logbeg as 0 }
and s.owner = r.address
and r.sid = scb.sid;
Does this query look correct ? I didn't know how to get LTXHWM value
from sysmaster to hard coded the values. Any suggestion or
improvement ?
Haven't looked at this thoroughly yet, but thought that I'd point out that
you can get $ONCONFIG values (like LTXHWM) directly out of sysmaster.
select * from sysmaster:sysconfig where cf_name like 'LTX%'
cheers
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Mohit
Sent: Monday, February 25, 2008 8:07 PM
To: informix-list@iiug.org
Subject: Potential Long transactions
Version: IDS 10
I have written below query to find out those transactions that could
be potential long transactions. Assuming 50/60 is high water mark
values:
select s.txid,r.owner, r.sid, scb.pid
from systxptab s, sysrstcb r, sysscblst scb
where (loguniq-logbeg)/(select sh_maxlogs
from sysshmvals) > 40/100 { catch when it'sover 40% }
and logbeg > 0 { I saw lot of transactions owned by informix that
have logbeg as 0 }
and s.owner = r.address
and r.sid = scb.sid;
Does this query look correct ? I didn't know how to get LTXHWM value
from sysmaster to hard coded the values. Any suggestion or
improvement ?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Feb 26, 4:54 am, "Jack Parker" <jack.park...@verizon.net> wrote:
> Haven't looked at this thoroughly yet, but thought that I'd point out that
> you can get $ONCONFIG values (like LTXHWM) directly out of sysmaster.
>
> select * from sysmaster:sysconfig where cf_name like 'LTX%'>
> cheers
> j.
>
> Sane ego te vocavi. Forsitan capedictum tuum desit.
>
>
>
> -----Original Message-----
> From:informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Mohit
> Sent: Monday, February 25, 2008 8:07 PM
> To:informix-l...@iiug.org
> Subject: Potential Long transactions
>
> Version: IDS 10
>
> I have written below query to find out those transactions that could
> be potential long transactions. Assuming 50/60 is high water mark
> values:
>
> select s.txid,r.owner, r.sid, scb.pid
> from systxptab s, sysrstcb r, sysscblst scb
> where (loguniq-logbeg)/(select sh_maxlogs
> from sysshmvals) > 40/100 { catch when it's> over 40% }
> and logbeg > 0 { I saw lot of transactions owned byinformixthat
> have logbeg as 0 }
> and s.owner = r.address
> and r.sid = scb.sid;
>
> Does this query look correct ? I didn't know how to get LTXHWM value
> from sysmaster to hard coded the values. Any suggestion or
> improvement ?
> _______________________________________________Informix-list mailing listInformix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text -
>
> - Show quoted text -
select s.txid, hex(s.owner), r.sid, scb.pid, s.logbeg, s.loguniq
from systxptab s, sysrstcb r, sysscblst scb
where (loguniq-logbeg)/(select sh_maxlogs
from sysshmvals) > (select cf_original*.75
from sysconfig
where cf_name ='LTXHWM')/100
and logbeg > 0 { I saw lot of transactions owned byinformix that
has logbeg as 0 }
and s.owner = r.address
and r.sid = scb.sid;
I also thought of using upf_logspuse from sysrsctb. In sysmaster it
says it's a % of logs space used, but when I ran a select the number
came out to be much much larger than 100 so I wasn't sure of this
field. I than put together above query assumins that loguniq(current)
would have to be much higher and may be that's the way to get the
potential long transactions. Any suggestions or improvements ?