alter connection

batch

Specifies how Replication Server sends commands to data servers. When batch is “on,” Replication Server may send multiple commands to the data server as a single command batch. When batch is “off,” Replication Server sends commands to the data server one at a time.

Default: on

batch_begin

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

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

deferred_name_resolution

Enable deferred name resolution in Replication Server to support deferred name resolution in Adaptive Server. Deferred name resolution is only supported in Adaptive Server 15.5 and later.

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

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

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.

Default: off

dist_stop_unsupported_cmd

When dist_stop_unsupported_cmd is on, DIST suspends itself if a command is not supported by downstream Replication Server. If it is off, DIST ignores the unsupported command.

Regardless of dist_stop_unsupported_cmd parameter’s setting, Replication Server always logs an error message when it sees the first instance of a command that cannot be sent over to a lower-version Replication Server.

Default: off

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.

Default: 512-byte network packet for all Adaptive Server databases Maximum: 16,384 bytes

dist_sqt_max_cache_size

The maximum Stable Queue Transaction (SQT) cache size for the inbound queue. 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

For 32-bit Replication Server:

• Minimum – 0

• Maximum – 2147483647

For 64-bit Replication Server:

• Minimum – 0

• Maximum – 2251799813685247

dsi_alt_writetext

Controls how large-object updates are sent to the replicate database. The 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 – generates an Adaptive Server writetext command that includes a text pointer.

Default: off

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, DSI applies bulk copy-in. Dynamic SQL is used if bulk copy-in is not used. Sybase recommends that you turn dsi_bulk_copy on to improve performance if you have large batches of inserts.

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

Configuration level: Server, database

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

dsi_charset_convert

The specification for handling character-set conversion on data and identifiers between the primary Replication Server and the replicate Replication Server. 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_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: on

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

License: Separately licensed under the Advanced Services Option. See “Replication Server – Advanced Services Option,” in Chapter 4, “Performance Tuning” in the Replication Server Administration Guide Volume 2.

dsi_cmd_separator

The character that separates commands in a command batch.

Default: newline (\n)

dsi_command_convert

Specifies how to convert a replicate command when you set dsi_compile_enable to ‘on’. 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.

Default: 1000ms (1 second)

Minimum: 0

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

dsi_commit_check_locks_log

The number of times the DSI executor thread executes the rs_dsi_check_thread_lock function string before logging a warning message. Used with parallel DSI.

Default: 200

Minimum: 1

Maximum: 1,000,000

dsi_commit_check_locks_max

The maximum number of times a DSI executor thread checks whether it is blocking other transactions in the replicate database before rolling back its transaction and retrying it. Used with parallel DSI.

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).

Default: on

dsi_compile _enable

Set to ‘on’ to enable RTL or HVAR at the server-level, database-level, or table-level.

Default:

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

• on – 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.

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.

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.

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.

Minimum: 100

Default: 100,000

You can configure the parameter at the server or database levels

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

dsi_connector_type

Specifies the database driver technology used for implementing the connector. This parameter along with dsi_dataserver_make is used to identify the connector that is associated with the connection. If you are replicating to ASE or IQ, set this parameter value to ctlib or if replicating to Oracle, set the value to oci.

Default: ctlib.

Valid values: ctlib, oci.

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” indicates that a connection will not close.

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. The 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 and dirty reads, and ensures that data written by one transaction represents the actual data.

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

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.

Set off to cause Replication Server to set triggers off in the Adaptive Server database, so that triggers do not fire when transactions are executed on the connection.

Set on for 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 – 1000.

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, image or rawobject columns from filling the RSSD or its log.

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

dsi_non_blocking_commit

The number of minutes that Replication Server saves a message after a commit. A 0 value means that non-blocking commit is disabled.

Default: 0 Maximum: 60

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, ignore_origin, origin_sessid, time, user, name, and none. See the Replication Server Administration Guide Volume 2 for detailed information.

Default: none

dsi_quoted_identifier

Enables or disables quoted identifier support in the Data Server Interface (DSI). Default: off

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, not usually 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_replication_ddl

