Creates a replication definition for replicating a database or a database object.
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 (‘file_path’)] [[not] replicate {SQLDML | DML_options} [in table_list]] [user username password pass]] setcont ::= [[in] ([owner1.]name1[, [owner2.]name2 [, ... ]])]
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.
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.
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.
create database replication definition rep_1B with primary at PDS.pdb replicate tables in (employee, employee_address)
create database replication definition rep_2 with primary at dsA.my_db replicate DDL
not replicate system procedures
create database replication definition rep_3 with primary at ds3.pdb1 replicate SQLDML
create database replication definition rep_3 with primary at ds3.pdb1 replicate 'UDSI'
create database replication definition dbrepdef with primary at ds1.pdb1 not replicate ‘S’ not replicate ‘U’ in (T) go
create database replication definition dbrepdef_UD with primary at ds2.pdb1 replicate 'UD' go
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
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 */
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)
================ #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’)
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 datatbase. 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 overrides the settings defined in the database replication definition for that table.