alter replication definition

Changes an existing replication definition.

Syntax

alter replication definition replication_definition
{with replicate table named [table_owner.]'table_name' | 
add column_name [as replicate_column_name]
       [datatype [null | not null]]
       [map to published_datatype] [quoted],... |
alter columns with column_name
       [as replicate_column_name] [quoted | not quoted],...|
alter columns with column_name
        datatype [null | not null]
        [map to published_datatype],...|
        references {[table_owner.]table_name [(column_name) | null}]
alter columns column_name {quoted | not quoted}
add primary key column_name [, column_name]... |
drop primary key column_name [, column_name]... |
add searchable columns column_name [, column_name]... |
drop searchable columns column_name [, column_name]... |
drop column_name[, column_name] ... |
send standby [off | {all | replication definition} columns] |
replicate {minimal | all} columns | 
replicate {SQLDML [‘off’] | ‘options’} |
replicate_if_changed column_name [, column_name]...|
always_replicate column_name [, column_name]... |
{with | without} dynamic sql |
alter replicate table name {quoted | not quoted}}
[with DSI_suspended]

Parameters

Examples

Usage

  • Use the alter replication definition command to change a replication definition by:
    • Adding or dropping primary keys

    • Changing the name of a target replicate table

    • Changing the names of target replicate columns

    • Adding columns and indicating the names of corresponding target replicate columns

    • Adding or dropping searchable columns

    • Changing replication definition usage by warm standby applications

    • Changing column datatypes

    • Changing between replicating all or minimal columns

    • Changing replication status for text, unitext, image, or rawobject columns

    • Introducing or removing a column-level datatype translation

    • Including or excluding the table in the dynamic SQL application at DSI

  • Execute alter replication definition at the primary site for the replication definition.

  • For a database replication definition to replicate encrypted columns without using a table level replication definition, you must define the encryption key for the encrypted columns with INIT_VECTOR NULL and PAD NULL.

  • In a mixed-version environment, where the primary Replication Server has a version later than that of the replicate Replication Server, you cannot change a replication definition that is supported and subscribed to by the replicate Replication Server if the replicate Replication Server cannot support the modification. However, if the replicate Replication Server supports but does not subscribe to the replication definition, the replication definition is modified and is dropped from the replicate Replication Server.

  • See Replicating SQL statements for more information about replicating SQL statements.

  • See create replication definition command for more information about the options in the alter replication definition command.

Adding Columns

  • If you add columns, coordinate alter replication definition with distributions for the replication definition. To avoid errors, follow the steps in "Procedure to Alter a Replication Definition."

  • If a column you are adding to a replication definition contains an identity column, the maintenance user must be the owner of the table (or must be “dbo” or aliased to “dbo”) at the replicate database in order to use the Transact-SQL identity_insert option. A primary table can contain only one identity column.

  • If the column you are adding to a replication definition contains a timestamp column, the maintenance user must be the owner of the table (or must be “dbo” or aliased to “dbo”) at the replicate database. A primary table can contain only one timestamp column.

Dropping Columns

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

    Note: 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.

Altering Column Dataypes

  • You cannot change the column datatype if it is used in a subscription or article where clause.

  • You cannot change the rs_address datatype.

  • You can change the column datatype to a text, untext, image, rawobject, or rawobject in row datatype only if it is not a primary key or searchable column.

  • To change the published datatype of a column, you must specify both the declared datatype and the map to option.

  • If there are more than one replication definition for a primary table, declared datatype and nullability of a column should be consistent across all replication definitions of the table.

  • See the Replication Server Administration Guide Volume 1, which describes how to change datatypes.

  • Changes between null and not null can only be used for text, unitext, image and rawobject columns.

Using Column-Level Datatype Translation

  • To effect column-level datatype translations, you must first set up and install the heterogeneous datatype support (HDS) objects as described in the Replication Server Configuration Guide for your platform.

  • You cannot use text, unitext, image, or rawobject datatypes as a base datatype or a datatype definition or as a source or target of either a column-level or class-level translation.

  • declared_datatype depends on the datatype of the value delivered to Replication Server:
    • If the Replication Agent delivers a base Replication Server datatype, declared_datatype is the base Replication Server datatype.

    • If the Replication Agent delivers any other datatype, declared_datatype must be the datatype definition for the original datatype in the primary database.

  • published_datatype is the datatype of the value after a column-level translation, but before any class-level translation. published_datatype must be a Replication Server native datatype or a datatype definition for the datatype in another database.

  • Columns declared in multiple replication definitions must use the same declared_datatype in each replication definition. published_datatype can differ.

Replicating All or Minimal Columns

  • When you use replicate minimal column option for a replication definition, data is sent to replicate Replication Servers for the minimum number of columns needed for delete or update operations. Specify replicate all columns to replicate all columns. See create replication definition for additional information about this feature.
    Note: If your replication definition has replicate all columns and the replicate minimal columns connection property is set to ‘on’, the connection replicates minimal columns. If you want to replicate all columns to your target database, then set replicate minimal columns values for the DSI connection to ‘off’.

Replicating Into a Standby Database

  • Replication Server does not require replication definitions to maintain a standby database in a warm standby application. Using replication definitions may improve performance in replicating into the standby database. You can create a replication definition just for this purpose for each table in the logical database.

  • Use send standby with any option other than off to use this replication definition to replicate transactions for this table to the standby database. The replication definition’s primary key columns and replicate minimal columns setting are used to replicate into the standby database. The options for this method include:
    • Use send standby or send standby all columns to replicate all primary table columns into the standby database.

    • Use send standby replication definition columns to replicate only the replication definition’s columns into the standby database.

  • Use send standby off to indicate that no single replication definition for this table should be used in replicating into the standby database. All the columns in the table are replicated into the standby database, and the union of all primary key columns in all replication definitions for the table is used in replicating into the standby database. The replicate_minimal_columns setting of the logical connection determines whether to send minimal columns or all columns for update and delete. See alter logical connection.

    If no replication definition exists for a table, all the columns in the table are replicated into the standby database and Replication Server constructs the primary key. In this case, replicate_minimal_columns is on.

Handling Tables That Have Referential Constraints

You can use a replication definition to specify tables that have referential constraints, such as a foreign key and other check constraints, so that Replication Server is aware of these tables when you enable RTL or HVAR. See the Replication Server Administration Guide Volume 2 > Performance Tuning > Advanced Services Option > High Volume Adaptive Replication to Adaptive Server and the Replication Server Heterogeneous Replication Guide > Sybase IQ as Replication Data Server > Sybase IQ Replicate Database Configuration.

Procedure to Alter a Replication Definition

When you request changes to replication definitions, Replication Server coordinates the propagation of replication definition changes and data replication automatically. You can request replication definition changes directly at the primary Replication Server, or at the primary database using the alter replication definition, alter applied replication definition, or alter request function replication definition commands, while making changes to the database schema.

When the primary database log does not contain data for the replication definition being changed, you can issue the replication definition request directly at the primary Replication Server. Otherwise, it is always safe to issue the replication definition requests at the primary database, using the rs_send_repserver_cmd stored procedure.

If the database does not support rs_send_repserver_cmd, you need to wait until the primary database log does not have any data rows for the schema that you are changing, and then execute the alter replication definition request at the primary Replication Server.

See Replication Server Administration Guide Volume 1 > Managing Replicated Tables > Replication Definition Change Request Process.

Permissions

alter replication definition requires “create object” permission.

Related reference
admin verify_repserver_cmd
alter function string
create replication definition
drop replication definition
rs_set_quoted_identifier
rs_send_repserver_cmd
rs_helprepversion