Changing parameters affecting a single connection

alter connection lets you change the attributes of a database connection. Use this command, for example, if you have added an Adaptive Server database connection using Sybase Central or rs_init, and then decide that you want the database connection to use a derived function-string class instead of a system-provided class. The syntax for alter connection is:

alter connection to data_server.database {
	set function string class [to] function_class |
	set error class [to] error_class |
	set password [to] passwd |
	set log transfer [to] {on | off} |
	set database_param [to] 'value'} |
	set security_param to {‘required’ | ‘not_required’} |
	set security_services [to] “default’
}

You indicate the data server and database that is connected to the Replication Server and specify one or more of the attributes to change. These include:

function_class – the function-string class to use with the database connection.

error_class – the error class to use for handling database errors.

passwd – the new password to use with the login name for the database connection.

log transfer on – allows transactions to be sent, using this connection, to the Replication Server.

log transfer off – stops transactions from being sent, using this connection, from a primary database to the Replication Server.

security_param – updates a network security configuration parameter that affects connections. See “Managing network-based security” for a list and description of parameters you can change.

set security_services [to] ‘default’ – resets all network-based security features for the connection to “not required.” See “Managing network-based security” for a description of network security for Replication Server.

An example of using alter connection

To change the function-string class for the pubs2 database in the SYDNEY_DS data server to sqlserver_derived_class, enter the following commands in the SYDNEY_RS Replication Server:

suspend connection to SYDNEY_DS.pubs2
alter connection to SYDNEY_DS.pubs2
    set function string to class
      sqlserver_derived_class
resume connection to SYDNEY_DS.pubs2

Refer to Chapter 3, “Replication Server Commands,” in the Replication Server Reference Manual for more information about the keywords and options of the alter connection command.

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 106 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 166 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 150 in the Replication Server Administration Guide Volume 2 for a list of configuration parameters that affect performance.

**Table 7-1: 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 “Command batching for non-ASE servers” on page 47 in the Replication Server Administration Guide Volume 2.
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 “Command batching for non-ASE servers” on page 47 in the Replication Server Administration Guide Volume 2.
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, 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 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: on 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) 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 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. See “Using parallel DSI threads” on page 166 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 166 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 166 in the Replication Server Administration Guide Volume 2. 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. 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 “High Volume Adaptive Replication to Adaptive Server” on page 231 in the Replication Server Administration Guide Volume 2 for HVAR and Chapter 11, “Sybase IQ Replicate Data Server Issues” in the Replication Server Heterogeneous Replication Guide for RTL.
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: 100,000 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. 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_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, 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 also “Partitioning rules: reducing contention and increasing parallelism” on page 180 in the Replication Server Administration Guide Volume 2 for detailed information. Default: none
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: 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. Mark identifiers in a replication definition as quoted. See “Quoted identifiers” to mark identifiers as quoted in a replication definition. 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_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. 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. 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_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. 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. 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 313 in the Replication Server Administration Guide Volume 2. 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
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 – Advanced Services Option” on page 230 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. 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. 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” on page 229 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. 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: 16K Maximum: 2GB
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 See “Command batching for non-ASE servers” on page 47 in the Replication Server Administration Guide Volume 2.

Changing parameters affecting a single connection

Using alter connection

An example of using alter connection

Configuration parameters affecting individual connections