ALTER SERVER Statement

Modifies the attributes of a remote server.

Syntax

ALTER SERVER server-name 
[ CLASS 'server-class' ]
[ USING 'connection-info' ]
[ CAPABILITY 'cap-name' { ON | OFF } ]
[ CONNECTION CLOSE [ CURRENT | ALL | connection-id ] ]

Parameters

server-class: – { ASAJDBC | ASEJDBC | ASAODBC | ASEODBC| DB2ODBC | MSSODBC | ORAODBC | ODBC }
connection-info: – { machine-name:port-number [ /dbname ] | data-source-name }
cap-name: – the name of a server capability

Examples

Example 1 – Changes the server class of the Adaptive Server Enterprise server named ase_prod so its connection to Sybase IQ is ODBC-based. The Data Source Name is ase_prod.
```
ALTER SERVER ase_prod
CLASS 'ASEODBC'
USING 'ase_prod'
```

Example 2 – Changes a capability of server infodc:

ALTER SERVER infodc
CAPABILITY 'insert select' OFF

Example 3 – Closes all connections to the remote server named rem_test:
```
ALTER SERVER rem_test
CONNECTION CLOSE ALL
```
Example 4 – Closes the connection to the remote server named rem_test that has the connection ID 142536:
```
ALTER SERVER rem_test
CONNECTION CLOSE 142536
```

Usage

Changes made by ALTER SERVER do not take effect until the next connection to the remote server.

CLASS clause—Use the CLASS clause to change the server class. For more information on server classes, see System Administration Guide: Volume 2 > Accessing Remote Data and System Administration Guide: Volume 2 > Server Classes for Remote Data Access.

USING clause—The USING clause changes the server’s connection information. For more information about connection information, see CREATE SERVER Statement.

CAPABILITY clause—The CAPABILITY clause turns a server capability ON or OFF. Server capabilities are stored in the system table SYSCAPABILITY. The names of these capabilities are stored in the system table SYSCAPABILITYNAME. The SYSCAPABILITY table contains no entries for a remote server until the first connection is made to that server. At the first connection, Sybase IQ interrogates the server about its capabilities and then populates SYSCAPABILITY. For subsequent connections, the server’s capabilities are obtained from this table.

In general, you need not alter a server’s capabilities. It might be necessary to alter capabilities of a generic server of class ODBC.

CONNECTION CLOSE clause—When a user creates a connection to a remote server, the remote connection is not closed until the user disconnects from the local database. The CONNECTION CLOSE clause allows you to explicitly close connections to a remote server. You may find this useful when a remote connection becomes inactive or is no longer needed.

These SQL statements are equivalent and close the current connection to the remote server:

ALTER SERVER server-name CONNECTION CLOSE

ALTER SERVER server-name CONNECTION CLOSE CURRENT

You can close both ODBC and JDBC connections to a remote server using this syntax. You do not need DBA authority to execute either of these statements.

You can also disconnect a specific remote ODBC connection by specifying a connection ID, or disconnect all remote ODBC connections by specifying the ALL keyword. If you attempt to close a JDBC connection by specifying the connection ID or the ALL keyword, an error occurs. When the connection identified by connection-id is not the current local connection, the user must have DBA authority to be able to close the connection.

Side effects:

Automatic commit

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Supported by Open Client/Open Server.

Permissions

Must have DBA authority to execute this command.