Use set repthreshold to set thresholds at the session-level.
The threshold that you define at the session level overrides the threshold you set at the table or the database level. The threshold set at the table level overrides the threshold set at the database level.
These examples show how to set the threshold at the session, database, and table levels, and at the same time define operations at the different levels.
Example 1 This example shows how to define the threshold at the session-level to 23, in the absence of any threshold setting at the database and table-levels or to override the threshold settings at the table and database levels:
set repthreshold 23 go
Example 2 This example shows how to reset the threshold to the default of 50, at the session-level:
set repthreshold 0 go
Example 3 This example shows how to set a different threshold at the database and table levels for the pubs2 database and table1 table, and then have a different operation defined for this session only:
Reset the threshold at the database-level to the default 50 rows:
sp_setrepdbmod 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 DML operations affect more than 1,000 rows:
sp_setrepdefmode table1, ‘threshold’, ‘1000’ go
Enable SQL statement replication only for update operations on any table and only for this session. This overrides the database-level setting in step 2:
set repmode on ‘u’ go
Example 4 You can invoke set reptheshold within an Adaptive Server stored procedure. This example shows how to create the set_rep_threshold_23 stored procedure and invoke it within the my_proc stored procedure:
Create the set_rep_threshold_23 stored procedure:
create procedure set_rep_threshold_23 as set repthreshold 23 update my_table set my_col = 2 (statement 2) go
Create the my_proc stored procedure:
create procedure my_proc as update my_table set my_col = 1 (statement 1) exec set_rep_threshold_23 update my_table set my_col = 3 (statement 3) go
Execute my_proc to invoke set_repthreshold_23:
exec my_proc go
Within the my_proc stored procedure, statement 1 executes first with a threshold of 50. Statement 2 executes next with a threshold of 23. Statement 3 executes next with a threshold of 50, because the set repthreshold 23 command in only valid while executing the set_rep_threshold_23 procedure..
Example 5 The session-level threshold is exportable. Therefore, you can set the export_options setting to ‘on’ for a procedure, and set the SQL statement replication threshold, so that procedures in the outer scope use the SQL statement replication threshold set by the stored procedure:
Create the set_repthreshold_23 stored procedure and set export_options on:
create procedure set_repthreshold_23 as set repthreshold 23 (statement 4) set export_options on update my_table set my_col = 2 (statement 2) go
Create the my_proc stored procedure:
create procedure my_proc as update my_table set my_col = 1 (statement 1) exec set_rep_threshold_23 update my_table set my_col = 3 (statement 3) go
Execute my_proc to invoke set_repthreshold_23:
exec my_proc go
Statement 1 executes first, with a threshold of 50. Statement 2 executes next with a threshold of 23. Statement 3 executes next with a threshold of 23, because the scope of the set repthreshold 23 command is the scope of the session.
Example 6 You can create a login trigger to set the replication threshold automatically for a specific login ID.
Create the threshold stored procedure with a threshold setting of 23 and enable export:
create proc threshold as set repthreshold 23 set export_options on go
Instruct Adaptive Server to automatically run the theshold stored procedure when user “Bob” logs in:
sp_modifylogin Bob, ‘login script’, threshold go
When Bob logs into Adaptive Server, the SQL statement replication threshold for the session is set to 23.
