Configuration parameters affecting individual connections

Table 7-1 displays the configuration parameters that affect database connections. (These configuration parameters affect physical database connections only. For parameters that affect logical database connections, see “Changing parameters affecting logical connections” on page 95 in the Replication Server Administration Guide Volume 2.)

If your system supports network-based security, see “Managing network-based security” for information about security parameters that affect connections.

See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2 for information about parameters for setting up and tuning parallel DSI connections.

See “Connection parameters that affect performance” on page 133 in the Replication Server Administration Guide Volume 2 for a list of configuration parameters that affect performance.

**Table 7-1: Configuration parameters affecting connections**
Parameter (database_param)	Value (value)
batch	The default, “on,” allows command batches to a replicate database. Default: on
batch_begin	Indicates whether a begin transaction can be sent in the same batch as other commands (such as insert and delete). Default: on
command_retry	The number of times to retry a failed transaction. The value must be greater than or equal to 0. Default: 3
db_packet_size	The maximum size of a network packet. During database communication, the network packet value must be within the range accepted by the database. You may change this value if you have Adaptive Server that has been reconfigured. Maximum: 16384 bytes Default: 512-byte network packet for all Adaptive Server databases
disk_affinity	Specifies an allocation hint for assigning the next partition. Enter the logical name of the partition to which the next segment should be allocated when the current partition is full. Values are “partition_name” and “off.” Default: off
dsi_charset_convert	The specification for handling character set conversion. This parameter applies to all data and identifiers to be applied at the DSI in question. The values are: “on” – convert from the primary Replication Server character set to the replicate Replication Server character set; if character sets are incompatible, shut down the DSI with an error. “allow” – convert where character sets are compatible; apply any unconverted updates to the database, as well. “off” – do not attempt conversion. This option is useful if you have different but compatible character sets and do not want any conversion to take place. During subscription materialization, a setting of “off” behaves as if it were “allow.” Default: on
dsi_check_lock_wait	The number of milliseconds before the DSI executor thread executes the rs_thread_check_lock function string, which queries the replicate database about lock status. Default: 3000 milliseconds (3 seconds)
dsi_cmd_batch_size	The maximum number of bytes that Replication Server places into a command batch. Default: 8192 bytes
dsi_cmd_separator	The character that separates commands in a command batch. For example, if you have specified a different separator character and want to change it back to the default character, enter: alter connection to data_server.database set dsi_cmd_separator to '<Return>' Press the Return key, and no other characters, between the two single-quote characters. Default: newline (\n) Pressing the Return key is effective only in an interactive update; it is not applicable to executing a script, such as a DDL generated script. You must update this parameter in an interactive mode. You cannot reset it from within a script.
dsi_commit_check_locks_intrvl	The number of milliseconds (ms) the DSI executor thread waits between executions of the rs_dsi_check_thread_lock function string. Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2. Default: 1000 ms (1 second) Minimum: 0 Maximum: 86,400,000 ms (24 hours)
dsi_commit_check_locks_max	The maximum number of times a DSI executor thread checks whether it is blocking other transactions in the replication database before rolling back its transaction and retrying it. Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2. Default: 400 Minimum: 1 Maximum: 1,000,000
dsi_commit_control	Specifies whether commit control processing is handled internally by Replication Server using internal tables (on) or externally using the rs_threads system table (off). Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2. Default: on
dsi_exec_request_sproc	Turns on or off request stored procedures at the DSI of the primary Replication Server. Default: on
dsi_fadeout_time	The number of seconds of idle time before a DSI connection is closed. A value of -1 specifies that the connection should not fade out. Default: 600 seconds
dsi_ignore_underscore_name	When the transaction partitioning rule is set to name, specifies whether or not Replication Server ignores transaction names that begin with an underscore. Values are “on” and “off.” Default: on
dsi_isolation_level	Specifies the isolation level for transactions. ANSI standard and Adaptive Server supported values are: 0 – ensures that data written by one transaction represents the actual data. 1 – prevents dirty reads and ensures that data written by one transaction represents the actual data. 2 – prevents nonrepeatable reads, prevents dirty reads, and ensures that data written by one transaction represents the actual data. 3 – prevents phantom rows, prevents nonrepeatable reads, prevents dirty reads, and ensures that data written by one transaction represents the actual data. Through the use of custom function strings, Replication Server can support any isolation level the replicate data servers may use. Support is not limited to ANSI standard only. The default value is the current transaction isolation level for the target data server.
dsi_keep_triggers	Specifies whether triggers should fire for replicated transactions in the database. “off” – causes Replication Server to set triggers off in the Adaptive Server database, so that triggers do not fire when transactions are executed on the connection. Use this setting for standby databases. on” – specifies all databases except standby databases. Default: on (except standby databases)
dsi_large_xact_size	The number of commands allowed in a transaction before the transaction is considered to be large. Minimum: 4 Default: 100
dsi_max_cmds_to_log	The number of commands to write into the exceptions log for a transaction. Default: –1 (all commands)
dsi_max_xacts_in_group	Specifies the maximum number of transactions in a group. Larger numbers may improve data latency at the replicate database. Range of values: 1 – 100. Default: 20
dsi_max_text_to_log	The number of bytes to write into the exceptions log for each rs_writetext function in a failed transaction. Change this parameter to prevent transactions with large text, unitext, or image columns from filling the RSSD or its log. Default: –1 (all text, unitext, or image columns)
dsi_num_large_xact_threads	The number of parallel DSI threads to be reserved for use with large transactions. The maximum value is one less than the value of dsi_num_threads. Default: 0
dsi_num_threads	The number of parallel DSI threads to be used. The maximum value is 255. Default: 1
dsi_partitioning_rule	Specifies the partitioning rules (one or more) the DSI uses to partition transactions among available parallel DSI threads. Values are origin, origin_sessid, time, user, name, and none. See also “Partitioning rules: reducing contention and increasing parallelism” on page 156 for detailed information. Default: none
dsi_replication	Specifies whether or not transactions applied by the DSI are marked in the transaction log as being replicated. When dsi_replication is set to “off,” the DSI executes set replication off in the Adaptive Server database, preventing Adaptive Server from adding replication information to log records for transactions that the DSI executes. Since these transactions are executed by the maintenance user and, therefore, usually not replicated further (except if there is a standby database), setting this parameter to “off” avoids writing unnecessary information into the transaction log. dsi_replication must be set to “on” for the active database in a warm standby application for a replicate database, and for applications that use the replicated consolidated replicate application model. Default: on (“off” for standby database in a warm standby application)
dsi_serialization_method	Specifies the method used to maintain serial consistency between parallel DSI threads when applying transactions to a replicate data server. no_wait – specifies that a transaction can start as soon as it is ready—without regard to the state of other transactions. wait_for_commit – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit. wait_for_commit – maintains transaction serialization by instructing the DSI to wait until a transaction is ready to commit before initiating the next transaction (off) or wait until a transaction has committed before initiating the next transaction (on). none – same as wait_for_start. Retained for backward compatibility. single_transaction_per_origin – same as wait_for_start with dsi_partitioning_rule set to origin. Retained for backward compatibility. Default: wait_for_commit
dsi_sql_data_style	This parameter is necessary only for connections to pre-version 12.0 Adaptive Servers. Formats datatypes (particularly date/time, binary, bit and money) to be compatible with: DB2 (“db2”) Lotus Notes (“notes”), or SQL Anywhere, formerly Watcom SQL (“watcom”), or SQL Remote (“sqlremote”) To support Transact-SQL, set this parameter to any value except those shown above. When you are configuring a connection to DB2, also specify the name of the NetGateway using the data_server parameter in the main clause of the alter connection command. When you are configuring a connection to Lotus Notes, SQL Anywhere, or any other ODBC data source, specify the connection as replication_driver_name.odbc_data_source_name. Refer to the Replication Driver for ODBC User’s Guide for more information. Default: “ ” or “sql” (for Adaptive Server)
dsi_sqt_max_cache_size	Maximum SQT (Stable Queue Transaction) interface cache memory for the database connection, in bytes. The default, 0, means the current setting of the sqt_max_cache_size parameter is used as the maximum cache size for the connection. Default: 0
dsi_text_convert_multiplier	Changes the length of text or unitext datatype columns at the replicate site. Use dsi_text_convert_multiplier when text or unitext datatype columns must expand or contract due to character set conversion. Replication Server multiplies the length of primary text or unitext data by the value of dsi_text_convert_multiplier to determine the length of text or unitext data at the replicate site. The value type is float. If the character set conversion involves expanding text or unitext datatype columns, set dsi_text_convert_multiplier equal to or greater than 1.0. If the character set conversion involves contracting text or unitext datatype columns, set dsi_text_convert_multiplier equal to or less than 1.0. Default: 1
dsi_xact_group_size	The maximum number of bytes, including stable queue overhead, to place into one grouped transaction. A grouped transaction is a set of transactions that the DSI applies as a single transaction. A value of –1 means no grouping. Sybase recommends that you set dsi_xact_group_size to the maximum value and use dsi_max_xacts_in_group to control the number of transactions in a group. Maximum: 2,147,483,647 Default: 65,536 bytes
dump_load	Set to “on” at replicate sites only to enable coordinated dump. See “Loading from coordinated dumps” on page 227 in the Replication Server Administration Guide Volume 2 for details. Default: off
exec_cmds_per_timeslice	Specifies the number of LTL commands an LTI or RepAgent Executor thread can process before it must yield the CPU to other threads. The range is 1 to 2, 147, 483, 648. Default: 5
exec_sqm_write_request_limit	Specifies the amount of memory available to the LTI or RepAgent Executor thread for messages waiting to be written to the inbound queue. Default: 16,384 bytes Minimum: 16,384 bytes Maximum: 983,040 bytes
md_sqm_write_request_limit	Specifies the amount of memory available to the Distributor for messages waiting to be written to the outbound queue. The range is 65,536 to 983,040 bytes. Default: 100,000 bytes
md_source_memory_pool	Bytes of memory available for holding pending writes for each source database. The allowed range is 65,536 to 983,040. This configuration parameter affects the source database, not the destination database. It cannot be set with the alter connection command. Default: 100,000 bytes
rep_as_standby	When rep_as_standby is on, table subscriptions replicate tables marked by sp_reptostandby. For rep_as_standby on to succeed, the RepAgent parameters send maint xacts to replicate must be false and send warm standby xacts must be true. Default: off
save_interval	The number of minutes that the Replication Server saves messages after they have been successfully passed to the destination data server. Default: 0 minutes
sub_sqm_write_request_limit	Specifies the memory available to the subscription materialization or dematerialization thread for messages waiting to be written to the outbound queue. The range is 16,384 to 983,040 bytes. Default: 16,384 bytes
use_batch_markers	If use_batch_markers is set to on, the function strings rs_batch_start and rs_batch_end will be executed. This parameter must be set to on only for replicate data servers that require additional SQL translation to be sent at the beginning and end of a batch of commands that are not contained in the rs_begin and rs_commit function strings. Default: off

