create replication definition

Creates a replication definition for a table that is to be replicated.

Syntax

create replication definition replication_definition
with primary at data_server.database
[with all tables named [table_owner.] 'table_name' [quoted] |
[with primary table named [table_owner.]'table_name']
 with replicate table named [table_owner.]'table_name'] [quoted]]
(column_name [as replicate_column_name] [datatype [null | not null] 
        [map to published_datatype]] [quoted]
[, column_name [as replicate_column_name] 
        [datatype [null | not null] computed]
        [map to published_datatype]] [quoted]...)
        [references [table_owner.]table_name [(column_name)]]
primary key (column_name [, column_name]...)
[searchable columns (column_name [, column_name]...)]
[send standby [{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 dynamic sql | without dynamic sql]

Parameters

Examples

Usage

  • Execute this command at the Replication Server that manages the database where the primary version of the table is stored.

  • Use rs_helprep to determine which replication definitions are available to Replication Server version 12.0 and earlier. For more information, see rs_helprep.

  • For an overview of defining and maintaining replicated tables, and for information about working with replication definitions, articles, and publications, see the Replication Server Administration Guide Volume 1.

  • Before executing the create replication definition command, be sure that:
    • The replication definition name you enter is unique among all replication definitions (table or function) in the replication system. Replication Server cannot always enforce this requirement when you enter create replication definition.

    • A connection exists from the Replication Server to the database where the primary table is stored. See create connection for more information. You can also create database connections using rs_init. See the Replication Server installation and configuration guides for your platform.

    • If you use more than one version of Replication Server (for example, version 12.0 and version 11.0.x) and you create multiple replication definitions for the same primary table, review any mixed-version issues for your replication system (for example, if column names are different for the same table in both versions). See Creating Multiple Replication Definitions for details.

  • Replication Server distributes the new replication definition to qualifying sites through the replication system. The changes do not appear immediately at all sites because of normal replication system lag time.

  • A replication definition that you create for a primary database applies to all primary connections, default and alternate, between the Replication Server that controls the replication definition and the primary database. Therefore, you must drop all replication definitions for the primary database before you drop the last primary connection to the primary database.

    With system version 1570, you can create replication definitions and publications only against a database. The name you specify for the with primary at clause of the create replication definition command must be the primary database name.

Replication Status

  • The replication status for text, unitext, image, and rawobject columns must be the same in the Adaptive Server database and in the replication definition.

  • Use alter replication definition to change replication status.

  • The replication status must be consistent for all of the replication definitions created against the same primary table.
    • If you change the replication status using alter replication definition, the replication status for other replication definitions against the same primary table also changes.

    • You do not have to specify replication status if the column is already listed in another replication definition for the same primary table.

Creating Multiple Replication Definitions

  • You can create multiple replication definitions for the same primary table and customize each one so it can be subscribed to by a replicate table whose characteristics are different from those of the primary table and other replicate tables.

    In addition to describing the primary table, each replication definition can specify, for example, a smaller number of columns, different column names, or a different table name for a replicate table. Replicate tables that match the specified characteristics can subscribe to the replication definition. You can also use multiple replication definitions even when replicate and primary tables match.

    This feature also allows you to create one replication definition for normal replication and another one for standby if the database requirements are different. See the Replication Server Administration Guide Volume 1 for details.

  • A replicate table can subscribe to only one replication definition per primary table, although it can subscribe to the same replication definition more than once.

  • Different replication definitions created for the same primary table must use the same column datatype and the same null status for text, unitext, and image columns.

  • If a table is replicated to standby or MSA connection using internal replication definition and dynamic SQL is enabled for the connection, any new replication definition for the table should define the column order consistent with the column order in the primary database. Otherwise, it may invalidate the existing prepared statements and may require the standby or MSA connection to be restarted.

Functions and Function Strings

  • Replication Server creates rs_insert, rs_delete, rs_update, rs_truncate, rs_select, and rs_select_with_lock functions for the replication definition. If the replication definition contains text, unitext, image, or rawobject data, Replication Server also creates rs_datarow_for_writetext, rs_get_textptr, rs_textptr_init, and rs_writetext functions.

  • Replication Server generates default function strings for these functions for the system-provided function-string classes and for derived classes that inherit from these classes. Some function strings may be generated dynamically, so they never exist in the RSSD. For other function-string classes, you must create all the function strings.

  • For each function-string class, each replication definition for the same table has its own set of function strings for the system functions.

  • When you create, drop, or alter a user-defined function, it is created, dropped, or altered for all the replication definitions for the same primary table.

  • Although different replication definitions for the same primary table share the same user-defined functions, each user-defined function has its own function string. You create user-defined functions using create function when you replicate stored procedures using the method associated with table replication definitions.

Specifying Columns and Datatypes

  • When you specify the columns and datatypes you want to replicate, observe these guidelines:
    • Columns that have user-defined datatypes must be defined in the replication definition with the underlying base datatypes.

    • The replication status (that is, replicate_if_changed, always_replicate) of a text, unitext, image, or rawobject column must be the same for all replication definitions on the primary table. If you change a text, unitext, image, or rawobject column’s replication status using alter replication definition, the replication status for that column also changes for other replication definitions for the same primary table.

      You do not have to specify the replication status of a text, unitext, image, or rawobject column that is part of a replication definition for the same table.

    • Omit length and precision from numeric datatype declarations. Replication Server processes numeric datatype values without affecting precision.

      Note: If you use the map to option to translate a larger varchar to a varchar with a smaller number of characters per column, make sure that any data you replicate does not exceed the character length of the column you replicate to.

      For instance, you can map a varchar(100) to a varchar(25) column, as long as the item you replicate does not exceed the limit of varchar(25). If it does, an error message appears.

    • If a replication definition column list contains an IDENTITY column and the replicate table is in Adaptive Server, 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 (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 a replication definition column list contains a timestamp column and the replicate table is in Adaptive Server, 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 with one or multiple replication definitions can contain only one timestamp column. However, you may use the map to option to publish multiple columns as the timestamp datatype with one or multiple replication definitions.

    • The rs_address datatype allows a unique subscription resolution technique. Bitmaps of the rs_address datatype (based on the underlying int datatype) are compared with a bitmask in a subscription’s where clause to determine whether a row should be replicated. To use this subscription resolution method, you must first create tables that use columns of the int datatype. In creating a replication definition, include these columns in the column list, but declare the datatype to be rs_address instead of int.

      See create subscriptions for more information. Also, see the Replication Server Administration Guide Volume 1 for more information about using the rs_address datatype.

Specifying Columns and Datatypes for Column-Level Translations

  • 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 native Replication Server datatype, declared_datatype is the native 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 the target database.

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

Using the replicate minimal columns Option

  • Using the replicate minimal columns option can improve DSI performance, reduce message overhead, and reduce queue size. It can also help to avoid application problems caused by triggers that are set for columns that are not actually changed.
    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’.

    For details on how this option works, see the Replication Server Administration Guide Volume 2.

  • These requirements apply to replicating minimal columns:
    • Normally, replicate minimal columns can be used only with replication definitions that use the default function strings for the rs_update and rs_delete functions. If you specify replicate minimal columns, you can create non-default rs_update and rs_delete function strings for the replication definition using the rs_default_fs system variable within the function string. See create function string for details.

    • You cannot use autocorrection with the replicate minimal columns option. If you specify set autocorrection on before you set replicate minimal columns, an informational message is logged for each delete or update operation. If you first specify replicate minimal columns, you cannot specify set autocorrection on for the replication definition.

    • If you have specified replicate minimal columns for a replication definition, you cannot create a subscription for it using non-atomic materialization (create subscription command, without holdlock option), or use the bulk materialization option that simulates non-atomic materialization. See the Replication Server Administration Guide Volume 2 for more information.

Replicating text, unitext, image, or rawobject Datatypes

  • The primary key of the replication definition must include the column or columns that uniquely identify a single row in the table.

  • The always_replicate and replicate_if_changed clauses let you specify the replication status for text, unitext, image, and rawobject columns. You can also set this status in the Adaptive Server system procedures sp_setreptable and/or sp_setrepcol, or sp_reptostandby. The replication status must be the same in the Adaptive Server system procedures and in the replication definitions of a primary table. If there are inconsistencies, the RepAgent can shut down. See the Replication Server Administration Guide Volume 1 for information on setting status and resolving inconsistencies if they occur. See Replication definitions and warm standby applications for information about replicating text, unitext, image, and rawobject data into warm standby applications.

  • You must specify the replication definition’s replication status as always_replicate when you mark a table with sp_setreptable only, because the sp_setreptable default replication status is always_replicate. You can change a table’s replication status to replicate_if_changed by changing the table’s replication definition replication status to replicate_if_changed and marking every column in the table with the sp_setrepcol replication status set to replicate_if_changed.

  • The following requirements apply to replicating text, unitext, image, or rawobject datatypes:
    • If a text, unitext, image, or rawobject column appears in the replicate_if_changed column list, attempting to enable autocorrection for the replication definition will cause an error. Autocorrection requires that all text, unitext, image, and rawobject columns appear in the always_replicate list for the replication definition.

    • If a text, unitext, image, or rawobject column with replicate_if_changed status was not changed in an update operation at the primary table and the update causes the row to migrate into a subscription, the inserted row at the replicate table will be missing the text, unitext, image, or rawobject data. Replication Server displays a warning message in the error log when the row migrates into the subscription and the text, unitext, image, or rawobject data is missing. In this case, run rs_subcmp to reconcile the data in the replicate and primary tables.

Replication Definitions and Warm Standby Applications

  • 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 to use the replication definition to replicate transactions for the 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 columns in the table to the standby database.

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

  • Use send standby off in alter replication definition to indicate that you don’t want any single replication definition for this table to be used in replicating into the standby database.

    When none of a primary table’s replication definitions are marked as used by the standby, all columns are replicated into the standby database, the union of all primary keys for all replication definitions for the table is used for the primary key, and minimal columns are replicated. The replicate_minimal_columns setting for the logical connection determines whether to send minimal columns or all columns for update and delete. See alter logical connection and alter replication definition for details.

  • See the Replication Server Administration Guide Volume 2 for more information about the performance optimizations gained by using replication definitions for replicating into the standby database.

  • In a primary table with multiple replication definitions, if a replication definition is already marked as used by the standby, another replication definition created or altered with send standby unmarks the first one.

  • You must specify the replication definition’s replication status as replicate_if_changed when you mark a database with sp_reptostandby only, because the sp_reptostandby default replication status is replicate_if_changed. You cannot change the replication status of text, unitext, image, and rawobject columns when the database is marked with sp_reptostandby only.

  • When you mark a database with sp_reptostandby and a table in that database with sp_setreptable, you must specify the replication status for the replication definition as always_replicate—because the default replication status is always_replicate. You can change a table’s replication status to replicate_if_changed by changing the table’s replication definition replication status to replicate_if_changed and marking every column in the table with the sp_setrepcol replication status set to replicate_if_changed.

Altering Replication Definitions

  • Use alter replication definition to add more columns or more searchable columns and to make other changes to the settings for an existing replications definition. See alter replication definition for details.

  • If you need to remove or rename primary columns in an existing replication definition, you must drop all subscriptions to the replication definition, drop the replication definition and re-create it, and re-create the subscriptions.

Replicating Stored Procedures

  • To enable replication of stored procedures, use create applied function replication definition or create request function replication definition. For an overview of replicating stored procedures, see the Replication Server Administration Guide Volume 1.

Replicating Computed Columns

  • create replication definition supports the replication of materialzied computed columns. Materialized computed columns need to be defined using its base datatype in the replication definition.

  • Materialized computed column is a computed column whose value is stored in the table page same as regular columns. It is re-evaluated upon each insert or update on its base column. It is not re-evaluated in a query.

  • There is another type of computed column called virtual or non-materialized computed column. The value of this computed column is not stored in the table or an index. It is only evaluated when it is referenced in a query and no action is taken upon insert or update operation.

    Replication of virtual computed columns is not supported and this should not be included in the replication definition.

For more information on replicating computed columns, see Replication Server Administration Guide Volume 1.

Using Quoted Identifiers

  • Use the quoted clause on each object that needs to have quotes to the replicate. When you use the quoted parameter to mark an identifier, and the dsi_quoted_identifier is set to on for a replicate server that subscribed to the replication definition, that replicate server receives the marked identifier as a quoted identifier. If the dsi_quoted_identifier is off, the markings are ignored and the replicate server does not receive quoted identifiers.

  • When replicating to a warm standby database and to replication definition subscribers, and the primary table name is marked as quoted but the replicate table name is not, or vice-versa, Replication Server sends both the primary table name and the replicate table name as quoted.

  • An embedded double quote character in identifiers is not supported.

  • Data servers such as Adaptive Server, SQL Anywhere, Microsoft SQL Server, Universal Database (UDB), and Oracle handle quoted identifiers differently in terms of length, special characters, and reserved words supported. In a heterogeneous environment, you must ensure that the quoted identifiers being replicated are valid on both the primary and replicate data servers.

  • For replication of a quoted identifier to succeed, the primary Replication Server and the Replication Server that connects to the replicate data server version must be 15.2 and later. However, intermediate Replication Servers in a route can be of lower versions.

Replicating SQL Statements

  • A table replication definition with the send standby clause can specify a replicate ‘I’ statement. You can replicate an insert select statement as a SQL replication statement only in warm standby or MSA environments. A table replication definition without a send standby clause cannot replicate the insert select statement.

  • By default, warm standby applications do not replicate the DML commands that support SQL statement replication. To use SQL replication, you can:
    • Create table replication definitions using replicate SQLDML and send standby clauses.

    • Set the WS_SQLDML_REPLICATION parameter to on. The default value is UDIS. However, WS_SQLDML_REPLICATION has a lower precedence than the table replication definition for SQL replication. If your table replication definition contains send standby clause for a table, the clause determines whether or not to replicate the DML statements, regardless of the WS_SQLDML_REPLICATION parameter setting.

  • SQL statement replication cannot perform autocorrection. If Data Server Interface (DSI) encounters a DML command for SQL statement replication and auto-correction is on, DSI is suspended and stops replication by default. Use the assign action command with error number 5193 to specify how Replication Server handles this error.

    Replication Server does not replicate SQLDML until the table level subscription is validated.

  • SQL statement replication is not supported when:
    • A replicate database has a different table schema than the primary database.

    • Replication Server must perform data or schema transformation.

    • Subscriptions include where clauses.

    • Updates include one or more text or image columns.

Handling Tables That Have Referential Constraints

For both alter replication definition and create replication definition with the reference clause, Replication Server:

  • Treats the reference clause as a column property. Each column can reference only one table.

  • Does not process the column name you provide in the column_name parameter within the reference clause.

  • Does not allow referential constraints with cyclical references. For example, the original referenced table cannot have a referential constraint to the original referencing table.

During replication processing, RTL loads:

  • Inserts to the referenced tables before the referencing table you specify in the replication definition.

  • Deletes to the referenced tables after the table you specify in the replication definition.

In some cases, updates to both tables fail because of conflicts. To prevent RTL from retrying replication processing, and thus decreasing performance, you can:

  • Stop replication updates by setting dsi_command_convert to “u2di,” which converts updates to deletes and inserts.

  • Turn off dsi_compile_enable to avoid compiling the affected tables.

RTL cannot compile and thus marks out tables with customized function-strings, and tables that have referential constraints to an existing table that it cannot compile. By marking out these tables, RTL optimizes replication processing by avoiding transaction retries due to referential constraint errors.

Permissions

create replication definition requires “create object” permission.

Related reference
alter function string
alter replication definition
configure logical connection
create connection
create applied function replication definition
create request function replication definition
create function string
create subscription
drop replication definition
rs_set_quoted_identifier
set
sp_setrepcol
sp_setreptable
rs_send_repserver_cmd