Long transaction problem!!

Today I had a problem with a Long transaction that blocked production db for 20 minutes, Actually I detect de LTX with the ALARMPROGRAM, but I would like to detect before this appear in order to prevent it, transactions to be candidates for a Long transaction. Does anyone know how can I do that. Thanks in advance. Luis Arturo López Caballero Proyecto ASARE - Modulo Base Mexico D.F. Tel. 01(55)52294400 Ext. 85705

Hi, I think you can't. At least not really. The problem is, that a transaction is detected as being long when the log files become "pretty full". This in turn depends on your setting of $ONCONFIG file parameters LTXHWM and LTXEHWM. Now the filling of logs is usually not only due to that one transaction (that is then determined to be the LTX), but other transactions contribute to log filling just as well. (The case where you have just one transaction filling all the log files is a special case, not considered here.) Since it is impossible to "predict" what existing and future transactions might do and how fast they might fill log files, it is also impossible to predict in advance whether a specific transaction will be a potential LTX candidate ... I recommend you read more details about the above $ONCONFIG parameters in the manuals. It will then become clearer, how to prevent the "complete system blocked" scenario, even though a long transaction might still occur and be aborted. If setting the value for LTXHWM substantially lower gives you too many LTX abortions on your system, you may have to think about more/bigger log files ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions "luis.lopez0...." <luis.lopez02@cfe.gob.mx> Sent by: forum.subscriber@iiug.org 02.04.2003 04:19 To: ids@iiug.org cc: Subject: Long transaction problem!! [839] Today I had a problem with a Long transaction that blocked production db for 20 minutes, Actually I detect de LTX with the ALARMPROGRAM, but I would like to detect before this appear in order to prevent it, transactions to be candidates for a Long transaction. Does anyone know how can I do that. Thanks in advance. Luis Arturo López Caballero Proyecto ASARE - Modulo Base Mexico D.F. Tel. 01(55)52294400 Ext. 85705