Table 7-1: Configuration parameters affecting connections

Parameter (database_param)

Value (value)

batch

The default, “on,” allows command batches to a replicate database.

Default: on

batch_begin

Indicates whether a begin transaction can be sent in the same batch as other commands (such as insert and delete).

Default: on

command_retry

The number of times to retry a failed transaction. The value must be greater than or equal to 0.

Default: 3

db_packet_size

The maximum size of a network packet. During database communication, the network packet value must be within the range accepted by the database. You may change this value if you have Adaptive Server that has been reconfigured.

Maximum: 16384 bytes Default: 512-byte network packet for all Adaptive Server databases

disk_affinity

Specifies an allocation hint for assigning the next partition. Enter the logical name of the partition to which the next segment should be allocated when the current partition is full. Values are “partition_name” and “off.”

Default: off

dsi_charset_convert

The specification for handling character set conversion. This parameter applies to all data and identifiers to be applied at the DSI in question. The values are:

“on” – convert from the primary Replication Server character set to the replicate Replication Server character set; if character sets are incompatible, shut down the DSI with an error.
“allow” – convert where character sets are compatible; apply any unconverted updates to the database, as well.
“off” – do not attempt conversion. This option is useful if you have different but compatible character sets and do not want any conversion to take place. During subscription materialization, a setting of “off” behaves as if it were “allow.”

