Enable SQL Statement Replication at the Table Level

Use sp_setrepdefmode to configure SQL statement replication at the table-level. Table-level settings override database-level settings.

sp_setrepdefmode includes options to:

Enable or disable SQL statement replication for specific DML operations
Configure the threshold that must be reached to activate SQL statement replication

The DML operations that apply to SQL statement replication include:

U – update
D – delete
I – insert select

For example, to enable SQL statement replication for update, delete, and insert select operations on table t, use:

sp_setrepdefmode t, 'UDI', 'on'
go

When a user executes deletes, updates, or insert select DML statements on table t, Adaptive Server logs additional information for SQL statement replication. RepAgent reads the log and sends both individual log records and the information needed by Replication Server to build the SQL statement.

The threshold parameter defines the minimum number of rows that a DML statement must affect, to activate SQL statement replication. The default threshold is 50 rows, which means that Adaptive Server automatically uses SQL statement replication if the DML statement affects at least 51 rows.

For example, to set the threshold to 100, use:

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

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

See Replication Server Reference Manual > Adaptive Server Commands and System Procedures > sp_setrepdefmode.

Note: You cannot configure a select into operation at the table level because the target table does not yet exist.