Long transactions monitor and Statistics

Hello all, IDS 12.10.FC1WE on Solaris 10 9/10 s10s_u9wos_14a The monitoring we use for Informix in our environment detected that certain session is being rolled back due to occupying more log space than defined in LTXEHWM parameter. So far so good, the engine successfully did an rollback of this session and aborted it: - confirmation for this can be seen in the message log - and also in onstat -x output (no sessions marked as being rolled back) However, the alarm was not released and the monitoring still thinks there is an issue and alarms us again without reason. The select statement used to detected longtx: SELECT syssessions.sid, username, longtxs FROM syssesprof, syssessions WHERE ((syssessions.sid=syssesprof.sid)) Example output: sid username longtxs 287823 appuser 1 As the engine stats are cumulative (please correct me if I am wrong), the only way "to fix" this is as resetting the engine statistics with onstat -z. Then, after I recheck with monitoring, no issues are being detected. But as resetting statistics is not desired fix for this, can anyone advise if I can rewrite the query to detect Long txs in a better way? Thanks in advance! BR, Lyubo --001a1133b2ae1944cc051e9a3003

drop that session ? Cheers Paul > Hello all, > > IDS 12.10.FC1WE on Solaris 10 9/10 s10s_u9wos_14a > > The monitoring we use for Informix in our environment detected that > certain > session is being rolled back due to occupying more log space than defined > in LTXEHWM parameter. So far so good, the engine successfully did an > rollback of this session and aborted it: > - confirmation for this can be seen in the message log > - and also in onstat -x output (no sessions marked as being rolled back) > > However, the alarm was not released and the monitoring still thinks there > is an issue and alarms us again without reason. > > The select statement used to detected longtx: > > SELECT syssessions.sid, username, longtxs FROM syssesprof, syssessions > WHERE ((syssessions.sid=syssesprof.sid))> > Example output: > > sid username longtxs > > 287823 appuser 1 > > As the engine stats are cumulative (please correct me if I am wrong), the > only way "to fix" this is as resetting the engine statistics with onstat > -z. Then, after I recheck with monitoring, no issues are being detected. > > But as resetting statistics is not desired fix for this, can anyone advise > if I can rewrite the query to detect Long txs in a better way? > > Thanks in advance! > > BR, > Lyubo > > --001a1133b2ae1944cc051e9a3003 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

My guess is that you want to look at systxptab table and the column called longtx. I have not tested the below SQL, but it should get you most of the way there. select a.sid, a.username, longtx from sysrstcb b, systxptab c, sysscblst a where c.owner =3D b.address and a.address =3D b.scb John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 08/31/2015 05:01:28 AM: > From: "Lyubomir Grigorov" <lgrigorlu1@gmail.com> > To: ids@iiug.org > Date: 08/31/2015 05:03 AM > Subject: Long transactions monitor and Statistics [35688] > Sent by: ids-bounces@iiug.org > > Hello all, > > IDS 12.10.FC1WE on Solaris 10 9/10 s10s=5Fu9wos=5F14a > > The monitoring we use for Informix in our environment detected that certain > session is being rolled back due to occupying more log space than defined > in LTXEHWM parameter. So far so good, the engine successfully did an > rollback of this session and aborted it: > - confirmation for this can be seen in the message log > - and also in onstat -x output (no sessions marked as being rolled back) > > However, the alarm was not released and the monitoring still thinks there > is an issue and alarms us again without reason. > > The select statement used to detected longtx: > > SELECT syssessions.sid, username, longtxs FROM syssesprof, syssessions > WHERE ((syssessions.sid=3Dsyssesprof.sid))> > Example output: > > sid username longtxs > > 287823 appuser 1 > > As the engine stats are cumulative (please correct me if I am wrong), the > only way "to fix" this is as resetting the engine statistics with onstat > -z. Then, after I recheck with monitoring, no issues are being detected. > > But as resetting statistics is not desired fix for this, can anyone advise > if I can rewrite the query to detect Long txs in a better way? > > Thanks in advance! > > BR, > Lyubo > > --001a1133b2ae1944cc051e9a3003 > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >