set repthreshold

Description

Specifies the minimum number of rows that a replicated SQL statement must impact before SQL statement replication is activated for the session.

Syntax

set repthreshold value

Parameters

value: Specifies the minimum number of rows that a replicated SQL statement must impact before SQL statement replication is activated for the session.

Examples

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

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 4

This example shows how to make the session-level threshold 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 50, because the scope of the set repthreshold 23 command is the scope of the session.

Example 5

You can create login triggers 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 threshold 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.

Usage

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 invoke set reptheshold within an Adaptive Server stored procedure.
The session-level threshold is exportable. Therefore, you can set the export_options setting ‘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
You can set the session-level threshold either at login by using a “login trigger”, or at the beginning of a batch. Your session settings overwrites the table or database settings.
Session-level thresholds are active only for the duration of the session. When you set the threshold inside a stored procedure or a trigger, the settings are reverted back to the table-level or database-level settings when the stored procedure or trigger execution terminates.
The threshold set at the session-level overrides the threshold at the table-level and database-level, and the theshold set for any table overrides the threshold set at the database-level.