Changes an existing replication definition.
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]
The name of the replication definition to change.
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 whether a table or column name is a quoted identifier.
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.
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 “Handling tables that have referential constraints” for more information on usage.
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 columns to remove.
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.
Turns on or off the SQL statement replication of the DML option specified.
Replicates any combination of these DML operations:
U – update
D – delete
I – insert select
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.
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.
Specifies that DSI must not use dynamic SQL commands.
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.
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.
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
Marks the table named foo as a quoted identifier:
alter replication definition repdef alter replicate table name “foo” quoted
Removes the quoted identifier marking from the column foo_coll:
alter replication definition repdef with replicate table named “foo” alter columns “foo_col1” not quoted
Instructs Replication Server to suspend the target DSI after primary data that exists before you execute alter replication definition is replicated to the target database:
alter replication definition pubs_rep alter columns with pub_name as pub_name_set with DSI_suspended
Drops the address, city, state, and zip columns from the “authors” replication definition:
alter replication definition authors drop address, city, state, zip
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 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.
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.
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 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.
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.
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 “High Volume Adaptive Replication to Adaptive Server,” in Chapter 4, “Performance Tuning” in the Replication Server Administration Guide Volume 2 and Chapter 13, “Replication into Sybase IQ” in the Replication Server Heterogeneous Replication Guide.
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 definition change request process,” in Chapter 9, “Managing Replicated Tables” in the Replication Server Administration Guide Volume 1.
alter replication definition requires “create object” permission.
admin verify_repserver_cmd, alter function string, create replication definition, drop replication definition, setrs_set_quoted_identifier, rs_send_repserver_cmd, rs_helprepversion
