create database replication definition

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]
           [[not] replicate setname setcont]
           [[not] replicate setname setcont]
           [[not] replicate setname setcont]
           [[not] replicate setname setcont]
            [[not] replicate {SQLDML | DML_options} [in table_list]]

setname  ::= {tables | functions | transactions | system procedures}

setcont  ::= [[in] ([owner1.]name1[, [owner2.]name2 [, ... ]])]

Note: The term functions in setname refers to user-defined stored procedures or user-defined functions.

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 – Tells Replication Server whether or not to send DDL to subscribing databases. If “replicate DDL” is not included, or the clause includes “not,” DDL is not sent to the replicate database.
[not] replicate setname setcont – Specifies whether or not to send objects stated in the setname category to the replicate database. The setname category can have a maximum of one clause for tables, one clause for functions, one clause for transactions, and one clause for system procedures.
If you omit the system procedures setname or include the not option, Replication Server does not replicate the system procedures.

If you omit tables, functions, or transactions setname or include the not option, Replication Server replicates all objects of the setname category.
[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.
owner – An owner of a table or a user who executes a transaction. Replication Server does not process owner information for 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 */

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.

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 overrides the settings defined in the database replication definition for that table.