Changes an existing replication definition.
alter replication definition replication_definition
{with replicate table named table_owner.]'table_name' |
add colum_name [as replicate_column_name]
[datatype [null | not null]]
[map to published_datatype],... |
alter columns with column_name
[as replicate_column_name],...|
alter columns with column_name
datatype [null | not null]
[map to published_datatype],...|
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]... |
send standby [off | {all | replication definition} columns] |
replicate {minimal | all} columns |
replicate_if_changed column_name [, column_name]... |
always_replicate column_name [, column_name]...}
The name of the replication definition to alter.
Specifies the name of the table at the replicate database. table_name is a character string of up to 200 characters. table_owner is an optional qualifier for the table name, representing the table owner. Data server operations may fail if actual table owners do not correspond to what you specify in the replication definition.
Specifies additional columns and their datatypes for the replication definition. column_name is the name of a column to be added to the replicated columns list. The column name must be unique for a replication definition.
Also add columns declared_column_name. See “Using column-level datatype translations”.
For columns you are adding to the replication definition, specifies a column name in a replicate table into which data from the primary column will be replicated. replicate_column_name is the name of a column in a replicate table that corresponds to the specified column in the primary table. Use this clause when the replicate and primary columns have different names.
The datatype of the column you are adding to a replication definition column list or the datatype of an existing column you are altering. See “Datatypes” for a list of supported datatypes and their syntax.
If a column is listed in an existing replication definition for a primary table, subsequent replication definitions for the same primary table must specify the same datatype.
Use as declared_datatype if you are specifying a column-level datatype translation for the column. A declared datatype must be a native Replication Server datatype or a datatype definition for the primary datatype.
Applies only to text, unitext, image, and rawobject columns. Specifies whether a null value is allowed in the replicate table. The default is not null, meaning that the replicate table does not accept null values.
The null status for each text, unitext, image, and rawobject column must match for all replication definitions for the same primary table, and must match the settings in the actual tables. Specifying the null status is optional if an existing replication definition of the same primary table has text, unitext, image, or rawobject columns.
Specifies columns and their datatypes to alter in the replication definition. column_name is the name of a column to be changed. The column name must be unique for a replication definition.
Use alter columns declared_column_name when specifying a column-level datatype translation.
Specifies the datatype of a column after a column-level datatype translation. published_datatype must be a Replication Server native datatype or a datatype definition for the published datatype.
Used to add or remove columns from the primary keys column list.Replication Server depends on primary keys to find the correct rows at the replicate or standby table. To drop 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. If all primary keys are missing, the DSI will shut down. See create replication definition for additional information on primary keys.
Specifies additional columns that can be used in where clauses of the create subscription or define subscription command. column_name is the name of a column to add to the searchable columns list. The same column name must not appear more than once in each clause.
You cannot specify text, unitext, image, rawobject, rawobject in row or encrypted columns as searchable columns.
Specifies columns to remove from the searchable column list. You can remove columns from the searchable column list only if they are not used in subscription or article where clauses.
Specifies how to use the replication definition in replicating into a standby database in a warm standby application. See “Replicating into a standby database” for details on using this clause and its options.
Sends (to replicate Replication Servers) only those columns needed to perform update or delete operations at replicate databases. To replicate all columns, use replicate all columns.
Specifies text, unitext, image, or rawobject columns to be added to the replicate_if_changed column list. When multiple replication definitions exist for the same primary table, using this clause to change one replication definition changes all replication definitions of the same primary table.
Specifies text, image, or rawobject columns to be added to the always_replicate column list. When multiple replication definitions exist for the same primary table, using this clause to change one replication definition changes all replication definitions of the same primary table.
Adds state as a searchable column to the authors_rep replication definition:
alter replication definition authors_rep add searchable columns state
Changes the titles_rep replication definition to specify that only the minimum number of columns will be sent for delete and update operations:
alter replication definition titles_rep replicate minimal columns
Changes the titles_rep replication definition to specify that the replication definition can be subscribed to by a replicate table called copy_titles owned by the user “joe”:
alter replication definition titles_rep with replicate table named joe.'copy_titles'
Changes the pubs_rep replication definition to specify that the primary column pub_name will replicate into the replicate column pub_name_set:
alter replication definition pubs_rep alter columns with pub_name as pub_name_set
Introduces a column-level translation that causes hire_date column values to be translated from rs_db2_date (primary) format to the native datatype smalldatetime (replicate) format:
alter replication definition employee_repdef alter columns with hire_date as rs_db2_date map to smalldatetime
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.
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
If you use more than one version of Replication Server (for example, Replication Server version 12.0 and version 11.0.x) and create multiple replication definitions for the same primary table, the first replication definition created, which has the same primary and replicate table names and does not include table owner name, is marked and propagated to pre-11.5 Replication Servers.
If you alter a replication definition propagated to a pre-11.5 Replication Server so that it is no longer version 11.0.x compatible, and subscriptions exist to that replication definition from 11.0.x and earlier sites, you cannot use alter replication definition. If there are no subscriptions from pre-11.5 to the replication definition, the definition is dropped from pre-11.5 sites, and the oldest replication definition created for that table that is compatible with version 11.0.x is distributed to the Replication Server of an earlier version so subscriptions can be created against it. See create replication definition for more information about working with replication definitions in a mixed-version environment.
alter replication definition affects the version compatibility of a pre-12.0 replication definition if you change the datatype of a column to rawobject or rawobject in row or add a column with a datatype of rawobject or rawobject in row.
Thus, for example, if you introduce a rawobject datatype into a replication definition compatible with a pre-12.0 version of Replication Server, the version of the replication definition will change and will no longer be compatible with the pre-12.0 Replication Server.
See create replication definition for more information about the options in the alter replication definition command.
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.
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.
Projections require that datatype and nullability be consistent across all projections for a table for declared datatypes. This is not necessary for published datatypes.
Changes between a rawobject or rawobject
in row and its base datatype for which only the current
replication definition is affected do not affect all projections.
See the Replication Server Administration Guide Volume 1, which describes how to change datatypes.
Use column nullability changes only for text, unitext, image, and rawobject columns.
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.
When you use replicate minimal 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.
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.
Altering a replication definition
Quiesce the replication system. You can use Replication Server Manager or the procedure described in the Replication Server Administration Guide Volume 1 to quiesce the system.
Ideally, you should first quiesce primary updates and ensure that all primary updates have been processed by the replication system. If you are unable to do that, then old updates in the primary log will not have values for new columns, and the replication system will use nulls instead. You may need to take this into account when altering function strings in step 4 below.
Shut down the RepAgent for the primary database.
Alter the table at the primary site and the replicate sites, if this is why you are altering the replication definition.
Alter the replication definition as many times as needed. Wait for the modified replication definition to arrive at the replicate sites.
If necessary, alter any function strings pertaining to the replication definition. Wait for the modified function strings to arrive at the replicate sites.
If necessary, modify subscriptions on the replication definition at destination sites. To modify a subscription, drop it and re-create it using the drop subscription and create subscription commands.
Altering a replication definition does not affect current subscriptions. If new columns are added to the replication definition, they are replicated with any new updates for all existing subscriptions.
If the dynamic SQL feature is enabled for the connection to the replicate database, suspend and resume the connection to clear out the old prepared statements.
Start up the RepAgent for the primary database.
Resume updates to the primary table.
Certain restrictions apply to replication status for text, unitext, image, and rawobject columns in replicated and warm standby databases. See “Replicating text, unitext, image, or rawobject datatypes”.
Changing the replication status of a text, unitext, image,
or rawobject column from replicate_if_changed to always_replicate
Stop updates to the primary table.
Wait for transactions that modify the primary table and have text, unitext, image, and rawobject columns with a replicate_if_changed status to arrive at the replicate sites.
Use sp_setrepcol to change the status of the column at the Adaptive Server to always_replicate.
Use alter replication definition to change the status of the column to always_replicate. Wait for the modified replication definition to arrive at the replicate sites.
Resume updates to the primary table.
Changing the replication status of a text, untext, image,
or rawobject column from always_replicate to replicate_if_changed
Use alter replication definition to change the status of the column to replicate_if_changed. Wait for the modified replication definition to arrive at the replicate sites.
Use sp_setrepcol to change the status of the column at the Adaptive Server to replicate_if_changed.
When
changing the status from always_replicate to replicate_if_changed, there
is no need to stop updates to the primary table because the change
in status does not result in a RepAgent error.
alter replication definition requires “create object” permission.
alter function string, create replication definition, drop replication definition, set autocorrection
