Show TOC

create database replication definitionLocate this document in the navigation structure

Creates a replication definition for replicating a database or a database object.

Syntax
create database replication definition <db_repdef>
      with primary at <server_name.db>
      [not replicate DDL] |
      [replicate DDL [{{with | without} auto_update_table_list} |
      {{with | without} auto_extend_table_list}]]
      [[not] replicate functions <setcont>]
      [[not] replicate transactions <setcont>]
      [[not] replicate system procedures <setcont>]
      [[not] replicate tables [[<setcont> [except <setcont>]] | in files ([<file1> [,<file2> [, ..]]])]
       [[not] replicate {SQLDML | DML_options} [in <table_list>]]
       [user <username> password <pass>]]
setcont ::= [[in] ([<owner1>.]<name1>[, [<owner2>.]<name2> [, ... ]])]
Parameters
db_repdef

Name of the database replication definition.

server_name.db

Name of the primary server/database combination. For example: <TOKYO.dbase>.

[not replicate DDL]

Informs Replication Server not to send DDL to subscribing databases.

[replicate DDL [{{with | without} auto_update_table_list} | {{with | without} auto_extend_table_list}]]

Informs Replication Server to send DDL to subscribing databases and whether to update or extend the table list. If you specify replicate DDL without any option, DDL is sent to the replicate database, however, the table is not added to the replication path.

replicate DDL with auto_update_table_list

Sends DDL commands to the replicate database. If pdb_automark_tables is true for a table, the table is automatically updated in the table list when drop table or rename table DDL commands are encountered.

replicate DDL without auto_update_table_list

Sends DDL commands to the replicate database, but does not update the tables in the table list when drop table or rename table DDL commands are encountered.

replicate DDL with auto_extend_table_list

Sends DDL commands to the replicate database. If pdb_automark_tables is true for a table, the table is automatically added to the table list when create table DDL commands are encountered.

replicate DDL without auto_extend_table_list
Sends DDL commands to the replicate database, but does not add the tables in the table list when create table DDL commands are encountered.
Note If you use the replicate DDL command with the auto_extend_table_list option, make sure that you are not simultaneously replicating system procedures or SQLDML operations.
[[not] replicate functions setcont]

Specifies whether to send functions to the replicate database. When replicating functions you can have a maximum of one clause for functions. If you omit the clause, Replication Sever replicates all functions.

[[not] replicate transactions setcont]

Specifies whether to send transactions to the replicate database. When replicating transactions you can have a maximum of one clause for transactions. If you omit the clause, Replication Sever replicates all transactions.

[[not] replicate system procedures setcont]

Specifies whether to send system procedures to the replicate database. When replicating system procedures you can have a maximum of one clause for system procedures. If you omit the clause, Replication Server does not replicate the system procedure.

