Use session option set repmode to set replication mode to SQL statement replication. You can specify session-level settings either during login by using a login trigger, or at the beginning of a batch. Session settings override both database-level and object-level settings.
Use set repmode on to enable SQL statement replication for the DML operation specified, for the duration of the session. Use set repmode off to remove all SQL statement replication settings at the session level.
For example, to replicate only select into and delete as SQL statements for the duration of the session, use:
set repmode on 'DS'
The set options are active for the duration of the session. Options that you set inside a stored procedure are reverted to the default values when the stored procedure finishes.
When you set options inside a login trigger, the option
settings are maintained after the trigger has finished executing.
Executing set repmode on enables SQL statement replication only if session-level option set replication on is set. This example does not enable SQL statement replication:
set replication off go set repmode on 'S' go
This example enables SQL statement replication:
sp_reptostandby pdb, 'ALL' go set repmode on 'S' go
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. See “Setting SQL statement replication threshold”.
This example shows how to define the threshold at the session-level as 1000 rows:
set repmode ‘threshold’, ‘1000’ go
See Chapter 5, “Adaptive Server Commands and System Procedures” in the Replication Server Reference Manual for details on the syntax and usage.
