sp_setrepdefmode

Description

Changes or displays the owner status of tables marked for replication, and enables or disables table-level SQL statement replication for a specific DML operation.

Syntax

sp_setrepdefmode table_name [, 'owner_on' | 'owner_off' | 
   ‘SQLDML_option [SQLDML_option [ ...]]’ [, ‘on’ | ‘off’ | ‘never’ ] | 
   ‘threshold’, ‘value’]

SQLDML_option ::= { U | D | I }

Parameters

table_name: The name of a table in the current database that has been marked for replication with sp_setreptable.

owner_on: Changes the owner status of the table so the table name and owner name are considered when the table is marked for replication. Enables replication of multiple tables of the same name with different owners.

owner_off: Changes the owner status of the table so that only the table name is considered when the table is marked for replication.

SQLDML_option: Any of these DML operations:

U – update

D – delete

I – insert select

When the table replication mode is set to any combination of UDI the RepAgent sends additional information to enable SQL statement replication for the specified DML operation.

on: Enables SQL replication of the DML operation specified.

off: Removes the table-level replication settings of SQL statements, whether or not the statements are specified in option; the database-level replication settings are followed.

never: Disables SQL statement replication, regardless of the database setting, and regardless of whether the UDI parameter is specified.

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

Examples

Example 1

Enables SQL statement replication for update, delete and insert select operations on table t:

1> sp_setrepdefmode t, 'UDI', 'on'
2> go

Example 2

Sets the threshold to 10. Adaptive Server will use SQL replication on table t if the DML statement affects at least 11 rows:

sp_setrepdefmode t, 'threshold', '10'

Example 3

Displays the SQL replication settings and the owner status of table rs_ticket_history:

1> sp_setrepdefmode rs_ticket_history,'udi'
2> go

The replication status for 'rs_ticket_history' is
currently  owner_off, 'udi'.
The replication threshold for table 'rs_ticket_history'
is '0'.
(return status = 0)

Example 4

Sets the threshold to the default value:

sp_setrepdbmode t, 'threshold', '0'

Usage

Use sp_setrepdefmode with RepAgent-enabled Adaptive Server databases.
If sp_setrepdefmode is executed with the table name only, it displays the SQL replication settings and owner status of the table.
Use sp_setrepdefmode to change the mode of the table. You cannot change the owner mode of tables with sp_setreptable.
If the owner_off option is supplied and the current mode of the table is “owner on,” sp_setrepdefmode checks that the table name is unique among all replicated tables in owner off mode. If the name is unique, sp_setrepdefmode changes the table mode to owner off. If the name is not unique, the procedure fails.
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.
See “sp_setrepdefmode” in the “System Changes” chapter in the Adaptive Server Enterprise 15.0.3 New Features Guide for more information.

Permissions

sp_setrepdefmode requires “sa” or “dbo” permission or replication_role.