After a connection is created, you can change its configuration parameters with the alter connection command. Refer to Table 7-1 for a list and description of configuration parameters that affect connections.
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.
database_param – updates a configuration parameter that affects connections. See Table 7-1 for a list of parameters you can change.
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.
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.
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 105 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 158 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 143 in the Replication Server Administration Guide Volume 2 for a list of configuration parameters that affect performance.
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,” in Chapter 2, “Customizing Database Operations” 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,” in Chapter 2, “Customizing Database Operations” 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 |
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:
|
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. 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. Minimum: 1 Default: 20 |
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:
|
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_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)
|
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 158 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 158 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 158 in the Replication Server Administration Guide Volume 2. 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 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:
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_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 169 in the Replication Server Administration Guide Volume 2 for detailed information. Default: none |
dsi_quoted_identifiers |
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:
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 maintain serial consistency between parallel DSI threads when applying transactions to a replicate data server.
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 the current setting of the sqt_max_cache_size parameter is used as the maximum cache size for the connection. Default: 0 |
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.
Default: 1 |
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 261 in the Replication Server Administration Guide Volume 2 for details. 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_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: 16K 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: 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 |
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.
Default: off See “Command batching for non-ASE servers,” in Chapter 2, “Customizing Database Operations” in the Replication Server Administration Guide Volume 2. |
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. 