create database replication definition

Description

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 [, ... ]])]

Parameters

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.

• 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.

• 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.

See also