Changing the specified columns

Following are examples of how to add or change a column for a replication definition.


Adding a column

To add a char column named zip (for zip code information) to the replication definition publishers:

  1. Alter the replication definition to add the char column:

    alter replication definition publishers_rep
    add zip char(10)
    
  2. 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.


Dropping columns from a replication definition

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

Dropping a searchable column

You can drop searchable columns from a replication definition only if they are not used in subscription or article where clause.

  1. 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”.

  2. 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.

  3. Use create subscription to re-create subscriptions to the altered replication definition. See “Using the create subscription command”.


Adding or dropping primary keys

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.


Altering column datatypes


Providing a different replicate column name

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: