alter connection

Description

Changes the attributes of a database connection.

Syntax

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] 'value' |
	set security_services [to] 'default']}

Parameters

data_server: The data server that holds the database whose connection is to be altered.

database: The database whose connection is to be altered.

function_class: The function-string class to use with the data server.

If you already have a DB2 database configured as a replicate database with an earlier version of Replication Server, continue to use the earlier version with Replication Server 12.0 and later and its HDS feature. The 12.0 and later function strings may not be compatible with earlier function string versions.

error_class: The error class to use to handle database errors.

passwd: The new password to use with the login name for the database connection. You must specify a password if network-based security is not enabled.

log transfer on: Allows the connection to send transactions from a RepAgent to the Replication Server.

log transfer off: Stops the connection from sending transactions from a primary database RepAgent.

database_param: The parameter that affects database connections from the Replication Server.

value

A character string containing a new value for the option.

Parameters and values are described in Table 3-14.

**Table 3-14: Parameters affecting database connections**
database_param	value
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
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
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_separator	The character that separates commands in a command batch. Default: newline (\n) You must update this parameter in an interactive mode, not by executing a DDL-generated script, or any other script. You cannot reset dsi_cmd_separator by running 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. Default: 1000ms (1 second) Minimum: 0 Maximum: 86,400,000 ms (24 hours)
dsi_commit_check_locks_logs	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_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. Data servers supporting other isolation levels are supported as well through the use of the rs_set_isolation_level function string. Replication Server supports all values for replicate data servers. 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 – 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, image or rawobject columns from filling the RSSD or its log. Default: -1 (all text, unitext, image, or rawobject 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, ignore_origin, origin_sessid, time, user, name, and none. See the Replication Server Administration Guide Volume 2 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, 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: off
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 option 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.
	These options are retained only for backward compatibility: 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_sql_data_style	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 instead, set this parameter to any value other than those listed above. When you configure a connection to DB2, specify the name of the NetGateway using the data_server parameter in the main clause of alter connection. When you configure 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 ODBC Driver Reference Guide, which is part of the Open Client/Server™ version 11.1.1 collection, for more information. Obsolete for Replication Server version 12.0 and later. Retained for compatibility with older Replication Servers. Default: “ ” (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 that the current setting of sqt_max_cache_size is used as the maximum cache size for the connection. Default: 0
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. 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
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
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 (default) – 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.
dynamic_sql_cache_size	Allows Replication Server to estimate how many database objects can use dynamic SQL for a connection. You can use dynamic_sqll_cache_size to limit resource demand on a data server. Default: 20 Minimum: 1 Maximum: 65,535
exec_cmds_per_timeslice	Specifies the number of LTL commands an LTI or RepAgent Executor thread can possess before it must yield the CPU to other threads. Default: 5 Minimum: 1 Maximum: 2,147,483,648
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: 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: 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. 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
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

security_param: A parameter that affects network-based security for connections. See Table 3-27 for a list of parameters and a description of values.

set security_services to 'default': Resets all network-based security features for the connection to match the global settings of your Replication Server.

Examples

Example 1

Changes the function-string class for the pubs2 database in the TOKYO_DS data server to sql_derived_class:

suspend connection to TOKYO_DS.pubs2
 
alter connection to TOKYO_DS.pubs2b
set function string class to sql_derived_class
 
resume connection to TOKYO_DS.pubs2

Example 2

Changes the number of LTL commands the LTI or RepAgent Executor thread can process before it must yield the CPU to other threads:

suspend connection to TOKYO_DS.pubs2
alter connection to TOKYO_DS.pubs2b
set exec_cmds_per_timeslice to ’10’
resume connection to TOKYO_DS.pubs2

Usage

Use suspend connection to suspend activity on the connection before altering it.
Execute alter connection at the Replication Server where the connection was created.
Before you use log transfer off to stop data transfer from a primary database, be sure there are no replication definitions defined for data in the database.
To change the route to a Replication Server, use alter route.
Use set function string class [to] function_class to activate class-level translations for non-Sybase data servers.
You can set connection parameters using the alter connection parameter.
Execute alter connection at the Replication Server where the connection was created.

Database connection parameters

Use alter connection to change the configuration parameters of a DSI or a database connection. To change a DSI configuration value, suspend the connection to the DSI, change the value, and then resume the connection to the DSI. This procedure causes the new value to take effect.
Replication Server configuration parameters are stored in the rs_config system table. Some parameters can be modified by updating rows in the table. See the Replication Server Administration Guide Volume 1 for more information.
See the Replication Server Administration Guide Volume 2 for more information about configuring parallel DSI threads.
Use assign action to enable retry of transactions that fail due to specific data server errors.
Before you change the function-string class, make sure that the class and all the required function strings exist for the new class.
Before you change the error class, make sure the new class exists.
Change the character for data servers that require a command separator to recognize the end of a command.

If you have specified a different separator character and want to change it back to a newline character, enter the alter connection command as follows:
```
alter connection to data_server.database 
  set  to '<Return>'
```
where you press the Return key, and no other characters, between the two single-quote characters.

The dsi_partitioning_rule parameter

You can specify more than one partitioning rule at a time. Separate values with a comma, but no spaces. For example:

alter connection to data_server.database
  set dsi_partitioning_rule to ‘origin,time’

The dump_load parameter

Before setting dump_load to “on,” create function strings for the rs_dumpdb and rs_dumptran functions. Replication Server does not generate function strings for these functions in the system-provided classes or in derived classes that inherit from these classes.

The save_interval configuration parameter

Set save_interval to save transactions in the DSI queue that can be used to resynchronize a database after it has been restored from backups. Setting a save interval is also useful when you set up a warm standby of a database that holds replicate data or receives replicated functions. You can use sysadmin restore_dsi_saved_segments to restore backlogged transactions.

Network-based security parameters

Both ends of a connection must use compatible Security Control Layer (SCL) drivers with the same security mechanisms and security features. The data server must support set proxy or an equivalent command.

It is the replication system Administrator’s responsibility to choose and set security features for each server. Replication Server does not query the security features of remote servers before attempting to establish a connection. Connections fail if security features at both ends of the connection are not compatible.
alter connection modifies network-based security settings for an outgoing connection from Replication Server to a target data server. It overrides default security parameters set with configure replication server.
If unified_login is set to “required,” only the replication system Administrator with "sa" permission can log in to the Replication Server without a credential. If the security mechanism should fail, the replication system Administrator can log in to Replication Server with a password and disable unified_login.
A Replication Server can have more than one security mechanism; each supported mechanism is listed in the libtcl.cfg file under SECURITY.
Message encryption is a costly process with severe performance penalties. In most instances, it may be wise to set msg_confidentiality “required” only for certain connections. Alternatively, choose a less costly security feature, such as msg_integrity.

Using alter connection to change ERSSD maintenance passwords

You can change ERSSD maintenance user passwords using the existing alter connection command:
```
alter connection to data_server.database
set password to password
```
If your Replication Server is using ERSSD and the
```
data_server.database
```
match the ERSSD names, using alter connection and set password updates the rs_maintusers table, issues sp_password at ERSSD, and updates the configuration file line RSSD_maint_pw.

Permissions

alter connection requires "sa" permission.