Configuration Parameters Affecting Physical Database Connections

Use alter connection with the parameter to change the attributes of a single connection or configure replication server to change the attribute of the parameter for all connections to Replication Server.

Configuration Parameters Affecting Database Connections

Parameter (database_param)

Value (value)

batch

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

Default: “on” for ASE and “off” for non-ASE databases.

See Replication Server Administration Guide Volume 2 > Customize Database Operations > Manage Function Strings > Command Batching for Non-ASE Servers.

batch_begin

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

Default: on

See Replication Server Administration Guide Volume 2 > Customize Database Operations > Manage Function Strings > Command Batching for Non-ASE Servers.

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

deferred_name_resolution

Enable deferred name resolution in Replication Server to support deferred name resolution in Adaptive Server.

You must ensure that deferred name resolution is supported in the replicate Adaptive Server before you enable deferred name resolution support in Replication Server.

Default: off

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_alt_writetext

Controls how large object updates are sent to the replicate database. Values are:
  • dcany – generates a writetext command that includes primary key columns. This setting prevents full table scans when populating non-ASE replicate databases using DirectConnect Anywhere™ as an interface.

  • off (default) – generates an Adaptive Server writetext command that includes a text pointer.

dsi_bulk_copy

Turns the bulk copy-in feature on or off for a connection. If dynamic_sql and dsi_bulk_copy are both on, Replication Server applies bulk copy-in when appropriate and uses dynamic SQL if Replication Server cannot use bulk copy-in.

Default: off.

dsi_bulk_threshold

The number of consecutive insert commands in a transaction that, when reached, triggers Replication Server to use bulk copy-in. When Stable Queue Transaction (SQT) encounters a large batch of insert commands, it retains in memory the number of insert commands specified to decide whether to apply bulk copy-in. Because these commands are held in memory, Sybase suggests that you do not configure this value much higher than the configuration value for dsi_large_xact_size.

Replication Server uses dsi_bulk_threshold for Real-time loading (RTL) replication to Sybase IQ and High volume adaptive replication (HVAR) to Adaptive Server. If the number of commands for an insert, delete, or update operation on one table is less than the number you specify after compilation, RTL and HVAR use language instead of bulk interface.

Minimum: 1

Default: 20

Note: You must set dsi_compile_enable to ‘on’ to use dsi_bulk_threshold.

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” (default) – 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.”

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_prefetch

Allows DSI to pre-build the next batch of commands while waiting for the response from data server, and therefore improves DSI efficiency. If you also tune your data server to enhance performance, it is likely that you will gain an additional performance increase when you use this feature.

Default: off

When you set dsi_compile_enable to ‘on’, Replication Server ignores what you set for dsi_cmd_prefetch.

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)

Note: 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_command_convert

Specifies how to convert a replicate command.

A combination of these operations specifies the type of conversion:

  • d – delete

  • i – insert

  • u – update

  • t - truncate

  • none – no operation

Combinations of operations for dsi_command_convert include i2none, u2none, d2none, i2di, t2none, and u2di.

You must type the number “2”. The operation before conversion precedes the “2” and the operations after conversion are after the “2”. For example:

  • d2none – do not replicate the delete command.

  • i2di,u2di – convert both insert and update to delete followed by insert, which is equivalent to an autocorrection.

  • t2none – do not replicate truncate table command.

Default: None.

You can also configure this parameter at the table-level.

For setting, use alter connection for database-level, or alter connection with the for replicate table named clause for table-level configuration.

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 Replication Server Administration Guide Volume 2 > Performance Tuning > Use Parallel DSI Threads.

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 Replication Server Administration Guide Volume 2 > Performance Tuning > Use Parallel DSI Threads.

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 Replication Server Administration Guide Volume 2 > Performance Tuning > Use Parallel DSI Threads.

Default: on

dsi_compile _enable

Enables or disables RTL or HVAR at the server-level, database-level, or table-level.

For setting, use configure replication server for server-level, alter connection for database-level, or alter connection with the for replicate table named clause for table-level configuration.

