Creates a replication definition for a table that is to be replicated.
create replication definition replication_definition
with primary at data_server.database
[with all tables named [table_owner.] 'table_name' |
[with primary table named [table_owner.]'table_name']
with replicate table named
[table_owner.]'table_name']]
(column_name [as replicate_column_name]
[datatype [null | not null]
[map to published_datatype]]
[, column_name [as replicate_column_name]
[datatype [null | not null] computed]
[map to published_datatype ]]...)
primary key (column_name [, column_name]...)
[searchable columns (column_name
[, column_name]...)]
[send standby [{all | replication definition}
columns]]
[replicate {minimal | all} columns]
[replicate_if_changed
(column_name [, column_name]...)]
[always_replicate (column_name [, column_name]...)]
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.
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.
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.
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.
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.
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 ( ).
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.
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.
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.
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.
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.
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.
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.
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.
Replicate text, unitext, image, or rawobject columns only when their column data changes.
Always replicate text, unitext, image, and rawobject columns.
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
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)
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.
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
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.
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.
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 replication definition specifies different primary and replicate table names, specifies different primary and replicate column names, or includes table owner names, only Replication Servers version 11.5 or later can subscribe to it. Such a replication definition is incompatible with Replication Servers version 11.0.x or earlier.
The first replication definition created for a table is marked and propagated to a Replication Server of a version earlier than 11.5 if it is compatible; that is, if it has the same primary and replicate table and column names, and does not include the table owner name.
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.
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.
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.
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.
Declare columns with the Adaptive Server timestamp datatype as binary(8) in replication definitions. See “Datatypes” for more information about mapping Adaptive Server datatypes to supported datatypes.
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.
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 subscription for more information. Also, see the Replication Server Administration Guide Volume 1 for more information about using the rs_address datatype.
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 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.
For details on how this option works, see the Replication Server Administration Guide Volume 2.
The following 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.
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 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.
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.
To enable replication of stored procedures, use create function replication definition. For an overview of replicating stored procedures, see the Replication Server Administration Guide Volume 1.
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.
create replication definition requires “create object” permission.
alter function string, alter replication definition, configure logical connection, create connection, create function replication definition, create function string, create subscription, drop replication definition, set autocorrection, sp_setrepcol, sp_setreptable
