Performing Alter Operations on Replicated Tables

When Enterprise Replication is active and data replication is in progress, you can perform many types of alter operations on replicated tables. Most of the supported alter operations do not require any special steps when performed on replicated tables; some, however, do require special steps for replicated tables.

You can perform the following alter operations on active, replicated tables without performing extra steps:

• Add or drop fragments
• Move a non-fragmented table from one dbspace to another dbspace
• Convert a fragmented table to a non-fragmented table
• Convert a non-fragmented table to a fragmented table
• Convert from one fragmentation strategy to another
• Change an existing fragment expression on an existing dbspace
• Move a fragment expression from one dbspace to another dbspace
• Create a clustered index
• Recluster an existing index
• Add or drop default values and SQL checks
• Add or drop unique, distinct, and foreign keys
• Alter the locking granularity
• Alter the next extent size

You can perform the following alter operations on active, replicated tables, but you must perform extra steps, which are described in following sections:

• Add a column to a replicated table
• Drop a column from a replicated table
• Modify a replicated column
• Attach a fragment to a replicated table

Enterprise Replication uses shadow replicates to manage alter operations on replicated tables without causing any interruption to replication. By using shadow replicates, the replicate participants SELECT clause can be modified seamlessly while replication is active. For example, a new column can be brought into the replicate definition, an existing replicated column can be removed from the replicate definition and the data type or size of a replicated column can be changed without interrupting replication. See Defining Shadow Replicates for more information about shadow replicates.

Before altering a replicated table, ensure that you have sufficient log space allocated for long transactions, a sufficient number of locks available, and sufficient space available for the queue sbspace.

When you issue a command to alter a replicated table, Enterprise Replication places the table in alter mode before performing the alter operation. Alter mode is a state in which only DDL (data-definition language) and SELECT operations are allowed but DML (data-manipulation language) operations are not allowed. After the transaction that initiated the alter operation completes, Enterprise Replication unsets alter mode.

The following restrictions apply when you use alter operations on replicated tables.

• Enterprise Replication must be in an active state, unless you are only adding or dropping check constraints and default values.
• Tables must have a master replicate defined.
• The RENAME TABLE and RENAME COLUMN statements are not supported.
• The DROP TABLE statement is not supported.

For a list of common alter operation problems and how to solve them, see Troubleshooting Tips for Alter Operations.