Infamous Long Transaction problem

Hi everyone. In order to resolve the common problem of a long transaction bringing the database to a halt (in this case IDS 9.4) I have had to shutdown the database hard (IE: onmode -ky) then do a full level 0 backup and then have the transaction restarted from scratch. Other than the obvious solution of preventing this from happening in the first place by having LOTS of logs does anyone know of any other methods to get out of the "long transaction mess" once it has already occured? I don't mean how to prevent it but instead assuming one is already is facing the issue of a long transaction eating up all of the logs...is the only real solution the one mentioned above?

You can setup LTXHWM and LTXEHWM onconfig variables to values like 50 and 60 to prevent the transactions roll back hang the server. or setup the DYNAMIC_LOGS onconfig parameter to Dynamic log allocation to prevents log files from filling and hanging the system during long transaction rollbacks. The only time that this feature becomes active is when the next log file contains an open transaction. You can do it manually as well, runing: 'onparams -a -d (dbspace_name) [-i]'. the '-i' flag insert a new log after the current log. Regards, Vivian Jones President IBM Information Management Awards 2006 - Most Distinguished Achievement Winner - Latin America Movil Colombia: (57-310) 4883890 Oficina Bogotá: (57-1) 3215753 Oficina Caracas: (58-212) 9056436 Email: jones@itconsultings.net Bogota, Medellin, Cali - Colombia Caracas - Venezuela Web Site: www.itconsultings.net IBM Premier Business Partner Informix & DB2 Specialists Business Intelligence Solutions DataStage & QualityStage Specialists -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de WILL LANDSTROM Enviado el: sábado, 10 de mayo de 2008 18:20 Para: ids@iiug.org Asunto: Infamous Long Transaction problem [12073] Hi everyone. In order to resolve the common problem of a long transaction bringing the database to a halt (in this case IDS 9.4) I have had to shutdown the database hard (IE: onmode -ky) then do a full level 0 backup and then have the transaction restarted from scratch. Other than the obvious solution of preventing this from happening in the first place by having LOTS of logs does anyone know of any other methods to get out of the "long transaction mess" once it has already occured? I don't mean how to prevent it but instead assuming one is already is facing the issue of a long transaction eating up all of the logs...is the only real solution the one mentioned above? **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Will, Once you hit a long transaction abort, my suggestion is to wait it out. Rollbacks routinely take 15-25 times the duration of the initial modifications. Efforts to speed the process are almost always futile and will only delay the process. No matter what you do, Informix will ultimately complete the rollback. The only way to stop the rollback from happening would be for down systems to hack it, but this will leave your data inconsistent. Therefore, they really don't like to do it. Here are my recommendations for minimizing the impact of future Long Transaction Aborts. DYNAMIC_LOGS 2 LTXHWM 50 or lower LTXEHWM 100 First, Dynamic_logs are essential to ensuring database integrity in these situations. Make sure they are enabled. Second, I view having 1 thread rollback unexpectedly as an annoyance. Having all data modifications in your instance stopped is when everyone notices and is magnitudes worse. Therefore, I suggest setting high watermarks aimed at preventing the later. Since avoiding LTXEHWM is the aim, set it to it's maximum 100. In order to have LTA's complete prior to hitting the LTXEHWM, they should to be triggered at or before 50% so they have sufficient space to rollback. If you have other threads using significant log space, you may still hit the exclusive mark. Hopefully the duration in exclusive mode will be limited. Hope this helps, Dave Griffen

Also try limiting your developers before they get to production. Meaning if you have test or QA systems they have to run their programs in before implementing in production, set your locks and other things like LTX... Lower in your test/QA systems to condition your programmers to run efficiently in those systems, then they are less likely to endanger your production box. Yes, I know, not foolproof, but possibly helpful. Norma Jean -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE GRIFFEN Sent: Monday, May 12, 2008 2:54 PM To: ids@iiug.org Subject: Re: Infamous Long Transaction problem [12087] Will, Once you hit a long transaction abort, my suggestion is to wait it out. Rollbacks routinely take 15-25 times the duration of the initial modifications. Efforts to speed the process are almost always futile and will only delay the process. No matter what you do, Informix will ultimately complete the rollback. The only way to stop the rollback from happening would be for down systems to hack it, but this will leave your data inconsistent. Therefore, they really don't like to do it. Here are my recommendations for minimizing the impact of future Long Transaction Aborts. DYNAMIC_LOGS 2 LTXHWM 50 or lower LTXEHWM 100 First, Dynamic_logs are essential to ensuring database integrity in these situations. Make sure they are enabled. Second, I view having 1 thread rollback unexpectedly as an annoyance. Having all data modifications in your instance stopped is when everyone notices and is magnitudes worse. Therefore, I suggest setting high watermarks aimed at preventing the later. Since avoiding LTXEHWM is the aim, set it to it's maximum 100. In order to have LTA's complete prior to hitting the LTXEHWM, they should to be triggered at or before 50% so they have sufficient space to rollback. If you have other threads using significant log space, you may still hit the exclusive mark. Hopefully the duration in exclusive mode will be limited. Hope this helps, Dave Griffen ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================