Set Thresholds and Operations at Session Level

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 is valid only 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 automatically set the replication threshold 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 in to Adaptive Server, the SQL statement replication threshold for the session is set to 23.