sp_config_rep

Changes or displays the configuration parameters for the RepAgent thread for an Adaptive Server database.

Syntax

sp_config_rep_agent [dbname
 [, {'enable', 'repserver_name', 'repserver_username', 'repserver_password'} |
'disable'[, 'preserve secondary truncpt'] |
'rs servername'[, 'repserver_name'] |
'rs username'[, 'repserver_username'] |
'rs password'[, 'repserver_password'] |
'scan batch size'[,'no_of_qualifying_log_records'] | 
'scan timeout'[, 'scan_timeout_in_seconds'] |
'retry timeout'[, 'retry_timeout_in_seconds'] |
'skip ltl errors'[, 'true' | 'false'] |
'batch ltl'[, 'true' | 'false'] |
'send warm standby xacts'[, 'true' | 'false'] |
'send buffer size’[, ‘2K’ | ‘4K’ |’8K’ | ‘16K’] |
'connect dataserver'[, 'connect_dataserver_name'] |
'connect database'[, 'connect_database_name'] |
'send maint xacts to replicate'[, 'true' | 'false'] |
'send structured oqids’[, ‘true’ | ‘false’] |
'short ltl keywords'[, 'true' | 'false'] |
'security mechanism'[, 'mechanism_name'] |
'unified login'[, 'true' | 'false'] |
'mutual authentication'[, 'true' | 'false'] |
'msg confidentiality'[, 'true' | 'false'] |
'msg integrity'[, 'true' | 'false'] |
'msg replay detection'[, 'true' | 'false'] |
'msg origin check'[, 'true' | 'false'] |
'msg out-of-sequence check'[, 'true' | 'false'] |
'skip unsupported features'[, 'true' | 'false'] |
'schema cache growth factor'[, 'growth_factor_value'] |
'ha failover'[, 'true' |'false'] |
'data limits filter mode'[, 'off' | 'stop' | 'skip' |'truncate'] |
'priority'[, 'priority_value'] |
'startup delay’[, 'delay_value'] |
'net password encryption’[, ‘true’ | ‘false’] |
'cluster instance name'[, 'coordinator' | 'instance_name'] |
'bind to engine'[, engine_number] |
'ltl batch size'[, ltl_batch_size] |
'ltl metadata reduction', {'true' |'false'}
'multithread rep agent', {'true' | 'false'}
'number of send buffers', {'num_of_send_buffers'} |
'max number replication paths', {'max number replication paths value'}
'ddl path for unbound objects', {'all' | 'default'} |
'auto start', {'true' | 'false'}]

Parameters

dbname – The name of the database for which you want to configure RepAgent.
enable – Marks the database as using RepAgent and sets the secondary truncation point to valid.
This command encodes the Replication Server password and inserts the Replication Server name, Replication Server user, and encoded password into the sysattributes table of the specified database.
repserver_name – The name of the Replication Server to which RepAgent connects and transfers log transactions.
repserver_username – The user name that RepAgent thread uses to connect to Replication Server.
repserver_password – The password that RepAgent uses to connect to Replication Server.
If network-based security is enabled and you want to establish unified login, you must specify NULL for repserver_password when enabling RepAgent at the database.
rs servername [, repserver_name] – The new or existing name of the Replication Server to which RepAgent connects and transfers log transactions.
rs username[, repserver_username] – The new or existing user name that RepAgent thread uses to connect to Replication Server.
rs password[, repserver_password] – The new or existing password that RepAgent uses to connect to Replication Server.
disable – Unmarks the database as using RepAgent. Use preserve secondary truncpt to retain the secondary truncation point. The default sets the secondary truncation point to IGNORE; that is, it disables it.
Use disable only when downgrading the Replication Server to an earlier version or changing the primary database to another status. This command truncates all RepAgent entries in the sysattributes table.
scan batch size[, 'no_of_qualifying_records’] – Specifies the maximum number of log records to send to Replication Server in each batch. When the maximum number of records is met, RepAgent asks Replication Server for a new secondary truncation point. The default is 1000 records.
scan timeout[, 'scan_timeout_in_seconds'] – Specifies the number of seconds that RepAgent sleeps once it has scanned and processed all records in the transaction log and Replication Server has not yet acknowledged previously sent records by sending a new secondary truncation point. RepAgent again queries Replication Server for a secondary truncation point after scan timeout seconds. The default is 15 seconds.
RepAgent continues to query Replication Server until Replication Server acknowledges previously sent records either by sending a new secondary truncation point or extending the transaction log.

If Replication Server has acknowledged all records and no new transaction records have arrived at the log, RepAgent sleeps until the transaction log is extended.
retry timeout[, 'retry_timeout_in_seconds'] – Specifies the number of seconds RepAgent sleeps before attempting to reconnect to Replication Server after a retryable error or when Replication Server is down. The default is 60 seconds.
skip ltl errors – Specifies whether RepAgent ignores errors in LTL commands. This option is normally used in recovery mode. When set to “true,” RepAgent logs and then skips errors returned by the Replication Server for distribute commands. When set to “false,” RepAgent shuts down when these errors occur. The default is false.
batch ltl – Specifies whether RepAgent sends LTL commands to Replication Server in batches or one command at a time. When set to “true,” the commands are sent in batches. The default is false.
send warm standby xacts – Specifies whether RepAgent sends maintenance user transactions, schema changes, and system transactions to the warm standby database. This option should be used only with the RepAgent for the current active database in a warm standby configuration. The default is false.
send buffer size[, '2K', '4K', '8K', '16K'] – Controls the size of the send buffer that RepAgent uses to communicate with Replication Server. Increasing the size of the send buffer reduces the number of times RepAgent communicates with Replication Server, but increases the amount of memory used.
The default value is 2K.
connect dataserver[, 'connect_dataserver_name'] – Specifies the name of the data server RepAgent uses when connecting to Replication Server in recovery mode. This is the data server name RepAgent uses for the connect source command; it is normally the data server for the primary database.
connect database[, 'connect_database_name'] – Specifies the name of the temporary database RepAgent uses when connecting to Replication Server in recovery mode. This is the database name RepAgent uses for the connect source command; it is normally the primary database.
send maint xacts to replicate – Specifies whether RepAgent should send records from the maintenance user to the Replication Server for distribution to subscribing sites. The default is “false.”
send structured oqids – Specifies whether RepAgent sends origin queue IDs (OQIDs) as structured tokens, which saves space in the LTL and thus improves throughput, or as binary strings. The default value is “false.”
short Itl keywords – Specifies whether RepAgent sends an abbreviated form of LTL to Replication Server, requiring less space and reducing the amount of data sent. The default value is “false.”
security mechanism [, ‘mechanism_name'] – Specifies the network-based security mechanism RepAgent uses to connect to Replication Server.
unified login – When a network-based security system is enabled, specifies whether RepAgent seeks to connect to other servers with a security credential or password. The default is “false.”
mutual authentication – Specifies whether RepAgent should require mutual authentication checks when connecting to Replication Server. The default is “false.” This option is not implemented.
msg confidentiality – Specifies whether to encrypt all messages sent to Replication Server. The default is “false.”
msg integrity – Specifies whether all messages exchanged with Replication Server should be checked for tampering. The default is “false.”
msg replay detection – Specifies whether messages received from Replication Server should be checked to make sure they have not been intercepted and replayed. The default is “false.”
msg origin check – Specifies whether to check the source of each message received from Replication Server. The default is “false.”
msg out-of-sequence check – Specifies whether to check the sequence of messages received from Replication Server. The default is “false.”
skip unsupported features – Instructs RepAgent to skip log records for Adaptive Server features unsupported by the Replication Server. This option is normally used if Replication Server is a lower version than Adaptive Server. The default is “false.”
schema cache growth factor[, 'growth_factor_value'] – Controls the duration of time table or stored procedure schema can reside in the RepAgent schema cache before expiring. Larger values mean a longer duration and require more memory. Range is 1 to 10. The default is 1.
ha failover – Specifies whether, when Sybase Failover has been installed, RepAgent automatically starts after server failover. The default is “true.”
data limits filter mode[, 'off' | 'stop' | 'skip' | 'truncate'] – Specifies how RepAgent handles log records containing new, wider columns and parameters, or larger column and parameter counts, before attempting to send them to Replication Server.
- off – RepAgent allows all log records to pass through.
- stop – RepAgent shuts down if it encounters log records containing wide data.
- skip – RepAgent skips log records containing wide data and posts a message to the error log.
- truncate – RepAgent truncates wide data to the maximum the Replication Server can handle.
Warning! Sybase recommends that you do not use the data_limits_filter_mode, off setting with Replication Server version 12.1 or earlier as this may cause RepAgent to skip or truncate wide data, or to stop.

The default value of data limits filter mode depends on the Replication Server version number. For Replication Server versions 12.1 and earlier, the default value is “stop.” For Replication Server versions 12.5 and later, the default value is “off.”
priority[, 'priority_value'] – Sets relative priority values for individual RepAgents. The value of priority ranges from 0 to 7, where a value of 0 indicates highest priority. The default value is 5.
Note: Sybase recommends that you do not set the value of priority to 0, as it may negatively impact performance.
startup delay[, 'delay_value'] – This delays the automatic start-up of RepAgent by a specified duration to allow Replication Server to be running before RepAgent attempts to connect to Replication Server. By default, RepAgent starts without any delay during automatic start-up. Setting a value in seconds results in a delay in RepAgent start-up by the specified number of seconds. Default: 0 (zero) seconds.
net password encryption – Specifies whether connections with a remote server are to be initiated with a client-side password encryption handshake or with the usual unencrypted password handshake sequence. Default: 'true'.
cluster instance name[, 'coordinator' | ‘instance_name’] – Controls the instance where RepAgent is started. By default, RepAgent starts at the instance with the coordinator role. However, you can configure RepAgent to start at any declared instance in the cluster.
bind to engine[, engine_number] – Restricts the RepAgent execution to the engine number specified. You can improve the RepAgent performance by running RepAgent on a dedicated or less utilized engine. The value of engine_number ranges from -1 to (max online engines - 1). Its default is -1, which means RepAgent can execute on any engine.
Note: The bind to engine clause does not restrict other user tasks or system tasks from running on the specified engine number.
ltl batch size[, ltl_batch_size] – Sets the maximum size, in bytes, of LTL data that a RepAgent can send to the Replication Server in a batch. The value of ltl_batch_size ranges from 16,384 to 2,147,483,647 bytes. Its default value is 16,384 bytes.
You can improve RepAgent performance by increasing the LTL batch size to a bigger number. At the end of each LTL batch, RepAgent checks for errors in the previous batch. Increasing the LTL batch size, decreases the number of times RepAgent checks for LTL errors.
ltl metadata reduction –
Set to true to enable table metadata reduction in RepAgent and automatically enable the Executor command cache in Replication Server. The default is false.
multithread rep agent –
Set to true enable multithreaded RepAgent which uses separate threads for the RepAgent scanner and sender activities, and is a prerequisite for building multiple primary replication paths. The default is false.
max number replication paths –
Sets the maximum number of paths that you allow RepAgent to use to replicate data out of the primary database through multiple replication paths. RepAgent generates one RepAgent sender thread for each RepAgent path.

Range of valid values: 1 to the value of MAXINT which is 2,147,483,647 paths. The default is 1.

If max number replication paths is less than the number of paths with replication objects bound to the paths, RepAgent reports an error and terminates.

To build multiple primary replication paths, enable multithreaded RepAgent with the multithread rep agent RepAgent parameter.
number of send buffers –
Sets the maximum number of send buffers that the scanner and sender tasks of multithreaded RepAgent can use when you configure for multi-path replication.

Range of valid values: 50 to the value of MAXINT which is 2,147,483,647 buffers. The default is 50 buffers.

To build multiple primary replication paths, enable multithreaded RepAgent with the multithread rep agent RepAgent parameter.
ddl path for unbound objects – Send SQL and DDL statements for unbound objects over all paths or the default path in a multi-path replication environment. The default setting is all.
auto start – Specifies whether RepAgent automatically starts when Adaptive Server restarts and recovers the database. Set to true for RepAgent to start automatically when you restart Adaptive Server. The default is false.

Examples

Example 1 – Enables RepAgent for the pubs2 database. RepAgent connects to repsvr1 with repusr1 and password reppwd1:
```
sp_config_rep_agent pubs2, 'enable', 'repsvr1',
  'repusr1', 'reppwd1'
```

Example 2 – Displays configuration information for the pubs2 database:

sp_config_rep_agent pubs2

Parameter Name               Default    Config Value     Run Value
----------------------      ---------  ---------------  -------------
priority                       5            5                5
trace flags                    0            0                0
scan timeout                   15           15               15
retry timeout                  60           60               60
rs username                    n/a          rs1_user         rs1_user
batch ltl                      true         true             true
rs servername                  n/a          rs1              rs1
send buffer size               2k           4k               4k
trace log file                 n/a          n/a              n/a
connect database               n/a          n/a              pdb1
connect dataserver             n/a          n/a              pds1
scan batch size                1000         1000             1000
security mechanism             n/a          n/a              n/a
msg integrity                  false        false            false
unified login                  false        false            false
schema cache growth factor     1            1                1
skip ltl errors                false        false            false
msg origin check               false        false            false
short ltl keywords             false        false            false
msg confidentiality            false        false            false
data limits filter mode        stop         stop             stop
msg replay detection           false        false            false
mutual authentication          false        false            false
send structured oqids          false        false            false
send warm standby xacts        false        false            false
msg out-of-sequence check      false        false            false
skip unsupported features      false        false            false
send maint xacts to replicate  false        false            false
net password encryption        true         true             true
startup delay                  0            5                5
cluster instance name     coordinator  coordinator     coordinator
bind to engine                 -1           2                2
ltl batch size                 16384        16384            16384

Example 3 – Displays values for a specific parameter.

sp_config_rep_agent pubs2, 'scan batch size'

Parameter Name             Default      Config Value     Run Value
----------------------     ---------    ---------------  -------------
scan batch size              1000       1000             1000

Example 4 – Sets scan_timeout to 60 seconds for the pubs2 database:
```
sp_config_rep_agent pubs2, 'scan timeout', '60'
```
Example 5 – Configures RepAgent to wait 50 seconds before starting:
```
sp_config_rep_agent pubs2, 'startup delay', '50'
```

Example 6 – Starts a disabled RepAgent on ASE1:

1> sp_config_rep_agent pdb, 
    'cluster instance name','ASE1'
2> go

Parameter Name             Default       Config Value     Run Value
----------------------     -----------   ---------------  -------------
cluster instance name      coordinator   ASE1             ASE1

Usage

Use sp_config_rep_agent to configure RepAgent for Adaptive Server databases.
Enable RepAgent in this way:
- sp_addserver – Identifies the Adaptive Server for RepAgent. You need to do this only once per screen.
- sp_configure 'enable rep agent thread' – Configures the data server for RepAgent. You need to do this only once per screen.
- sp_config_rep_agent – Configures the database for RepAgent.
Refer to the Adaptive Server Enterprise Reference Manual for more information about sp_addserver.
After you configure the parameters using sp_config_rep_agent, you must restart RepAgent using sp_start_rep_agent for the new parameters to take effect.
If you execute sp_config_rep_agent without parameters, Adaptive Server displays the default, configured, and runtime values for all databases that are enabled for RepAgent.

If you only enter dbname, Adaptive Server displays the default, configured, and runtime values for the specified database.
Properties specified by sp_config_rep_agent are stored in the sysattributes table of the database and have an attribute class of RA.
Use sp_config_rep_agent to set the RepAgent configuration parameters after you have enabled RepAgent at the data server using sp_configure.
repserver_user must have connect source permission.

Configuring Network-Based Security

Note: Network-based security for RepAgent is enabled with sp_configure at the Adaptive Server. See the Adaptive Server Enterprise System Administration Guide for more information.

A security mechanism may not support all security properties. Verify the properties of a security mechanism by executing admin security_property at the Replication Server. For more information, see admin security_property.
The security mechanism enabled for the RepAgent must be the same as that enabled for the Replication Server. Security settings at the RepAgent and the Replication Server must be compatible.
If RepAgent Setting Is

Setting at Replication Server Can Be
“true”

“required”, or

“not required”
“false”

“not required”
If unified_login is “true,” you must specify the rs_password parameter as NULL when RepAgent is enabled at the database.
If you specify one or more security settings, but do not specify a security mechanism, Adaptive Server initializes the default mechanism, the first entry in the SECURITY section in $SYBASE/$SYBASE_ASE/config/libtcl.cfg.

If RepAgent Setting Is	Setting at Replication Server Can Be
“true”	“required”, or “not required”
“false”	“not required”

Permissions

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