Default: on

dsi_check_lock_wait

The number of milliseconds before the DSI executor thread executes the rs_thread_check_lock function string, which queries the replicate database about lock status.

Default: 3000 milliseconds (3 seconds)

dsi_cmd_batch_size

The maximum number of bytes that Replication Server places into a command batch.

Default: 8192 bytes

dsi_cmd_separator

The character that separates commands in a command batch. For example, if you have specified a different separator character and want to change it back to the default character, enter:

alter connection to data_server.database
set dsi_cmd_separator to '<Return>'

Press the Return key, and no other characters, between the two single-quote characters.

Default: newline (\n)

Pressing the Return key is effective only in an interactive update; it is not applicable to executing a script, such as a DDL generated script. You must update this parameter in an interactive mode. You cannot reset it from within a script.

dsi_commit_check_locks_intrvl

The number of milliseconds (ms) the DSI executor thread waits between executions of the rs_dsi_check_thread_lock function string. Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2.

Default: 1000 ms (1 second)

Minimum: 0

Maximum: 86,400,000 ms (24 hours)

dsi_commit_check_locks_max

The maximum number of times a DSI executor thread checks whether it is blocking other transactions in the replication database before rolling back its transaction and retrying it. Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2.

Default: 400

Minimum: 1

Maximum: 1,000,000

