Show TOC

alter replication definitionLocate this document in the navigation structure

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 either be the owner of the table, or must be “dbo” or aliased to “dbo”, or must have sa_role pemission at the replicate database in order to use the Transact-SQL identity_insert option to perform operations on the table.Without the proper permissions, you see an error message in the Replication Server log as replication of identity columns cannot proceed.

    A primary table (with one or multiple replication definitions) can contain only one identity column. However, you may use the map to option to publish multiple columns as the identity datatype with one or multiple replication definitions.

  • 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 > SAP IQ as Replication Data Server > SAP 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 Information