Understanding the Informix Error Code 83793 'table has pending in-place alter'

If you try to add a table to CDC Replication replication that has been altered at some point in the past, you may receive an CDC Replication error that indicates the table has pending in-place alters. (Informix® Error Code 83793 ('table has pending in-place alter').

When the database engine determines that it is possible to manage existing stored data using the new presentation caused by an ALTER statement, it will allow the change without modifying any existing physical data row structures for existing pages on disk.

This is referred to as an in-place alter. While the database engine can manage this situation without problem it poses a problem for replication.

In order to understand the problem posed by this, it is important to understand that the database log may contain a before image - the data row that was in the database before a change happened, and an after image - the data row that is in the database after a change happened.

An insert log record will contain only an after image, a delete operation will contain only a before image, and an update operation will contain a before image and an after image.

If the physical structure of a row was an older format and was read from the log that way, it would differ from the current structure expected by the CDC Replication replication engine and a problem would result.

For that reason, when an attempt is made to add an Informix table to an CDC Replication subscription and set its mode to Mirroring, the fact that it has a pending in-place alter is detected and an error condition results.

To resolve this, it is necessary to force the physical changes to be made to the table's data pages.

This is done by means of what is sometimes called a dummy UPDATE:

UPDATE <table> SET column = column;

Where <table> specifies the table name.

Note that for some types of ALTERs where the system cannot guarantee that existing data can be managed in the new representation, the database engine will perform a slow ALTER. So not every ALTER will result in this error when changing a table mapping's replication method back to Mirroring following a DDL change.