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.

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

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

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

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

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

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

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

NoteIn 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


Database connection parameters


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


Using alter connection to change ERSSD maintenance passwords

Permissions

alter connection requires "sa" permission.

See also

admin show_connections, admin who, alter connection, configure replication server, create error class, create function string class, drop connection, resume connection, set proxy, suspend connection