You may experience problems with some applications such as Sage Line 500 running on Informix Dynamic Server with High Availability Data Replication (HDR). When running period end or maintenance programs that recreate tables after purging some of the contents, the table may not immediately be available afterwards, creating locked tables and causing the program to crash. This article provides a work-around.
The chain of events is as follows:
- A new table is created.
- Rows to be retained are inserted.
- An index is created.
- The next operation fails because the table is locked.
With the default settings, once an index has been created on a primary server, it is sent directly (not via logical logs) afterwards to the HDR secondary server with text appended to the Informix message logs as follows:
If the index is large, this can take some time. The table is meanwhile exclusively locked on both primary and secondary, even though control has already been returned to the calling application. Unless the table is empty, if it tries to use the table right away, even to create a second index, it will most likely fail with:
A solution is available from IDS 11.10 onwards. The following command will activate and set a configuration parameter in the $ONCONFIG file so that new pages are instead passed to the secondary via the logical logs when indexes are created on the primary:
The message log then shows:
With the new setting in force, a table is no longer locked and is immediately available for use after an index has been built. You can check whether it is currently enabled as in the example below:
There are some caveats:
- The documentation advises: “If LOG_INDEX_BUILDS is enabled, logical log file space consumption will increase, depending on the size of the indexes”.
- It will take longer to create an index due to the logging overhead.
- Most importantly, a large index build might fail with “Long transaction aborted” if the database engine decides it has consuming too big a proportion of the total log space available.
Experienced DBAs therefore temporarily disable this parameter during outages for major schema changes with replication stopped.
Note that index page logging is mandatory for the alternative Informix Remote Secondary Server (RSS) replication method.
Provided you have sufficient log space, enabling index page logging can prevent unexpected failures and locked tables during normal running when applications use newly indexed tables with HDR in operation.
The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.