Default:

  • off – server and database-level. Replication Server uses continuous log order row by row change replication.

  • on – table-level

Set dsi_compile_enable to ‘off’ for an affected table if replicating new row changes causes problems, such as when there is a trigger on the table which requires all the operations on that table to be replicated in log order, and therefore compilation is not allowed.

Note: Set dsi_compile _enable to ‘on’ at the server- or database-level before you set dsi_compile_enable to ‘on’ at the table-level.

When you set dsi_compile_enable to ‘on’, Replication Server ignores what you set for replicate_minimal_columns and dsi_cmd_prefetch.

After you execute dsi_compile_enable at the server, database, or table-level, suspend and resume the connection.

See Replication Server Administration Guide Volume 2 > Performance Tuning > Advanced Services Option > High Volume Adaptive Replication to Adaptive Server for HVAR.

See Replication Server Heterogeneous Replication Guide > Sybase IQ as Replicate Data Server for RTL.

License: Separately licensed under the Advanced Services Option. See the Replication Server Administration Guide Volume 2 > Performance Tuning > Advanced Services Option.

dsi_compile_max_cmds

Specifies, in number of commands, the maximum size of a group of transactions. When HVAR or RTL reaches the maximum group size for the current group that it is compiling, HVAR or RTL starts a new group.

If there is no more data to read, and even if the group does not reach the maximum number of commands, HVAR or RTL completes grouping the current set of transactions into the current group.

For setting, use configure replication server for server-level or alter connection for database-level.

Minimum: 100

Default: 10,000

Note: You must set dsi_compile_enable to ‘on’ to use dsi_compile_max_cmds.

dsi_dataserver_make

Specifies the data server type that contains the replicate database that you want to connect to.

Possible values are: ASE, IQ, and ORA.

Use dsi_dataserver_make and dsi_connector_type to identify the connector that is associated with the connection.

Set to IQ to replicate to Sybase IQ. Set to ASE to replicate to Adaptive Server, and ORA to replicate to Oracle.

You can configure dsi_dataserver_make at the database level.

If you do not specify this parameter, Replication Server deduces the data server type from the function-string class name of the database connection.

If the functions-string class is customized, Replication Server cannot deduce the data server type and defaults to ‘ASE’.

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.

Default: 100

Minimum: 4

Maximum: 2,147,483,647

This parameter is ignored when dsi_compile_enable is turned on.

dsi_max_cmds_in_batch

Defines maximum number of source commands for which output commands can be batched.

You must suspend and resume the connection for any change in the parameter to take effect.

Range: 1 – 1000

Default: 100

dsi_max_cmds_to_log

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

Default: -1 (all commands)

Valid values: 0 to 2147483647

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 – 1000.

Default: 20

This parameter is ignored when dsi_compile_enable is turned on.

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_non_blocking_commit

Specifies the number of minutes to extend the period of time Replication Server saves messages after a commit. Range of values: 0 – 60 minutes.

Default: 0 – non-blocking commit is disabled.

Enable this parameter to improve replication performance when the delayed commit feature is available in Adaptive Server 15.0 and later or the equivalent feature is available in Oracle 10g v2.

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 Replication Server Administration Guide Volume 2 > Performance Tuning > Use Parallel DSI Threads > Partitioning Rules: Reducing Contention and Increasing Parallelism for detailed information.

Default: none

This parameter is ignored when dsi_compile_enable is turned on.

dsi_proc_as_rpc

Specifies how Replication Server applies stored procedure replication.
  • Set on to use remote procedure call (RPC) calls.
  • Set off to use language calls.

Default: off

When the replicate database is Adaptive Server, dsi_proc_as_rpc can be on or off.

When the replicate database is Oracle:
  • Set on if you use ExpressConnect for Oracle (ECO). ECO only supports stored procedure replication using RPC. By default, Replication Server sets dsi_proc_as_rpc on if you use one of the Oracle ECO connection profiles when you create the connection to the Oracle database from Replication Server. See Replication Server Options 15.5 > Installation and Configuration Guide ExpressConnect for Oracle 15.5 > Configuring ExpressConnect for Oracle.
  • Set off if you use ECDA Option for Oracle. ECDA does not support RPC for stored procedure replication.

