Long Transactions
One of the most critical things that can happen to an Informix instance is a long transaction. A long transaction occurs when a transaction begins in one logfile and spans multiple logfiles. If it gets to a certain point of logfile usage, the transaction begins to roll back. If there is not enough logfile space available for the rollback, the Informix engine cannot make the database consistent. If this happens, the database needs to be reinitialized and restored from an archive. If you get into this problem, pray that your support contract is up to date because Informix Support may be the only source of help. Otherwise, you'll have to restore from an archive.
This is almost the worst thing that can happen to the database. You'll certainly lose a lot of time. You'll probably lose data. Before Version 4.1, the situation was much easier to get into. The later versions have several tb/onconfig parameters that are meant to avoid the problem, but their defaults are not conservative enough until later versions of 7.XX, and need to be changed. In versions later than 4.1, the configuration file has two critical parameters.
LTXHWM: This is the long transaction high water mark. The Informix default value is 80 percent. When the total number of filled logs reaches this amount, any transactions that started in the oldest logfile are marked as long transactions and are forced to begin to roll back. This parameter's default is 80 percent, and it is often way too high. Set it at about 60 or 70 percent in most cases. You need to give the transaction enough room to roll back as well as give other jobs room to log.
LTXEHWM: This is the long transaction exclusive high water mark. The Informix default value is 90 percent. When the total number of filled logs reaches this amount, all processes except long transactions that are rolling back are prevented from logging to the logfiles. These processes are effectively frozen. When you reach this stage of fullness, all of the logspace is needed to assure that the long transactions can roll back. It's defaulted at 90 percent and should probably be 75-80 percent.
The long transaction is the culprit that fills up the logfiles. There are several types of long transactions, most of them easily understood and one that is less obvious. Let's start with the one of the more obvious ones.
When you are working within a database that supports logging, either explicitly or by virtue of being a MODE ANSI database, the BEGIN WORK statement may precede a series of SQL statements. The BEGIN WORK event is logged in the logfile. This event occurs in a MODE ANSI database when the first SQL statement outside of a transaction is executed and for other logged databases when the BEGIN WORK statement is executed. Until the corresponding COMMIT WORK statement is entered into a logfile, the engine will consider that all of the logfiles beginning with the one holding the BEGIN WORK and ending with the currently active logfile are needed in case the transaction does not complete and the engine needs to roll it back The classic long transaction occurs when a user or a program is performing an update, insert, or delete on a large set of rows from a very large table. Each row affected generates a logfile entry. It is possible that one SQL statement on a table with hundreds of thousands of rows can swamp the logfiles, even if nothing else is being logged within the system. This type of situation often occurs when initially loading the database or when loading or unloading large tables.
The less obvious situation is when a user is doing a short transaction and fails to commit work. In this case, the BEGIN WORK statement is logged. The job does very little work, but the COMMIT WORK statement is not issued. Everything else that is logged into the logfiles then constitutes the long transaction. The engine does not care what is being done between the BEGIN WORK and the COMMIT WORK statements. Until it sees a matching COMMIT WORK statement it thinks it's in a long transaction. The dbaccess program executes a hidden BEGIN WORK statement when you first query a logged non-ANSI database. This is the reason that you sometimes get a "Commit or rollback" prompt inside of dbaccess when you have not actually executed a visible BEGIN WORK statement.
Suppose a user issues a BEGIN WORK statement from within isql/dbaccess for a short transaction and neither issues a COMMIT WORK nor exits from the isql or dbaccess program. That person then goes to lunch, or on a long weekend, or maybe on vacation. You've got a long transaction working. It will continue until the user either commits or exits isql/dbaccess. If the user exits isql/dbaccess, the program will prompt for action on the commit before v exits memory. As long as that isql/dbaccess instance is running, it will be looking for the COMMIT WORK statement. The lesson here is to train your users to be aware of their transaction usage.