RE: Monitor Informix Longtx
To give you the percent of the syslogs which are used between now
and the start of the longest transaction you can use this query.
SELECT (
(
SELECT SUM(size-used)
FROM syslogs
WHERE uniqid >= (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
)
+
(
SELECT SUM(size)
FROM syslogs
WHERE uniqid < (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0
)
)
)
/ (select sum(size) from syslogs) *100
FROM systables
WHERE tabid=1
Using this percent and your Transaction rollback percentage you
can figure out how far it is to the long transaction
Note: Query stolen from a Mark D. Stock posting and modified
(I wish I was good enough with the sysmaster to come up with this
alone)
Will Rice
>===== Original Message From jphilips@my-deja.com =====
>I'm trying to find a way how to monitor when a
>long transaction is almost going to happen on an
>informix instance. We are using the IDS version
>7.24. Can this be done using onstat utilities or
>via an sql query on the sysmaster database.
>
>With an onstat -l you can see the logical logs
>which have been backed up but you can't see if
>there is a long running transaction busy with
>onstat -l. Logical logs will recycle but are>than reaching the LTXHWM or LTXEHWM values.
>
>Can somebody help me to find a way of monitoring
>this? Thanks.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
------------------------------------------------------------
This e-mail has been sent to you courtesy of OperaMail, as a free service from
Opera Software, makers of the award-winning Web Browser, Opera. Visit us at
http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail
account is waiting at: http://www.operamail.com/
------------------------------------------------------------
Did not work for me (7.31uc2, HP10.20). But it was fascinating enough to tinker with.
Modified like below, it worked fine on my instance.
SELECT
(select cf_effective f
from sysconfig
where cf_name = 'LTXHWM') LTXHWM,
((
select sum(size)
FROM syslogs
WHERE uniqid >= (
SELECT MIN(tx_loguniq)
FROM systrans
WHERE tx_loguniq > 0)
and uniqid <= (
SELECT uniqid
FROM syslogs
WHERE is_current = 1)
)
/ (select sum(size) from syslogs) *100) CURRENT_USED
FROM systables
WHERE tabname = 'systables';
Rudy
William Rice wrote:
> To give you the percent of the syslogs which are used between now
> and the start of the longest transaction you can use this query.
>
> SELECT (
> (
> SELECT SUM(size-used)
> FROM syslogs
> WHERE uniqid >= (
> SELECT MIN(tx_loguniq)
> FROM systrans
> WHERE tx_loguniq > 0
> )
> )
> +
> (
> SELECT SUM(size)
> FROM syslogs
> WHERE uniqid < (
> SELECT MIN(tx_loguniq)
> FROM systrans
> WHERE tx_loguniq > 0
> )
> )
> )
> / (select sum(size) from syslogs) *100
> FROM systables
> WHERE tabid=1
>
> Will Rice