alter replication definition

Changes an existing replication definition.

Syntax

alter replication definition {replication_definition
 |* with primary at data_server.database }
{with replicate table named [table_owner.]'table_name' | alter [primary] owner from current_table_owner to new _table_owner  | 
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

{replication_definition | * } –
- replication_definition – specifies the name of one replication definition to change
- * wildcard character – use only with the with primary at data_server. database and alter [primary] owner from current_table_owner to new _table_owner clauses to change the owner in multiple replication definitions affected by the ownership transfer, where current_table owner is the same for all the replication definitions.
with replicate table named – 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.
alter [primary] owner from current_table_owner to new _table_owner – specifies the current and new table owners when you want to change the owner of a table.
Include the primary option to only modify the primary table owner if Replication Server is not replicating DDL commands.
with primary at data_server.database – specifies the data server and database containing the primary data
Use this clause only with the * wildcard character and the alter [primary] owner from current_table_owner to new _table_owner clause.

To ensure that Replication Server only alters the affected replication definitions from the intended primary database when you use the * wildcard instead of a replication definition name., include the with primary at data_server.database clause when you use alter [primary] owner from current_table_owner to new _table_owner to change the table owner. If the primary database is part of a warm standby application, data_server.database is the name of the logical data server and database.
add columns column_name – 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."
as replicate_column_name – 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.
datatype – 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.
null or not null – 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.
quoted | not quoted – Specifies whether a table or column name is a quoted identifier. Use the quoted clause on each object that needs to have quotes to the replicate.
alter columns column_name – 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.
map to published_datatype – 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.
references table owner.table name column name – Specifies the table name of the table with referential constraints at the primary database that you want to add or change as a referencing table. Use the null option to drop a reference. table_name is a character string of up to 200 characters. table_owner is optional, and represents the table owner. column name is optional. Data server operations may fail if the actual table owners do not correspond to what you specify in the replication definition. See "HandlingTables That Have Referential Constraints" in "create replication definition" command for more information on usage.
add/drop primary key – 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.
add searchable columns column_name – 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.
drop searchable columns column_name – 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.
drop column_name – Specifies columns to remove.
send standby – 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.
replicate minimal columns – 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.
replicate SQLDML [‘off’]’ – Turns on or off the SQL statement replication of the DML option specified.
replicate ‘options’ – Replicates any combination of these DML operations:
- U – update
- D – delete
- I – insert select
replicate_if_changed – 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.
always_replicate – 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.
with dynamic sql – Specifies that DSI applies dynamic SQL to the table if the command qualifies and enough cache space is available. This is the default.
See the Replication Server Administration Guide Volume 2 for the conditions a command must meet to qualify for dynamic SQL.
without dynamic sql – Specifies that DSI must not use dynamic SQL commands.
with DSI_suspended – Allows you to suspend the standby DSI, if there is one, and each of the subscribing replicate DSI threads. Replication Server suspends the DSI thread in the standby or replicate database after Replication Server applies all the data for the old replication definition version to the standby or replicate database.
After Replication Server suspends a DSI thread, you can make changes to the target schema, and to any customized function strings. When you resume the DSI thread, Replication Server replicates the primary updates using the altered replication definition.

You do not need to use with DSI_suspended if:
- There is no subscription to the replication definition.
- You do not need to change customized function strings.
- You do not need to change the replicate or standby database schema.
Note: If there is a subscription from a replicate Replication Server with a site version earlier than 1550, the replicate DSI threads for that Replication Server are not suspended.

Examples

Example 1 – Adds state as a searchable column to the authors_rep replication definition:
```
alter replication definition authors_rep
 add searchable columns state
```
Example 2 – 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
```
Example 3 – 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'
```
Example 4 – 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
```
Example 5 – 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
```
Example 6 – Places quotes around the table named foo when sent to the replicate site :
```
alter replication definition repdef
   alter replicate table name foo quoted
```

Example 7 – Removes the quoted identifier marking from the column foo_col2:

alter replication definition repdef
      alter columns “foo_col2” not quoted

Example 8 – Instructs Replication Server to alter the replication definition column name to pub_name_set, process what is currently in the queue using the old column name pub_name, and then suspend the target DSI once the data that was in the queue has been processed by Replication Server. Upon resuming the DSI, Replication Server uses the altered replication definition for the target database:
```
alter replication definition pubs_rep
alter columns with pub_name as pub_name_set
with DSI_suspended
```
Example 9 – Drops the address, city, state, and zip columns from the “authors” replication definition:
```
alter replication definition authors
drop address, city, state, zip
```

Example 10 – Adds a reference relationship to a table replication definition:

alter replication definition doctors_rep
   alter columns with tlid
   references doctors_main (logid)

Example 11 – After changing the table owner from mario to angela with the Adaptive Server alter... modify owner command, immediately execute the rs_send_repserver_cmd stored procedure at the primary database to change the table owner in the authors_repdef replication definition :
```
exec rs_send_repserver_cmd 'alter replication definition authors_repdef
alter owner from mario to angela'
```
Example 12 – After changing all tables with mario as current owner to angela as the new owner with the Adaptive Server alter... modify owner command, immediately execute the rs_send_repserver_cmd stored procedure at the primary database to change the table owner in all replication definitions to angela where the current table owner is mario:
```
exec rs_send_repserver_cmd 'alter replication definition *
with primary at NY_DS.ny_pdb1
alter owner from mario to angela'
```

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 owner of a table – coordinate the change with the transfer of object ownership at the primary Adaptive Server database by using the rs_send_repserver_cmd stored procedure at the primary database to execute alter replication definition. See Replication Server Administration Guide Volume 1 > Manage RepAgent and Support Adaptive Server > Transfer of Database Object Ownership.
- 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.
Support for subscription materialization of LOB compressed data depends on how you specify the column datatype in the replication definition and on the version of Replication Server. See Subscription Materialization of LOB Compressed Data in the Replication Server Administration Guide Volume 1.
See Replicating SQL statements for more information about replicating SQL statements.
See create replication definition 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.