[[not] replicate tables [[setcont [except setcont]]

Specifies whether to send tables to the replicate database. When replicating tables you can have a maximum of one clause for tables. If you omit the clause, Replication Sever replicates all tables.

An exception list can only be used with table list. The exception list adds additional adjustment for the table list defined in the database replication definition and takes precedence over the table list.

[[not] replicate tables in files (file 1, file 2...)]

Specifies whether to send tables specified in the <file> to the replicate database.

The order of the files in the in files parameter’s file list sets the materialization order. All the tables in the first file in the list materialize—in the order in which they are listed in the file—before tables in the second file begin to materialize, and so on.

If a table in any file fails to materialize, SAP Replication Server finishes materializing all the other tables in that file, but does not move on to the next file. If a table occurs in more than one file, only the first occurrence triggers materialization. If a table is later added to the replication definition using the alter database replication definition command, the file will not be updated. The user who starts the primary Replication Server must have read permission for the files in the in files parameter’s file list.

Note The user who starts the primary Replication Server must have read permission to read the file.
The table name format in a file and in a table list is the same. You can specify the table names in a table list in these formats:
  • <ownername>.<tablename>
  • <tablename> (the table name is stored as dbo.tablename)
  • *.<tablename>
  • <ownername>.*
  • '*x*y'.'a*b' (wildcard embedded in the string)
Note When create database replication definition is issued, the partial wildcard is expanded through all lists, including the table list and the exception list. The simple wildcard is expanded in the exception list before they are stored in the system table. You must specify values for user and password if your require any wildcard expansion.
When you use an include or exclude table list, observe these guidelines:
  • The table name delimiter in the file must be a new line character.
  • Lines starting with # are ignored as comments.
  • Blanks before an owner name or table name are truncated.
  • The max length of an owner name is 30 characters.

An exception list can only be used with table list. The exception list adds additional adjustment for the table list defined in the database replication definition and takes precedence over the table list.

[not] replicate {SQLDML | DML_options} [in table_list]

Informs Replication Server whether or not to replicate SQL statements to tables defined in <in table_list>.

SQLDML
These DML operations:

  • U – update

  • D – delete

  • I – insert select

  • S – select into

DML_options
Any combination of these DML operations:

  • U – update

  • D – delete

  • I – insert select

  • S – select into

When the database replication mode is set to any combination of UDIS, the RepAgent sends both the individual log records and the information needed by Replication Server to build the SQL statement.

[user <username> password <pass>]

The user ID and password used to connect to the primary Adaptive Server database or Replication Agent and select from the primary table.

If you use wildcard in a table name, you must specify values for user and password.
Note The user and password values are used once and are not stored in the RSSD.
owner

An owner of a table or a user who executes a transaction.

Note

When you run the create database replication definition command, do not specify the owner of functions or system procedures.

You can replace <owner> with a space surrounded by single quotes or with an asterisk.

  • A space (' ') – indicates no owner.

  • An asterisk (*) – indicates all owners. Thus, for example, <*.publisher> means all tables named <publisher>, regardless of owner.

name

The name of a table, function, transaction, or system procedure.

You can replace <name> with a space surrounded by single quotes or with an asterisk.

  • A space (' ') – indicates no name. For example, <maintuser.’ ‘ >means all unnamed maintenance user transactions.

  • An asterisk (*) – indicates all names. Thus, for example, <robert.*> means all tables (or transactions) owned by <robert>.

Examples
Example 1
Creates a database replication definition <rep_1B>. This database replication definition specifies that only tables <employee> and <employee_address> are replicated:
create database replication definition rep_1B
  with primary at PDS.pdb
  replicate tables in (employee, employee_address)
Example 2
Creates a database replication definition rep_2. In this example, the database <my_db> is replicated, DDL is replicated, but system procedures are not replicated:
create database replication definition rep_2
  with primary at dsA.my_db
  replicate DDL
  not replicate system procedures
Example 3
Replicates insert, update, delete, and select into commands from all the tables in the <pdb1> database. All transactions and functions are replicated but DDL and system procedures are not:
create database replication definition rep_3
   with primary at ds3.pdb1
   replicate SQLDML
This example has the same result as the preceding example:
create database replication definition rep_3
   with primary at ds3.pdb1
   replicate 'UDSI'
Example 4
Filters out the select into statement for all tables. The second clause, not replicate 'U' in (T), filters out updates on table <T>:
create database replication definition dbrepdef
       with primary at ds1.pdb1
       not replicate ‘S’
       not replicate ‘U’ in (T)
go
Example 5
Enables update and delete statements on all tables using the replicate 'UD' clause:
create database replication definition dbrepdef_UD
       with primary at ds2.pdb1
       replicate 'UD'
go
Example 6
You can use multiple clauses to specify a table multiple times in the same definition. However, you can use each of U, D, I, and S only once per definition:
create database replication definition dbrepdef
       with primary at ds2.pdb1
       replicate tables in (tb1,tb2)
       replicate 'U' in (tb1)
       replicate 'I' in (tb1,tb2)
go
Example 7
A replication definition that replicates all user stored procedures, system procedures, and DML for all the tables in the database except for the table <T>. For the table <T>, the replication definition replicates all commands except for the delete command:
create database replication definition repdef_7
   with primary at ds3.pdb1
   replicate functions
   replicate system procedures
   replicate 'IUS'      /* replicate 'IUS' DML for all tables, including */
                       /* table 'T' */
   not replicate 'D' in (T)  /* not replicate 'D' DML for table T, but */
                             /* replicate 'D' for all other tables */
Example 8
Creates a database replication definition <dbrepdef>. DDL is replicated and if a table is marked for replication at the primary, the table is automatically added to <table_list> when create table DDL commands are encountered. The command also replicates data for the table <USER1.TABLE1> and all data for <TABLE2>, except the <TABLE2> owned by <USER2>.
create database replication definition <dbrepdef>
with primary at <ds1>.<pdb1>
replicate DDL 
with auto_extend_table_list
replicate tables in (<USER1>.<TABLE1>, *.<TABLE2>)  except in (<USER2>.<TABLE2>)
Example 9
Creates a database replication definition <db_repdef>. DDL is replicated, but does update the <table_list> when new DDL commands are encountered. The command also replicates data for the table specified in the file /sap/user/tablelist.txt, which include tables <USER1>.<TABLE1>, and all data for <TABLE2>.
For example, tablelist.txt contains:
================
#user tables:
USER1.TABLE1
*.TABLE2
================
create database replication definition <db_repdef>
with primary at <ds1>.<pdb1>
replicate DDL 
replicate tables in files (‘/sap/user/table_list.txt’)
Usage

  • create database replication definition lets you replicate all, all with some exceptions, or only some of the tables, functions, transactions, and system procedures from the primary database.

  • Use create database replication definition alone or in conjunction with table and function replication definitions.

  • With only a database replication definition, that is, without table or function replication definitions, Replication Server cannot transform data. However, it can perform minimal column replication. This data replication behavior is similar to that of a default warm standby.

    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 column with INIT_VECTOR NULL and PAD NULL. If a table in the database includes encrypted columns where the encryption key was created with random padding (the default) or initialization vectors, a table level replication definition is required to ensure database consistency.

  • Database replication definitions are global objects. They are replicated to all Replication Servers that have a route from the defining Replication Server.

  • Database replication definitions do not affect request function replication.

  • Table and function filters are not implemented if table and function subscriptions exist.

  • Replication Server does not process owner information for functions and system procedures.

  • If you use the auto_update_table_list option and a drop table DDL command is encountered, the table is dropped from the include list or the table is added to the exclude list.

  • If you use the auto_update_table_list option and a rename table DDL command is encountered, the table is renamed in the table list. If the original table is already being replicated, the renamed table is also replicated. Otherwise, the renamed table is not replicated.

  • If you use the auto_extend_table_list option and a create table DDL command indicates that the table is automatically marked, the table is created at the replicate database and replication of the table data is established for the newly created table. If the create table DDL command does not indicate that the table is automatically marked, the table is created at the replicate database. However, replication of the table data is not established for the newly created table.

Owner Information

  • Replication Server always uses owner information provided in the database replication definition.

  • Replication Server does not use owner information provided in a table replication definition if the table is marked with sp_reptostandby.

  • Replication Server only uses owner information provided in a table replication definition if the table is marked by sp_setreptable with the owner_on clause.

SQL Statement Replication

  • To replicate SQL statements in an MSA environment, you must include the replicate SQLDML clause in your replication definition.

  • You can use multiple replicate clauses in a create database replication definition. However, for an alter database replication definition, you can use only one clause.

  • If you do not specify a filter in your replication definition, the default is the not replicate clause. Apply alter database replication definition to change the SQLDML filters. You can either specify one or multiple SQLDML filters in a replicate clause.

  • If a table replication definition with send standby clause is defined for a table, the SQL replication settings of the table replication definition override the settings defined in the database replication definition for that table.

Related Information