sp_setrepdefmode

sp_setrepdefmode includes options to:

Enable or disable SQL statement replication for a specific DML operation on a specific table
Configure the threshold that must be reached for SQL statement replication to become active

sp_setrepdefmode tabname [, owner_on | owner_off | '{UDI}' , [‘on' | 'off' | 'never' | ‘threshold’ [, 'value']]]

UDI – designates the type of DML operation:
- U – update
- D – delete
- I – insert select
You can use any combination and order of the letters U, D, I as a parameter.
on – enables SQL statement replication for the type of DML operation chosen with the UDI parameter. The table must be marked for replication using sp_setreptable. Table-level settings override the database level settings.
off – removes any existing status related to SQL statement replication at the table level.
never – disables SQL statement replication, regardless of the database setting, and regardless of whether the UDI parameter is specified.
threshold – defines the number of rows a DML statement affects. This is the value at which SQL statement replication becomes active. value designates the actual number of rows.

The default threshold is 50 rows, which means that Adaptive Server uses SQL statement replication if the DML statement affects at least 51 rows.

Valid values for the threshold parameter are from 0 – 10000. Setting the threshold to '0' means that the default threshold of '50' is used.

Example 1 In this example, the update and delete statements on table t use SQL statement replication if the statement affects at least 101 rows:

sp_setreptable t, true
go
sp_setrepdefmode t, 'UD', 'on'
go
sp_setrepdefmode t, 'threshold','100'
go

Example 2 This example illustrates how table-level settings override the database level settings. Update, delete, and insert select statements on table t are replicated using SQL statement replication, while select into statements are replicated using traditional replication:

sp_reptostandby pdb, 'ALL'
go
sp_setrepdbmode pdb, 'S', 'on'
go
sp_setreptable t, true
go
sp_setrepdefmode t, 'UDI', 'on'
go

Example 3 In this example, database pdb is configured to use SQL statement replication for delete statements. However, table t uses SQL statement replication only for update and insert select statements.

sp_reptostandby pdb, 'ALL'
go
sp_setrepdbmode pdb, 'D', 'on'
go
sp_setreptable t, true
go
sp_setrepdefmode t, 'UI', 'on'
go

Example 4 The example below retrieves threshold settings:

sp_setrepdbmode t, 'threshold'
go
The replication threshold for table 't' is '90'.

Example 5 The example below illustrates how to set the threshold to the default value:

sp_setrepdbmode t, 'threshold', '0'

The default replication mode is traditional replication, using individual log records.
When the replication mode is set to any combination of UDI, Adaptive Server logs additional information in the transaction log. Sybase Replication Agent sends information for SQL statement replication, as well as atomic changes for each modified row.

You cannot set select into at the object level; the target table does not yet exist.