dsi_quoted_identifier

Enables or disables quoted identifier support in the DSI. Values are on to enable and off to disable.

Default: off

Quoted identifiers are object names that contain special characters such as spaces and nonalphanumeric characters, start with a character other than an alphabetic character, or that correspond to a reserved word, and need to be enclosed in double quote characters to be parsed correctly. Enable this parameter to:

  1. Create or modify a connection that allows quoted identifiers to be forwarded to data servers.

    Use the create connection or the alter connection command to set dsi_quoted_identifier “on” or “off”. Use the admin config command to check the value of dsi_quoted_identifier.

  2. Mark identifiers in a replication definition as quoted.

The quoted identifier feature is not supported in mixed version environments. For replication of a quoted identifier to succeed, the primary Replication Server and the Replication Server that connects to the replicate data server version must be 15.2. However, intermediate Replication Servers in a route can be of lower versions.

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_row_count_validation

If you have table rows that are not synchronized, and you want to bypass the default error actions and messages, you can set dsi_row_count_validation to off to disable row count validation.

Default: on to enable row count validation.

You need not suspend and resume a database connection when you set dsi_row_count_validation for the connection; the parameter takes effect immediately. However, the new setting affects the batch of replicated objects that Replication Server processes after you execute the command. Changing the setting does not affect the batch of replicated objects that Replication Server is currently processing.

dsi_serialization_method

Specifies the method used to determine when a transaction can start, while still maintaining consistency. In all cases, commit order is preserved.

These methods are ordered from most to least amount of parallelism. Greater parallelism can lead to more contention between parallel transactions as they are applied to the replicate database. To reduce contention, use the dsi_partition_rule option.

  • no_wait – specifies that a transaction can start as soon as it is ready—without regard to the state of other transactions.

  • wait_for_start – specifies that a transaction can start as soon as the transaction scheduled to commit immediately before it has started.

  • wait_for_commit – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it is ready to commit.

  • wait_after_commit – specifies that a transaction cannot start until the transaction scheduled to commit immediately preceding it has committed completely.

Note: You can only set dsi_serialization_method to no_wait if dsi_commit_control is set to “on”.
These options are retained only for backward compatibility with older versions of Replication Server:
  • none – same as wait_for_start.

  • single_transaction_per_origin – same as wait_for_start with dsi_partitioning_rule set to origin.

Note: The isolation_level_3 value is no longer supported as a serialization method but it is the same as setting dsi_serialization_method to wait_for_start and dsi_isolation_level to 3.

Default: wait_for_commit

dsi_sqt_max_cache_size

Maximum SQT (Stable Queue Transaction interface) cache memory for the database connection, in bytes.

The default, “0,” means that the current setting of sqt_max_cache_size is used as the maximum cache size for the connection.

Default: 0

For 32-bit Replication Server:

  • Minimum – 0

  • Maximum – 2147483647

For 64-bit Replication Server:

  • Minimum – 0

  • Maximum – 2251799813685247

dsi_stage_all_ops

Prevents compilation for specified tables when you configure Replication Server and Sybase IQ InfoPrimer integration.

If table history must be preserved, as in the case of slowly changing dimension (SCD) tables, set dsi_stage_all_ops to on.

See Replication Server Heterogeneous Replication Guide > Sybase IQ as Replicate Data Server > Replication Server and Sybase IQ InfoPrimer Integration > Parameters > dsi_stage_all_ops.

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_timer

Use the dsi_timer configuration parameter to specify a delay between the time transactions commit at the primary database and the time transactions commit at the standby or replicate database. Replication Server processes transactions in the outbound queue in commit order after the period of delay is over.

After you execute dsi_timer with alter connection or alter logical connection, suspend and resume the connection.

Specify the delay in the hh:mm format.

  • Maximum: 24 hours.

  • Default: 00:00, which means there is no delay.

Note: Replication Server does not support time zone differences between the Replication Agent at the primary database and the Replication Server with the DSI connection where you want to execute dsi_timer.

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.
Note: Obsolete for Replication Server version 15.0 and later. Retained for compatibility with older Replication Servers.