--0__=85256CFC004A62348f9e8a93df938690918c85256CFC004A6234 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: quoted-printable Hi Luis, I wrote this script some time ago to do what you are requesting. I've enclosed it. You may use it at your own risk. I disclaim responsibili= ty for anything that may go wrong. Please note that I've been using this = for years and nothing has gone wrong. (See attached file: ltx.db) Before using it, you will need to change some lines at the top of the script to match your environment: Change this to point to a script that sets up your environment to conne= ct to the database, or just substitute the environment variables in it's place: . $HOME/.dbenv_`hostname`.sh Change the email addresses to go to whom you want to be notified: SENDTO=3D"dba1@mail.box 0000000000@mobile.att.net" Change this to indicate at what percentage you would like long transact= ion notifications to start. In SAP, they set LTXEHWM to 80 and LTXHWM to 7= 0. As you know, Informix attempts to abort a transaction when it reaches t= he LTXWHM percentage. I recommend setting the LTXWARN script variable bel= ow this value. LTXWARN=3D50 Once these changes are made, ftp the script to the box with the informi= x instance, then make it an informix executable with the following comman= ds: chown informix:informix ltx.db chmod 700 ltx.db Finally, add this or similar line to your contab file so monitoring can= take place. 0,15,30,45 * * * * /location/of/the/script/ltx.db >> /dev/null Whala, now you can monitor long transactions before they abort. The sc= ript will provide you with detailed information so that you can manually abo= rt (if necessary) long running transactions before they pose harm to your system. Happy days ahead, -Tim = =20 "luis.lopez0... = =20 ." To: ids@iiug.org = =20 <luis.lopez02@c cc: = =20 fe.gob.mx> Subject: Long transaction= problem!! [839] =20 Sent by: = =20 forum.subscribe = =20 r@iiug.org = =20 = =20 = =20 04/01/2003 = =20 09:19 PM = =20 = =20 = =20 Today I had a problem with a Long transaction that blocked production d= b for 20 minutes, Actually I detect de LTX with the ALARMPROGRAM, but I w= ould like to detect before this appear in order to prevent it, transactions = to be candidates for a Long transaction. Does anyone know how can I do that. Thanks in advance. Luis Arturo L=F3pez Caballero Proyecto ASARE - Modulo Base Mexico D.F. Tel. 01(55)52294400 Ext. 85705 = --0__=85256CFC004A62348f9e8a93df938690918c85256CFC004A6234 Content-type: application/octet-stream; name="=?iso-8859-1?Q?ltx.db?=" Content-Disposition: attachment; filename="=?iso-8859-1?Q?ltx.db?=" Content-transfer-encoding: base64 #!/usr/bin/ksh -e # Tested with IDS 7.2x and 7.3x # setup informix environment variables . $HOME/.dbenv_`hostname`.sh SENDTO="dba1@mail.box dba2@mail.box" # send warning messages to them LTXWARN=50 # percentage when to start warning # The database ($INFORMIXDIR/etc/$ONCONFIG) has the following initialization parameters: # LTXHWM - percentage of logs spanned (per tx) when a rollback is enforced # LTXEWHM - percentage of logs spanned (per tx) when an exclusive rollback is enforced # # This script adds has the following initialization parameters: # LTXWARN - percentage of logs spanned (per tx) when a warning should be issued # SENDTO - list of email/pager addresses to send a message when an event occurs # current log and number of logs set `onstat -l|awk ' { if($1=="address") grab=1; else if(grab && ($4~"^[0-9][0-9]*$")) { nlogs=$2; if(substr($3,5,1)=="C") clog=$4; } } END{printf("%d %d",clog,nlogs);}'` CLOG=$1 # current log NLOGS=$2 # number of logs # long transaction high water marks set `onstat -c|grep LTX|awk -v nlogs=$NLOGS -v ltxwarn=$LTXWARN ' { if($1=="LTXHWM") hwm=($2*nlogs)/100; else if($1=="LTXEHWM") ehwm=($2*nlogs)/100; } END{printf("%d %d %d",(ltxwarn*nlogs)/100,hwm,ehwm);}'` WARN=$1 # warning level HWM=$2 # high water mark EHWM=$3 # exclusive high water mark # transactions onstat -x|egrep -v "active|address"|awk -v clog=$CLOG -v nlogs=$NLOGS -v warn=$WARN -v hwm=$HWM -v ehwm=$EHWM ' { if(NF==7 && ($5~"^[0-9][0-9]*$") && $5!=0) # log is a number other than 0 { stat=substr($2,3,1); # B-begin work, C-commit, R-rollback, H-hueristic rollback uthd=$3; # userthread tx=$1; # transaction ID -- correlated with tx number when transaction aborts spanned=clog-$5; # number of logs spanned locks=$4; # number of locks held if(spanned>=ehwm) printf("%s EXCLUSIVE ROLLBACK !!! %s-%s spanned %d logs (%d before engine HALTS) - %d locks\\n", uthd, tx, stat, spanned, nlogs-spanned, locks); else if(spanned>=hwm) printf("%s ROLLBACK! %s-%s spanned %d logs (%d before EXCLUSIVE rollback) - %d locks\\n", uthd, tx, stat, spanned, ehwm-spanned, locks); else if(spanned>=warn && stat=="B") printf("%s NOTE: %s-%s spanned %d logs (%d before rollback) - %d locks\\n", uthd, tx, stat, spanned, hwm-spanned, locks); } }' | # send message to dba and sap while read TX TXMSG do # additional information SID=`onstat -u|grep $TX|awk '{printf("%s",$3);}'` INFO=`onstat -g ses|grep $SID|awk -v sid=$SID -v txmsg="$TXMSG" ' { if($1==sid) { printf("Long Trans: PID %d on %s.. %s\\n", $4, $5, txmsg); break; } }'` for sendto in $SENDTO do echo $INFO | mailx -s "`uname -n` long transaction" $sendto done done --0__=85256CFC004A62348f9e8a93df938690918c85256CFC004A6234--