The only way to guarantee consistency between tables and replication definitions and ensure that replication works correctly, is to use the steps in the following procedure when you want to add or modify columns in a primary table within a replication system.
This procedure is required only when a table has subscriptions,
or when send standby replication definition columns is
used to replicate to a standby database.
Altering a primary table that is part of a replication
system and avoid data inconsistency or Replication Server thread
shutdown:
Stop all primary database activity.
Send an intentional fake “update” command. When this transaction’s results appear at the replicate site, you know that all operations have been completed by the Replication Server.
Backup the primary database. If your primary database is Adaptive Server, use the Transact-SQL dump transaction command to make a copy of the primary database’s transaction log (syslog) and remove the inactive portion.
See the Adaptive Server Enterprise Reference Manual for instructions.
Use alter table to change the primary table schema.
See the alter table command in the Adaptive Server Enterprise Reference Manual (version 12.0 or later) for instructions.
Quiesce the replication system. Make sure that the last update (step 2) has reached the replicate. Verify that you quiesce the RepAgent for Oracle if Oracle is the primary database.
See the “Quiescing a replication system” for instructions.
Use alter replication definition to change the corresponding replication definitions. Verify that the replication definition changes reach all destination RSSDs.
If the alter table changes involve
columns that are used in a subscription or article where clause,
drop the subscription (without purge) or article before you alter
the replication definition. If you use alter table to drop
columns that are not used in a where clause, replication definition changes
are not necessary.
See “Altering replication definitions” for details.
If you dropped subscriptions in step 6, recreate them using create subscription without materialization or define subscription.
See create subscription and define subscription in Chapter 3 of the Replication Server Reference Manual (version 11.5 or later) for instructions.
Change the replicated table schema if necessary.
Resume activity in the primary database.
