sp_setrepdbmode

Enables or disables replication of SQL statements at the database-level and for one or more specific DML operation type.

Syntax

sp_setrepdbmode <dbname> [, “<option> [<option> [...]]” [, “on” | “off”] 
   [‘threshold’, ‘<value>’]

<option> ::= { U | D | I | S }

Parameters

dbname

The name of the database for which you want to enable SQL statement replication.

option

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 individual log records and the information needed by Replication Server to build the SQL statement.

on

Enables SQL replication of the DML operation specified.

off

Disables SQL statement replication at the database-level for all types of DML operations, regardless of the operation specified in <option>.

'threshold', 'value'

Specifies the minimum number of rows that a replicated SQL statement must impact before SQL statement replication is activated. Reset <value> to ‘0’ for the default threshold of 50 rows.

Examples

Example 1

Replicates delete and select into statements:

sp_setrepdbmode pdb, 'DS', 'on'

Example 2

Displays the current SQL replication settings:

1> sp_setrepdbmode pdb1
2> go

The replication mode for database 'pdb1' is 'us'.
(return status = 0)

Example 3

To disable replication of all SQL statements at database-level, use:

sp_setrepdbmode pdb, 'D', 'off'

Example 4

To set threshold value at 100 rows:

sp_setrepdbmode pubs2, ‘threshold’, ‘100’
go

Example 5

This example shows how to set a different threshold at the database and table levels for the <pubs2> database and <table1> table:

Reset the threshold at the database-level to the default of 50 rows:
```
sp_setrepdbmode pubs2, ‘threshold’, ‘0’
go
```
Enable SQL statement replication of update, delete, insert, and select into operations for <pubs2>:
```
sp_setrepdbmode pubs2, ‘udis’, ‘on’
go
```
Trigger SQL statement replication for <table1> in <pubs2> only when update, delete, insert, and select into operations execute on <table1> and affect more than 1,000 rows:
```
sp_setrepdefmode table1, ‘threshold’, ‘1000’
go
```

Example 6

This example shows how to define the threshold at the database-level for <pubs2>, and at the same time define different operations for tables, such as <table1> and <table2>:

Set the threshold at the database-level to trigger SQL statement replication when a data manipulation language (DML) statement affects more than 100 rows:
```
sp_setrepdbmode pubs2, ‘threshold’, ‘100’
go
```
Define a different set of operations for two specific tables, where you want the operations replicated using SQL statement replication. Update, delete, and insert operations are for <table1> and delete operations are for <table2>:
```
sp_setrepdefmode table1, ‘udi’, ‘on’
go
sp_setrepdefmode table2, ‘d’ ‘on’
go
```

In this example, when a delete operation executes against <table2> or any DML on <table1> executes, the threshold of 100 rows that you defined at the database-level triggers SQL statement replication when reached.

Usage

You can set SQL statement replication at the database level only when the database has been marked for replication by setting sp_reptostandby to ALL or L1.
The default threshold is 50 rows, which means that Adaptive Server uses SQL statement replication if the DML statement affects at least 51 rows. To use the default threshold, set the threshold parameter to 0. The threshold parameter range is 0 to 10,000.
You can configure replication at the database-level and set the threshold for SQL statement replication at the database-level at the same time. For example:
```
sp_reptostandby pubs2, ‘none’
go
sp_setrepdbmode pubs2, ‘threshold’
go
```
However, you cannot configure replication at the database-level and define operations also at the database-level as SQL statement replication at the database-level requires that the entire database be replicated and you cannot replicate the operations only. For example, you cannot execute:
```
sp_reptostandby pubs2, ‘none’
go
sp_setrepdbmode pubs2, ‘udis’, ‘on’
go
```
The threshold set at the session-level overrides the threshold at the table-level and database-level, and the threshold set for any table overrides the threshold set at the database-level.