dsi_commit_control

Specifies whether commit control processing is handled internally by Replication Server using internal tables (on) or externally using the rs_threads system table (off). Used with parallel DSI. See “Using parallel DSI threads” on page 145 in the Replication Server Administration Guide Volume 2.

Default: on

dsi_exec_request_sproc

Turns on or off request stored procedures at the DSI of the primary Replication Server.

Default: on

dsi_fadeout_time

The number of seconds of idle time before a DSI connection is closed. A value of -1 specifies that the connection should not fade out.

Default: 600 seconds

dsi_ignore_underscore_name

When the transaction partitioning rule is set to name, specifies whether or not Replication Server ignores transaction names that begin with an underscore. Values are “on” and “off.”

Default: on

dsi_isolation_level

Specifies the isolation level for transactions. ANSI standard and Adaptive Server supported values are:

0 – ensures that data written by one transaction represents the actual data.
1 – prevents dirty reads and ensures that data written by one transaction represents the actual data.
2 – prevents nonrepeatable reads, prevents dirty reads, and ensures that data written by one transaction represents the actual data.
3 – prevents phantom rows, prevents nonrepeatable reads, prevents dirty reads, and ensures that data written by one transaction represents the actual data.

Through the use of custom function strings, Replication Server can support any isolation level the replicate data servers may use. Support is not limited to ANSI standard only.

The default value is the current transaction isolation level for the target data server.

dsi_keep_triggers

Specifies whether triggers should fire for replicated transactions in the database. “off” – causes Replication Server to set triggers off in the Adaptive Server database, so that triggers do not fire when transactions are executed on the connection. Use this setting for standby databases. on” – specifies all databases except standby databases.

Default: on (except standby databases)

dsi_large_xact_size

The number of commands allowed in a transaction before the transaction is considered to be large.

Minimum: 4 Default: 100

dsi_max_cmds_to_log

The number of commands to write into the exceptions log for a transaction.

Default: –1 (all commands)

dsi_max_xacts_in_group

Specifies the maximum number of transactions in a group. Larger numbers may improve data latency at the replicate database. Range of values: 1 – 100.

Default: 20

dsi_max_text_to_log

The number of bytes to write into the exceptions log for each rs_writetext function in a failed transaction. Change this parameter to prevent transactions with large text, unitext, or image columns from filling the RSSD or its log.

Default: –1 (all text, unitext, or image columns)

dsi_num_large_xact_threads

The number of parallel DSI threads to be reserved for use with large transactions. The maximum value is one less than the value of dsi_num_threads.

Default: 0

dsi_num_threads

The number of parallel DSI threads to be used. The maximum value is 255.

Default: 1

dsi_partitioning_rule

Specifies the partitioning rules (one or more) the DSI uses to partition transactions among available parallel DSI threads. Values are origin, origin_sessid, time, user, name, and none.

Default: none

dsi_replication

Specifies whether or not transactions applied by the DSI are marked in the transaction log as being replicated. When dsi_replication is set to “off,” the DSI executes set replication off in the Adaptive Server database, preventing Adaptive Server from adding replication information to log records for transactions that the DSI executes. Since these transactions are executed by the maintenance user and, therefore, usually not replicated further (except if there is a standby database), setting this parameter to “off” avoids writing unnecessary information into the transaction log. dsi_replication must be set to “on” for the active database in a warm standby application for a replicate database, and for applications that use the replicated consolidated replicate application model.

Default: on (“off” for standby database in a warm standby application)

dsi_serialization_method

Specifies the method used to maintain serial consistency between parallel DSI threads when applying transactions to a replicate data server.