Maximum: 2,147,483,647

Default: 65,536 bytes

This parameter is ignored when dsi_compile_enable is turned on.

dump_load

Set to “on” at replicate sites only to enable coordinated dump. See Replication Server Administration Guide Volume 2 > Replication System Recovery > Recover from Primary Database Failures > Loading from Coordinated Dumps.

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,647.

Default: 2,147,483,647

dynamic_sql

Turns dynamic SQL feature on or off. Other dynamic SQL related configuration parameters will only take effect if this parameter is set to “on”.

Default: off

dynamic_sql_cache_size

Gives the Replication Server a hint on how many database objects may use the dynamic SQL statement for a connection. Minimum: 1

Maximum: 65536

Default: 100

dynamic_sql_cache_management

Manages the dynamic SQL cache for a DSI/E thread. Values: mru - keep the most recently used statements and deallocate the to allocate new dynamic statements when dynamic_sql_cache_size is reached. fixed (default)- Replication Server stops allocating the new dynamic statements once dynamic_sql_cache_size is reached.

exec_nrm_request_limit

Specifies the amount of memory available for messages from a primary database waiting to be normalized.

Set nrm_thread to ‘on’ with configure replication server before you use exec_nrm_request_limit.

Minimum: 16,384 bytes

Maximum: 2,147,483,647 bytes

Default for:

  • 32-bit – 1,048,576 bytes (1MB)

  • 64-bit – 8,388,608 bytes (8MB)

After you change the configuration for exec_nrm_request_limit, suspend and resume the Replication Agent.

License: Separately licensed under the Advanced Services Option. See Replication Server Administration Guide Volume 2 > Performance Tuning > Advanced Services Option .

exec_sqm_write_request_limit

Specifies the amount of memory available for messages waiting to be written to an inbound queue.

Default: 1MB

Minimum:16KB

Maximum: 2GB

md_sqm_write_request_limit

Specifies the amount of memory available to the Distributor for messages waiting to be written to the outbound queue.

Note: In Replication Server 12.1, md_sqm_write_request_limit replaces md_source_memory_pool. md_source_memory_pool is retained for compatibility with older Replication Servers.

Default: 1MB

Minimum: 16K

Maximum: 2GB

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

replicate_minimal_columns

Specifies whether Replication Server should send all replication definition columns for all transactions, or only those needed to perform update or delete operations at the replicate database.

Values are On and Off.

Default: On

Replication Server uses this connection-level parameter when a replication definition does not contain the replicate minimal columns clause, or if there is no replication definition at all

Otherwise, Replication Server ignores the value of this parameter.

You can use admin config to display replicate_minimal_columns configuration information.

When you set dsi_compile_enable to ‘on’, Replication Server ignores what you set for replicate_minimal_columns.

See Replication Server Administration Guide Volume 2 > Performance Tuning > Dynamic SQL for Enhanced Replication Server Performance > Use Replicate Minimal Columns with Dynamic SQL.

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

stage_operations

Set to on for Relication Server to write operations to staging tables for the specified connection when you configure Replication Server and Sybase IQ InfoPrimer integration.

See Replication Server Heterogeneous Replication Guide > Sybase IQ as Replicate Data Server > Replication Server and Sybase IQ InfoPrimer Integration > Parameters > stage_operations.

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.

Default: 1MB

Minimum: 16K

Maximum: 2GB

unicode_format

Supports sending unicode data in U&” format.

Set unicode_format to one of these values:
  • string – unicode characters are converted to character string format. For example, the string “hello” is sent out as “hello”.
  • ase – unicode characters are sent out in U&'' format. For example, the string “hello” is sent out as "U&'\0068\0065\006c\006c\006f’ ". The two-byte unicode value is sent in network order as required by Adaptive Server Enterprise.

Default: string

use_batch_markers

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

Note: 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

See Replication Server Administration Guide Volume 2 > Customize Database Operations > Manage Function Strings > Command Batching for Non-ASE Servers.