Adaptive Server Enterprise version 12.0 and later allows users to alter existing tables—add non-nullable columns, drop columns, and modify column datatypes.
This section describes how Replication Server supports table changes resulting from the alter table command when the table has no subscriptions.
To support table changes that result from alter
table when subscriptions exist for that table, you need
to alter the table’s replication definition. See “Modifying
replication definitions” on page 305 in the Replication
Server Administration Guide Volume 1 for instructions.
In previous releases, when a replication definition was defined for a table, Replication Server always used the column datatype defined in the warm standby replication definition. Beginning with Replication Server version 12.0, and depending on the situation, Replication Server may or may not use a table’s replication definition.
When you use alter table against a table without replication definitions, Replication Server sends warm standby databases the same information it receives from the primary server. All options of alter table are supported. When you execute alter table at the primary, the command is replicated to the warm standby, and replication to the standby continues—no action is required in the Replication Server.
See the Adaptive Server Enterprise Reference Manual, Volume 2: Commands for alter table syntax and information.
When you issue the alter table command in the active database to add a column with a default value, Adaptive Server creates a constraint with an auto-generated name. When the command is replicated to the standby database, the standby database also creates the same constraint with another, different auto-generated name. When you drop the constraint in the active database, the standby database does not recognize the constraint name and generates a data server interface (DSI) error.
To avoid this, drop the constraint in the active database first. The data server interface (DSI) shuts down automatically. Then drop the constraint created in the standby database and issue the resume dsi skip transaction command.
An alternative workaround is to execute:
alter table table name replace column name default null
This automatically drops the constraints created on both active and standby sites.
When there is no send standby clause associated with any replication definition, Replication Server sends whatever data it receives from the primary table without referring to the replication definitions.
Replication Server uses the original column names and datatypes to send data received from the RepAgent. The replication definition is used only to find the primary key. The primary keys are the union of primary keys in all replication definitions for the table.
If schema changes do not involve dropping all primary key columns in all replication definitions of the table, the scenario is the same as discussed in “No replication definition”. All options of alter table are supported, and no action is required in the Replication Server.
You can alter the replication definition at any point to drop all primary keys in the replication definitions, and add the new primary key columns to the replication definitions before you alter the primary table.
Drop the old primary keys only after all of the old data rows are out of the replication system. Otherwise, the Data Server Interface (DSI) shuts down. If this occurs, see for recovery instructions.
When send standby all columns is associated with a replication definition, Replication Server sends whatever data it receives from the RepAgent using the original column names and datatypes. The replication definition is used only to find the primary key.
If schema changes do not involve dropping all primary key columns in the replication definition with the send standby all columns clause, the scenario is the same as “No replication definition”. All options of alter table are supported, and no action is required in the Replication Server.
You can alter the replication definition at any time to drop all primary keys in the replication definition with the send standby all columns clause, and add the new primary key columns to the replication definition before you alter the primary table.
Drop the old primary keys after all of the old data rows have left the replication system. Otherwise, the Data Server Interface (DSI) shuts down. If this occurs, see “Recovering from inbound queue problems” on page 310 in the Replication Server Administration Guide Volume 1 for recovery instructions.
When there is a send standby replication definition columns clause in the replication definition, the standby will continue to use the replicate table name and column names as well as the datatype defined in the table’s corresponding replication definition.
If you want the replication definition datatype to be used in the standby, always create a replication definition with a send standby replication definition columns clause.
Please note that:
To add or alter columns in the primary database, follow the “Migration procedure” on page 308 in the Replication Server Administration Guide Volume 1.
To drop columns in the primary database, you do not need to alter the replication definition of the table as long as you do not drop all primary key columns.
To drop all primary key columns, alter the replication definition to add new primary key columns before you alter the primary table. You can drop the old primary keys when the old data rows have been removed from the replication system.
