Following are examples of how to add or change a column for a replication definition.
To add a char column named zip (for zip code information) to the replication definition publishers:
Alter the replication definition to add the char column:
alter replication definition publishers_rep add zip char(10)
If the column you added to the destination table has a different name than the source column, replace the earlier alter replication definition request in 2 with this:
alter replication definition publishers_rep add zip as rep_zip char(10)
See “Adding and deleting columns in source and destination tables” and alter replication definition in Chapter 3, “Replication Server Commands” Replication Server Reference Manual for more information.
In this example, alter replication definition drops the address, city, state, and zip columns from the “authors” replication definition:
alter replication definition authors drop address, city, state, zip
If there is a subscription from a replicate Replication Server with a site version earlier than 1550, the primary Replication Server rejects the alter replication definition request to drop a column.
If you alter a replication definition to drop a column,
you may need to reset autocorrection or dynamic SQL settings at
replicate Replication Servers with site versions earlier than 1550.
If there are multiple replication definitions for a primary table, alter replication definition drops only the columns from the replication definition you specify in repdef_name in the command line.
The drop parameter drops a column or columns from a table replication definition. If a column is part of the primary key or searchable columns, drop drops the column from the primary key list or searchable column list. Replication Server rejects an alter replication definition request to drop a column if the column is:
The only column
The only primary key column for the replication definition
In the where clause of a subscription or article
Before a searchable column which is specified in the where clause of an article or subscription.
You can drop searchable columns from a replication definition only if they are not used in subscription or article where clause.
Use drop subscription to remove any subscriptions in which you want the where clause to exclude the searchable columns you are dropping. See “Using the drop subscription command”.
Use alter replication definition to drop the searchable column. For example:
alter replication definition publishers_rep drop searchable columns zip
(This example removes the zip searchable column from the publishers_rep replication definition.)
See alter replication definition in Chapter 3, “Replication Server Commands” in the Replication Server Reference Manual for more information.
Use create subscription to re-create subscriptions to the altered replication definition. See “Using the create subscription command”.
Replication Server depends on primary keys to find the correct rows at the replicate or standby table. To add a the column zip as a primary key to the replication definition, enter:
alter replication definition publishers_rep add primary key zip
To drop a primary key, enter:
alter replication definition publishers_rep drop primary key zip
To replace all primary key columns, first alter the corresponding replication definition to add the new primary keys, then drop the old primary key columns in the table.
WARNING! If all primary key columns are missing from the primary table, the DSI will shut down.
You cannot change the declared column datatype (the datatype in the primary table) if it is used in a where clause in a subscription or a where clause in an article.
You cannot change the rs_address datatype.
You can change the column datatype to text, unitext, image, rawobject, or rawobject in row only if it is not a primary key or searchable column.
To change the published (replicate) datatype, you must include the declared (primary) datatype of a column (whether it is being changed or not) and the [map to] clause.
Altering a column’s datatype and nullability affects the same column across all replication definitions for a table.However, changes between a rawobject or rawobject in row and its base datatype, affects only the current replication definition. See “Translating datatypes using HDS” for more information about HDS.
Use column nullability changes for text, unitext, image, and rawobject columns only.
To replicate data for the source column zip into a destination column named rep_zip2, enter:
alter replication definition publishers_rep alter columns with zip as rep_zip
Enter such a command when:
You alter the existing destination table to add column rep_zip.
You drop and re-create the destination table to contain the column rep_zip in place of the original column zip.
