alter connection

Changes the attributes of a database connection.

Syntax

alter connection to data_server.database {
     [for replicate table named [table_owner.]table_name 
     [set table_param [to] ‘value’]] |
     set function string class [to] function_class |
     set error class [to] error_class |
     set replication server error class [to] rs_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']
     set dataserver and database name [to] new_ds.new_db |
     set trace [to] 'value'}

Parameters

Examples

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 for the default or alternate connections using the alter connection parameter.

    Any value you set for an alternate connection overrides inherited values from the default connection or the default values.

  • 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_bulk_copy parameter

When dsi_bulk_copy is on, SQT counts the number of consecutive insert statements on the same table that a transaction contains. If this number reaches the dsi_bulk_threshold, DSI:

  1. Bulk-copies the data to Adaptive Server until DSI reaches a command that is not insert or that belongs to a different replicate table.

  2. Continues with the rest of the commands in the transaction.

Adaptive Server sends the result of bulk copy-in at the end of the bulk operation, when it is successful, or at the point of failure.

Note: The DSI implementation of bulk copy-in supports multistatement transactions, allowing DSI to perform bulk copy-in even if a transaction contains commands that are not part of the bulk copy.

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 dataserver and database name parameter

Using dataserver and database name parameter you can switch the connection from using one connector to using another connector. For example, if you replicating to Oracle using the ASE/CT-Lib connector and DirectConnect™ for Oracle and you want to switch your connection to use the Oracle/OCI connector, you may be required to use a new data server and database name. Because the name given to the DirectConnect/Oracle in the Sybase interfaces file may not be the same as the Oracle data server name in the Oracle TNS Names file. To change:

  1. Suspend the connection.

  2. Alter the connection setting dsi_dataserver_make to ora and dsi_connector_type to oci.

  3. Alter the connection setting dataserver and database name to new_ds and new_db

    where:

    • new_ds – name of the data server in the Oracle tnsnames.ora file

    • new_ds – name of the database

    Note: The new_ds and new_db parameters can have the same values that you have defined for data_server and database parameters.

  4. Resume the connection.

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 maintenance passwords

  • You can change the maintenance user password of any DSI connection using the 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_enc.

Permissions

alter connection requires “sa” permission.

Related reference
admin show_connections
admin who
create alternate connection
create connection
configure replication server
create error class
create function string class
drop connection
resume connection
set proxy
suspend connection