Maintaining table schema

Replication Server stores information about table schema in a table’s replication definition. During alter table operations, new or modified data rows may reach Replication Server while old data rows are still being processed farther down the data stream. When replication definitions exist for a table, the discrepancies between the columns in the table and the columns in the replication definition may cause Replication Server threads (executor, distributor, or DSI) to shutdown.

See the section on alter table in the Adaptive Server Enterprise Reference Manual (version 12.0 or later) for syntax and details on how alter table works in Adaptive Server.

Figure 9-2: Replicate Table Schema Inconsistency

Figure 9-2 illustrates replicate table schema inconsistency. As shown in this figure, the client application retrieves the data from the primary database through the primary data server. It contains a publishers table, which initially has four columns and four rows. The four column table transactions in the data stream goes to the Replication Server. The Replication Server contains the replication definition for the four column table and is subscribed by the replicate data server. The alter table command adds 2 columns to the publishers table, making it six column table transactions in the data stream as the new data rows. The replication definition cannot describe the old data rows and the new data rows at the same time, which causes discrepancies between the primary and replicate database.

As Figure 9-2 illustrates, because the replication definition cannot describe the old data rows and the new data rows (Figure 9-2, A & B) at the same time, discrepancies between a replication definition and its corresponding table may cause Replication Server to behave incorrectly; that is, not able to read or write data rows to inbound and outbound queues.

For example:

If Replication Server receives the following from RepAgent:

old_datarow1
old_datarow2
...
alter table command
new_datarow1
new_datarow2
...

both the old and new data rows need to be replicated with the correct number of columns and the correct column datatype.If alter table drops columns, old data rows still have these columns replicated while the new data rows do not.

If alter table adds new columns, the new columns need to be included only in the new data rows. Figure 9-2 illustrates that when you add new columns to the Publishers table using alter table (“B”), because the new rows are not in the table’s replication definition, the new rows will not be replicated, causing you to lose data.

If alter table alters a column datatype, both the old and new data rows need to be replicated in their own column types. When you modify primary table column datatypes, there is also a period of time when the replication definition column datatype does not match the table column datatype. This mismatch may cause problems in Replication Server when column datatypes are used.