Supports bidirectional replication by specifying whether or not transactions are to be replicated back to the original database.

When dsi_replication_ddl is set to on, DSI sends set replication off to the replicate database, which instructs it to mark the succeeding DDL transactions available in the system log not to be replicated. Therefore, these DDL transactions are not replicated back to the original database, which enables DDL transaction replication in bidirectional MSA replication environment.

Default: off

dsi_rs_ticket_report

Determines whether to call function string rs_ticket_report or not. rs_ticket_report function string is invoked when dsi_rs_ticket_report is set to on.

Default: on

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.

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.

Default: wait_for_commit

dsi_sqt_max_cache_size

Maximum SQT (Stable Queue Transaction interface) cache size for the outbound queue, 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_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. Its 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.

dsi_xact_group_size

The maximum number of bytes, including stable queue overhead, to place into one grouped transaction. A grouped transaction is multiple 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 the Replication Server Administration Guide Volume 2 for details.

Default: off

dynamic_sql

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

Default: off

dynamic_sql_cache_management

Manages the dynamic SQL cache for a connection.

Values:

• mru – specifies that once dynamic_sql_cache_size is reached, the old dynamic SQL prepared statements are deallocated to give room for new statements.

• fixed – specifies that once the dynamic_sql_cache_size is reached, allocation for new dynamic SQL statements stops.

Default: “fixed”

dynamic_sql_cache_size

Allows Replication Server to estimate how many database objects can use dynamic SQL for a connection. You can use dynamic_sql_cache_size to limit resource demand on a data server.

Default: 100 Minimum: 1 Maximum: 65,535

exec_cmds_per_timeslice

Specifies the number of LTL commands an LTI or RepAgent executor thread can process before yielding the CPU. By increasing this value, you allow the RepAgent executor thread to control CPU resources for longer periods of time, which may improve throughput from RepAgent to Replication Server.

Set this parameter at the connection level using alter connection.

See “Controlling the number of commands the RepAgent executor can process,” in Chapter 4, “Performance Tuning” in the Replication Server Administration Guide Volume 2.

Default: 2,147,483,647

Minimum: 1

Maximum: 2,147,483,647

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 – Advanced Services Option,” in Chapter 4, “Performance Tuning” in the Replication Server Administration Guide Volume 2.

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.

Default: 1MB Minimum: 16KB Maximum: 2GB

parallel_dsi

Provides a shorthand method for configuring parallel DSI threads.

A setting of “on” configures these values:

• dsi_num_threads to 5

• dsi_num_large_xact_threads to 2

• dsi_serialization_method to “wait_for_commit”

• dsi_sqt_max_cache_size to 1 million bytes (on 32-bit platform) and 20 million bytes (on 64-bit platform).

A setting of “off” configures these parallel DSI values to their defaults.

You can set this parameter to “on” and then set individual parallel DSI configuration parameters to fine-tune your configuration.

Default: off

rep_as_standby

When the database is marked with sp_reptostandby and rep_as_standby is on, tables with a table replication definition not covered by a database replication definition are replicated. To replicate the tables, set:

• rep_as_standby to on

• send maint xacts to replicate to false

• send warm standby xacts to 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.

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.Default: On

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 “Using replicate_minimal_columns with dynamic SQL.” in Chapter 4, “Performance Tuning” in the Replication Server Administration Guide Volume 2.

save_interval

The number of minutes that the Replication Server saves messages after they have been successfully passed to the destination data server. See the Replication Server Administration Guide Volume 2 for details.

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.

Default: 1MB Minimum: 16KB Maximum: 2GB

use_batch_markers

Controls the processing of function strings rs_batch_start and rs_batch_end. If use_batch_markers is set to on, the rs_batch_start function string is prepended to each batch of commands and the rs_batch_end function string is appended to each batch of commands.

Set use_batch_markers to on only for replicate data servers that require additional SQL to be sent at the beginning or end of a batch of commands that is not contained in the rs_begin function string.

Default: off