ALTER SERVER Statement

Syntax

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

server-class:
   { ASAJDBC  
      | ASEJDBC 
      | SAODBC  
      | ASEODBC
      | DB2ODBC  
      | MSSODBC 
      | ORAODBC  
      | ODBC }

connection-info:
   { machine-name:port-number [ /dbname ] | data-source-name }

Parameters

cap-name – the name of a server capability

CLASS – changes the server class. For more information on server classes.

USING – if a JDBC-based server class is used, the USING clause is hostname:port-number [/dbname] where:
- hostname – the machine on which the remote server runs.
- portnumber – the TCP/IP port number on which the remote server listens. The default port number for SAP Sybase IQ and SQL Anywhere is 2638.
- dbname – for SQL Anywhere remote servers, if you do not specify a dbname, the default database is used. For Adaptive Server Enterprise, the default is the master database, and an alternative to using dbname is to another database by some other means (for example, in the FORWARD TO statement).
If an ODBC-based server class is used, the USING clause is the data-source-name, which is the ODBC Data Source Name.

CAPABILITY – 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, SAP 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 – 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 the SERVER OPERATOR system privilege 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 the SERVER OPERATOR system privilege to be able to close the connection.

Examples

Example 1 – changes the server class of the Adaptive Server Enterprise server named ase_prod so its connection to SAP 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

Side effects:

Automatic commit

Standards

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

Permissions

Requires the SERVER OPERATOR system privilege.