counting long transactions
The poster wanted an instance-wide count of long transactions (to tune logical log count vs. LTXHWM), noting the stat only appears in session profiles (syssesprof), not onstat -p/sysprof. Answers: no such instance counter exists, but long transactions always log a message and fire an alarm, so you can count them with "select count(*) from sysmaster:sysonlinelog where line matches 'Long transaction'" (works as long as online.log isn't truncated). On v11 the alarms are recorded in sysadmin:ph_alert (alert_object_name=22), with retention tunable via the ALERT HISTORY RETENTION threshold; otherwise modify ALARMPROGRAM (class 22) to log occurrences yourself, or poll running transactions. The poster (on v10) accepted the online.log approach.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
There some place to count how much long transactions occur during the live of
the instance?
I'm looking for this information and found anything...
This information have only on sessions profile (syssesprof) not in instance
profile (onstat -p / sysprof).
The idea behind this... identify how much long transaction occur to review the
amount of logical logs X Long Trans water marks.
I couldn't find it. You can count them by checking the online.log. A long
transaction always triggers an event (alarmprogram.sh) and writes a message
in the online.log. It would be easier to SELECT from somewhere but I don't
think it's available.
Regards.
On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Hi,
>
> There some place to count how much long transactions occur during the live
> of
> the instance?
> I'm looking for this information and found anything...
> This information have only on sessions profile (syssesprof) not in instance
> profile (onstat -p / sysprof).
> The idea behind this... identify how much long transaction occur to review
> the
> amount of logical logs X Long Trans water marks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0016367faaa909d03e0485d8bd42
select count(*) from sysmaster:sysonlinelog where line matches "Longtransaction";
That should do it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 5, 2010 at 9:24 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> I couldn't find it. You can count them by checking the online.log. A long
> transaction always triggers an event (alarmprogram.sh) and writes a message
> in the online.log. It would be easier to SELECT from somewhere but I don't
> think it's available.
>
> Regards.
>
> On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> cesar_inacio_martins@yahoo.com.br> wrote:
>
> > Hi,
> >
> > There some place to count how much long transactions occur during the
> live
> > of
> > the instance?
> > I'm looking for this information and found anything...
> > This information have only on sessions profile (syssesprof) not in
> instance
> > profile (onstat -p / sysprof).
> > The idea behind this... identify how much long transaction occur to
> review
> > the
> > amount of logical logs X Long Trans water marks.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016367faaa909d03e0485d8bd42
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e0a6e9fd3c640485d8da4d
I was convinced that sysonlinelog only had the last N lines... I was wrong,
so as long as you don't clear the file it should be a good option.
Thanks and regards.
On Wed, May 5, 2010 at 2:32 PM, Art Kagel <art.kagel@gmail.com> wrote:
> select count(*) from sysmaster:sysonlinelog where line matches "Long> transaction";
>
> That should do it.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Wed, May 5, 2010 at 9:24 AM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > I couldn't find it. You can count them by checking the online.log. A long
> > transaction always triggers an event (alarmprogram.sh) and writes a
> message
> > in the online.log. It would be easier to SELECT from somewhere but I
> don't
> > think it's available.
> >
> > Regards.
> >
> > On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> > cesar_inacio_martins@yahoo.com.br> wrote:
> >
> > > Hi,
> > >
> > > There some place to count how much long transactions occur during the
> > live
> > > of
> > > the instance?
> > > I'm looking for this information and found anything...
> > > This information have only on sessions profile (syssesprof) not in
> > instance
> > > profile (onstat -p / sysprof).
> > > The idea behind this... identify how much long transaction occur to
> > review
> > > the
> > > amount of logical logs X Long Trans water marks.
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --0016367faaa909d03e0485d8bd42
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636e0a6e9fd3c640485d8da4d
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001485f1ea6ae3fbc20485d92e81
Sorry , I forgot to say... The environment is v10 ..
Well, probably I will need get to online.log and with luck get considerable
history on them...
thanks.
Cesar
--- Em qua, 5/5/10, Fernando Nunes <domusonline@gmail.com> escreveu:
De: Fernando Nunes <domusonline@gmail.com>
Assunto: Re: counting long transactions [20004]
Para: ids@iiug.org
Data: Quarta-feira, 5 de Maio de 2010, 10:55
I was convinced that sysonlinelog only had the last N lines... I was wrong,
so as long as you don't clear the file it should be a good option.
Thanks and regards.
On Wed, May 5, 2010 at 2:32 PM, Art Kagel <art.kagel@gmail.com> wrote:
> select count(*) from sysmaster:sysonlinelog where line matches "Long> transaction";
>
> That should do it.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Wed, May 5, 2010 at 9:24 AM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > I couldn't find it. You can count them by checking the online.log. A long
> > transaction always triggers an event (alarmprogram.sh) and writes a
> message
> > in the online.log. It would be easier to SELECT from somewhere but I
> don't
> > think it's available.
> >
> > Regards.
> >
> > On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> > cesar_inacio_martins@yahoo.com.br> wrote:
> >
> > > Hi,
> > >
> > > There some place to count how much long transactions occur during the
> > live
> > > of
> > > the instance?
> > > I'm looking for this information and found anything...
> > > This information have only on sessions profile (syssesprof) not in
> > instance
> > > profile (onstat -p / sysprof).
> > > The idea behind this... identify how much long transaction occur to
> > review
> > > the
> > > amount of logical logs X Long Trans water marks.
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --0016367faaa909d03e0485d8bd42
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636e0a6e9fd3c640485d8da4d
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001485f1ea6ae3fbc20485d92e81
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Cesar,
You can approach this in other ways:
- Periodically check the running transactions and verify the difference
between start log and current log
- Periodically check the running transactions and if they're getting x% near
LTXHWM generate an alarm...
This will not give you a view of the past. But if run during some weeks will
give you plenty of details on the current workload.
Regards.
On Wed, May 5, 2010 at 3:06 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Sorry , I forgot to say... The environment is v10 ..
> Well, probably I will need get to online.log and with luck get considerable
> history on them...
>
> thanks.
> Cesar
> --- Em qua, 5/5/10, Fernando Nunes <domusonline@gmail.com> escreveu:
>
> De: Fernando Nunes <domusonline@gmail.com>
> Assunto: Re: counting long transactions [20004]
> Para: ids@iiug.org
> Data: Quarta-feira, 5 de Maio de 2010, 10:55
>
> I was convinced that sysonlinelog only had the last N lines... I was wrong,
> so as long as you don't clear the file it should be a good option.
> Thanks and regards.
>
> On Wed, May 5, 2010 at 2:32 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > select count(*) from sysmaster:sysonlinelog where line matches "Long> > transaction";
> >
> > That should do it.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KS
> > www.iiug.org/conf
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Wed, May 5, 2010 at 9:24 AM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > I couldn't find it. You can count them by checking the online.log. A
> long
> > > transaction always triggers an event (alarmprogram.sh) and writes a
> > message
> > > in the online.log. It would be easier to SELECT from somewhere but I
> > don't
> > > think it's available.
> > >
> > > Regards.
> > >
> > > On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> > > cesar_inacio_martins@yahoo.com.br> wrote:
> > >
> > > > Hi,
> > > >
> > > > There some place to count how much long transactions occur during the
> > > live
> > > > of
> > > > the instance?
> > > > I'm looking for this information and found anything...
> > > > This information have only on sessions profile (syssesprof) not in
> > > instance
> > > > profile (onstat -p / sysprof).
> > > > The idea behind this... identify how much long transaction occur to
> > > review
> > > > the
> > > > amount of logical logs X Long Trans water marks.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --0016367faaa909d03e0485d8bd42
> > >
> > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001636e0a6e9fd3c640485d8da4d
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001485f1ea6ae3fbc20485d92e81
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001636833536e2f5650485d98a36
Hi Fernando,
Thanks for your answers.
I need to take this like a snapshot... just like onstat -p, unfortunately I
can't stay monitoring the environment...
Thanks again.
César
--- Em qua, 5/5/10, Fernando Nunes <domusonline@gmail.com> escreveu:
De: Fernando Nunes <domusonline@gmail.com>
Assunto: Re: counting long transactions [20006]
Para: ids@iiug.org
Data: Quarta-feira, 5 de Maio de 2010, 11:21
Cesar,
You can approach this in other ways:
- Periodically check the running transactions and verify the difference
between start log and current log
- Periodically check the running transactions and if they're getting x% near
LTXHWM generate an alarm...
This will not give you a view of the past. But if run during some weeks will
give you plenty of details on the current workload.
Regards.
On Wed, May 5, 2010 at 3:06 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Sorry , I forgot to say... The environment is v10 ..
> Well, probably I will need get to online.log and with luck get considerable
> history on them...
>
> thanks.
> Cesar
> --- Em qua, 5/5/10, Fernando Nunes <domusonline@gmail.com> escreveu:
>
> De: Fernando Nunes <domusonline@gmail.com>
> Assunto: Re: counting long transactions [20004]
> Para: ids@iiug.org
> Data: Quarta-feira, 5 de Maio de 2010, 10:55
>
> I was convinced that sysonlinelog only had the last N lines... I was wrong,
> so as long as you don't clear the file it should be a good option.
> Thanks and regards.
>
> On Wed, May 5, 2010 at 2:32 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > select count(*) from sysmaster:sysonlinelog where line matches "Long> > transaction";
> >
> > That should do it.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KS
> > www.iiug.org/conf
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Wed, May 5, 2010 at 9:24 AM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > I couldn't find it. You can count them by checking the online.log. A
> long
> > > transaction always triggers an event (alarmprogram.sh) and writes a
> > message
> > > in the online.log. It would be easier to SELECT from somewhere but I
> > don't
> > > think it's available.
> > >
> > > Regards.
> > >
> > > On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> > > cesar_inacio_martins@yahoo.com.br> wrote:
> > >
> > > > Hi,
> > > >
> > > > There some place to count how much long transactions occur during the
> > > live
> > > > of
> > > > the instance?
> > > > I'm looking for this information and found anything...
> > > > This information have only on sessions profile (syssesprof) not in
> > > instance
> > > > profile (onstat -p / sysprof).
> > > > The idea behind this... identify how much long transaction occur to
> > > review
> > > > the
> > > > amount of logical logs X Long Trans water marks.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --0016367faaa909d03e0485d8bd42
> > >
> > >
> > >
> > >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001636e0a6e9fd3c640485d8da4d
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001485f1ea6ae3fbc20485d92e81
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001636833536e2f5650485d98a36
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Please note each time the alarm is trigger in version 11 an entry is
placed in the sysadmin:ph_alert table. You can just
run a simple query to find the information
SELECT * FROMsysadmin:ph_alert
WHERE alert_object_type="ALARM"
AND alert_object_name=22 --- ALARM id for long transaction
By default this table will contain 15 days of historical information,
If you want a longer history of events then update the following to change
to 30 days.
UPDATE ph_threshold SET (value) = ("30 0:00:00" )
WHERE name = "ALERT HISTORY RETENTION";
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 05/05/2010 06:24:13 AM:
> [image removed]
>
> Re: counting long transactions [20002]
>
> Fernando Nunes
>
> to:
>
> ids
>
> 05/05/2010 06:24 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> I couldn't find it. You can count them by checking the online.log. A long
> transaction always triggers an event (alarmprogram.sh) and writes a
message
> in the online.log. It would be easier to SELECT from somewhere but I
don't
> think it's available.
>
> Regards.
>
> On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> cesar_inacio_martins@yahoo.com.br> wrote:
>
> > Hi,
> >
> > There some place to count how much long transactions occur during the
live
> > of
> > the instance?
> > I'm looking for this information and found anything...
> > This information have only on sessions profile (syssesprof) not
ininstance
> > profile (onstat -p / sysprof).
> > The idea behind this... identify how much long transaction occur to
review
> > the
> > amount of logical logs X Long Trans water marks.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016367faaa909d03e0485d8bd42
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
If you are not on version 11, you could also modify your ALARMPROGRAM script
to trap for a long TX. The code you add could then grab the current date, some
onstat info and write / append it to a file you specify. The 'SEVERITY' is 3
and the 'CLASS' is 22 I believe.
Bob
----- Original Message -----
From: "John Miller iii" <miller3@us.ibm.com>
To: ids@iiug.org
Sent: Wednesday, May 5, 2010 1:16:48 PM GMT -05:00 US/Canada Eastern
Subject: Re: counting long transactions [20019]
Please note each time the alarm is trigger in version 11 an entry is
placed in the sysadmin:ph_alert table. You can just
run a simple query to find the information
SELECT * FROMsysadmin:ph_alert
WHERE alert_object_type="ALARM"
AND alert_object_name=22 --- ALARM id for long transaction
By default this table will contain 15 days of historical information,
If you want a longer history of events then update the following to change
to 30 days.
UPDATE ph_threshold SET (value) = ("30 0:00:00" )
WHERE name = "ALERT HISTORY RETENTION";
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 05/05/2010 06:24:13 AM:
> [image removed]
>
> Re: counting long transactions [20002]
>
> Fernando Nunes
>
> to:
>
> ids
>
> 05/05/2010 06:24 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> I couldn't find it. You can count them by checking the online.log. A long
> transaction always triggers an event (alarmprogram.sh) and writes a
message
> in the online.log. It would be easier to SELECT from somewhere but I
don't
> think it's available.
>
> Regards.
>
> On Wed, May 5, 2010 at 1:37 PM, Cesar Inacio Martins <
> cesar_inacio_martins@yahoo.com.br> wrote:
>
> > Hi,
> >
> > There some place to count how much long transactions occur during the
live
> > of
> > the instance?
> > I'm looking for this information and found anything...
> > This information have only on sessions profile (syssesprof) not
ininstance
> > profile (onstat -p / sysprof).
> > The idea behind this... identify how much long transaction occur to
review
> > the
> > amount of logical logs X Long Trans water marks.
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --0016367faaa909d03e0485d8bd42
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.