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

replication_definition – The replication definition, which must conform to the rules for identifiers. The replication definition name is assumed to be the name of both the primary and replicate tables, unless you specify the table names.
with primary at data_server.database – Specifies the location of the primary data. If the primary database is part of a warm standby application, data_server.database is the name of the logical data server and database.
with all tables named – Specifies the table name at both the primary and replicate databases. table_name is a character string of up to 200 characters. table_owner is optional, and represents the table owner. Data server operations may fail if the actual table owners do not correspond to what you specify in the replication definition.
quoted – Use the quoted parameter to specify that the table or column name being created is a quoted identifier. Use the quoted clause on each object that needs to have quotes to the replicate.
with primary table named – Specifies the table name at the primary database. table_name is a character string of up to 200 characters. table_owner is optional and represents the table owner. Data server operations may fail if the actual table owners do not correspond to what you specify in the replication definition.
If you specify the primary table name but do not also specify the replicate table name, the replication definition name is assumed to be the name of the replicate table.
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 optional and represents the table owner. Data server operations may fail if the actual table owners do not correspond to what you specify in the replication definition.
If you specify the replicate table name but do not also specify the primary table name, the replication definition name is assumed to be the name of the primary table.
column_name – A column name from the primary table. You cannot use a column name more than once in each clause.
Each column and datatypes must be enclosed in parentheses ( ).
as replicate_column_name – Specifies a column name in a replicate table into which data from the primary column will be copied. Use this clause when the source and destination columns have different names.
datatype – The datatype of the column in the primary table. See Datatypes for a list of the datatypes and syntax.
Use as declared_datatype if you are specifying a column-level datatype translation. A declared datatype must be a native Replication Server datatype or a datatype definition for the primary datatype.

For different replication definitions created against the same table, the column datatypes must be the same, however the published datatypes may be different. See the Replication Server Administration Guide Volume 1 for more information.

Specifying the datatype is optional if a replication definition created against the same table already has this column.
null or not null – Applies only to text, unitext, image, or 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, and rawobject columns.

You cannot change this setting for a column once it is included in a replication definition for the table. To change the value, you must drop and re-create all replication definitions that include the column.
map to published_datatype – Specifies the datatype of a column after a column-level datatype translation, but before any class-level translation and before presentation to the replicate database.
references table owner.table name column name – Specifies the table name of the table with referential constraints at the primary database. 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.
primary key column_name – Specifies the columns that form the primary key for the table. You cannot use a column name more than once in each clause.
You cannot include text, unitext, image, rawobject, rawobject in row, or rs_address columns as part of the primary key.
searchable columns column_name – Specifies the columns that can be used in where clauses of create subscription, define subscription, or create article. You cannot use a column name more than once in each clause.
You cannot specify text, unitext, image, rawobject, rawobject in row or encrypted columns as searchable columns.
send standby – Specifies how to use the replication definition in replicating into a standby database in a warm standby application. See Replication Definitions and Warm Standby Applications for details on using this clause and its options.
replicate minimal columns or replicate all columns – Sends all replication definition columns for every transaction or only those needed to perform update or delete operations at replicate databases. The default is to replicate all columns.
Note: If your replication definition has the [replicate {minimal | all} columns] clause, the [replicate {minimal | all} columns] clause must always precede the [replicate {SQLDML ['off'] | 'options'}] clause.
replicate SQLDML [‘off’] – Turns on or off the SQL replication of the DML operation specified.
replicate ‘options’ – Replicates any combination of these DML operations:
- U – update
- D – delete
- I – insert select
replicate_if_changed – Replicate text, unitext, image, or rawobject columns only when their column data changes.
always_replicate – Always replicate text, unitext, image, and rawobject columns.
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.

Examples

Example 1 – Creates a replication definition named authors_rep for the authors table. The primary copy of the authors table is in the pubs2 database in the LDS data server. All copies of the table are also named authors. Only the minimum number of columns will be replicated for delete and update operations:

create replication definition authors_rep
  with primary at LDS.pubs2
  with all tables named 'authors'
    (au_id varchar(11), au_lname varchar(40),
     au_fname varchar(20), phone char(12),
     address varchar(12), city varchar(20),
     state char(2), country varchar(12), postalcode
     char(10))
  primary key (au_id)
  searchable columns (au_id, au_lname)
  replicate minimal columns

Example 2 – Creates a replication definition called blurbs_rep for the blurbs table owned by “emily” in the pubs2 database. Data in the copy column, which uses the text datatype and accepts null values, will be replicated when the column data changes:
```
create replication definition blurbs_rep
  with primary at TOKYO_DS.pubs2
  with all tables named emily.'blurbs'
    (au_id char(12), copy text null)
  primary key (au_id)
  replicate_if_changed (copy)
```
Example 3 – Where at least one replication definition already exists for the primary table publishers in the pubs2 database, this command creates an additional replication definition called pubs_copy_rep. This replication definition can be subscribed to by replicate tables that are named pubs_copy and for which “joe” is the owner. Subscriptions may fail for replicate tables that are also named pubs_copy but for which “joe” is not the owner:
```
create replication definition pubs_copy_rep
  with primary at TOKYO_DS.pubs2
  with primary table named 'publishers'
  with replicate table named joe.'pubs_copy'
    (pub_id, pub_name as pub_name_set)
  primary key (pub_id)
```
Data for the pub_name column in the primary table will replicate into the pub_name_set column in the replicate table, which must share the same datatype. You do not need to specify the datatype for a column in an existing replication definition. In this example, the city and state columns from the primary table are not required for the replicate table pubs_copy, and are thus excluded from this replication definition.
Example 4 – Creates a replication definition that replicates all modified columns of the authors table to the standby database. This definition also replicates to the MSA, however, only the modified values of au_id and au_lname columns are replicated. au_id is the key used to update and delete from the authors table:
```
create replication definition authors_rep
  with primary at LDS.pubs2
  with all tables named 'authors'
     (au_id varchar(11), au_lname varchar(40))
  primary key (au_id)
  send standby
  replicate minimal columns
```

Example 5 – Creates a table foo where column foo_col1 is a quoted identifier:

create replication definition repdef
   with primary at primaryDS.primaryDB
   with all tables named “foo”
   (“foo_col1” int quoted, “foo_col2” int)
   primary key (“foo_col1”)

Example 6 – Creates a table replication definition that replicates update and delete statements:

create replication definition repdef1
       with primary at ds3.pdb1
       with all tables named 'tb1'
         (id_col int, str_col char(40))
       primary key (id_col)
       replicate all columns
       replicate ‘UD’
go

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.