no_wait – specifies that a transaction can start as soon as it is ready—without regard to the state of other transactions.
wait_for_commit – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit.
wait_for_commit – maintains transaction serialization by instructing the DSI to wait until a transaction is ready to commit before initiating the next transaction (off) or wait until a transaction has committed before initiating the next transaction (on).
none – same as wait_for_start. Retained for backward compatibility.
single_transaction_per_origin – same as wait_for_start with dsi_partitioning_rule set to origin. Retained for backward compatibility.

Default: wait_for_commit

dsi_sql_data_style

This parameter is necessary only for connections to pre-version 12.0 Adaptive Servers.

Formats datatypes (particularly date/time, binary, bit and money) to be compatible with:

DB2 (“db2”)
Lotus Notes (“notes”), or
SQL Anywhere, formerly Watcom SQL (“watcom”), or
SQL Remote (“sqlremote”)

To support Transact-SQL, set this parameter to any value except those shown above. When you are configuring a connection to DB2, also specify the name of the NetGateway using the data_server parameter in the main clause of the alter connection command. When you are configuring a connection to Lotus Notes, SQL Anywhere, or any other ODBC data source, specify the connection as replication_driver_name.odbc_data_source_name. Refer to the Replication Driver for ODBC User’s Guide for more information. Default: “ ” or “sql” (for Adaptive Server)

dsi_sqt_max_cache_size

Maximum SQT (Stable Queue Transaction) interface cache memory for the database connection, in bytes. The default, 0, means the current setting of the sqt_max_cache_size parameter is used as the maximum cache size for the connection.

Default: 0

dsi_text_convert_multiplier

Changes the length of text or unitext datatype columns at the replicate site. Use dsi_text_convert_multiplier when text or unitext datatype columns must expand or contract due to character set conversion. Replication Server multiplies the length of primary text or unitext data by the value of dsi_text_convert_multiplier to determine the length of text or unitext data at the replicate site. The value type is float.

If the character set conversion involves expanding text or unitext datatype columns, set dsi_text_convert_multiplier equal to or greater than 1.0.
If the character set conversion involves contracting text or unitext datatype columns, set dsi_text_convert_multiplier equal to or less than 1.0.

Default: 1

dsi_xact_group_size

The maximum number of bytes, including stable queue overhead, to place into one grouped transaction. A grouped transaction is a set of transactions that the DSI applies as a single transaction. A value of –1 means no grouping.

Sybase recommends that you set dsi_xact_group_size to the maximum value and use dsi_max_xacts_in_group to control the number of transactions in a group.

Maximum: 2,147,483,647 Default: 65,536 bytes

dump_load

Set to “on” at replicate sites only to enable coordinated dump. See “Loading from coordinated dumps” on page 227 in the Replication Server Administration Guide Volume 2 for details.

Default: off

exec_cmds_per_timeslice

Specifies the number of LTL commands an LTI or RepAgent Executor thread can process before it must yield the CPU to other threads. The range is 1 to 2, 147, 483, 648.

Default: 5

exec_sqm_write_request_limit

Specifies the amount of memory available to the LTI or RepAgent Executor thread for messages waiting to be written to the inbound queue.

Default: 16,384 bytes Minimum: 16,384 bytes Maximum: 983,040 bytes

md_sqm_write_request_limit

Specifies the amount of memory available to the Distributor for messages waiting to be written to the outbound queue. The range is 65,536 to 983,040 bytes.

Default: 100,000 bytes

md_source_memory_pool

Bytes of memory available for holding pending writes for each source database. The allowed range is 65,536 to 983,040.

This configuration parameter affects the source database, not the destination database. It cannot be set with the alter connection command.

Default: 100,000 bytes

rep_as_standby

When rep_as_standby is on, table subscriptions replicate tables marked by sp_reptostandby.

For rep_as_standby on to succeed, the RepAgent parameters send maint xacts to replicate must be false and send warm standby xacts must be true.

Default: off

save_interval

The number of minutes that the Replication Server saves messages after they have been successfully passed to the destination data server.

Default: 0 minutes

sub_sqm_write_request_limit

Specifies the memory available to the subscription materialization or dematerialization thread for messages waiting to be written to the outbound queue. The range is 16,384 to 983,040 bytes.

Default: 16,384 bytes

use_batch_markers

If use_batch_markers is set to on, the function strings rs_batch_start and rs_batch_end will be executed.

This parameter must be set to on only for replicate data servers that require additional SQL translation to be sent at the beginning and end of a batch of commands that are not contained in the rs_begin and rs_commit function strings.

Default: off