DB2 10.5 for Linux, UNIX, and Windows

ALTER SERVER statement

The ALTER SERVER statement is used to modify the definition or configuration of a data source.

This statement can be used to make the following changes:

Modify the definition of a specific data source, or the definition of a category of data sources.
Make changes in the configuration of a specific data source, or the configuration of a category of data sources-changes that will persist over multiple connections to the federated database.

In this statement, the word SERVER and the parameter names that start with server- refer only to data sources in a federated system. They do not refer to the federated server in such a system, or to DRDA® application servers.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include DBADM authority.

Syntax


>>-ALTER SERVER------------------------------------------------->

>--+-server-name--+-----------------------------+----------------------------------+-->
   |              '-VERSION--| server-version |-'                                  |   
   '-TYPE--server-type--+--------------------------------------------------------+-'   
                        '-VERSION--| server-version |--+-----------------------+-'     
                                                       '-WRAPPER--wrapper-name-'       

>--+---------------------------------------------------------------------+-><
   |             .-,------------------------------------------------.    |   
   |             V   .-ADD-.                                        |    |   
   '-OPTIONS -(----+-+-----+--server-option-name--string-constant-+-+--)-'   
                   | '-SET-'                                      |          
                   '-DROP--server-option-name---------------------'          

server-version

|--+-version--+------------------------+-+----------------------|
   |          '-.--release--+--------+-' |   
   |                        '-.--mod-'   |   
   '-version-string-constant-------------'

Description

server-name

Identifies the federated server's name for the data source to which the changes being requested are to apply. The data source must be one that is described in the catalog.

VERSION

After server-name, VERSION and its parameter specify a new version of the data source that server-name denotes.

version: Specifies the version number. The value must be an integer.
release: Specifies the number of the release of the version denoted by version. The value must be an integer.
mod: Specifies the number of the modification of the release denoted by release. The value must be an integer.
version-string-constant: Specifies the complete designation of the version. The version-string-constant can be a single value (for example, '8i'); or it can be the concatenated values of version, release and, if applicable, mod (for example, '8.0.3').

TYPE server-type

Specifies the type of data source to which the changes being requested are to apply.

VERSION

After server-type, VERSION and its parameter specify the version of the data sources for which server options are to be enabled, reset, or dropped.

WRAPPER wrapper-name

Specifies the name of the wrapper that the federated server uses to interact with data sources of the type and version denoted by server-type and server-version. The wrapper must be listed in the catalog.

OPTIONS

Indicates what server options are to be enabled, reset, or dropped for the data source denoted by server-name, or for the category of data sources denoted by server-type and its associated parameters.

ADD: Enables a server option.
SET: Changes the setting of a server option.
server-option-name: Names a server option that is to be enabled or reset.
string-constant: Specifies the setting for server-option-name as a character string constant.
DROP server-option-name: Drops a server option.

Notes

A server option cannot be specified more than once in the same ALTER SERVER statement (SQLSTATE 42853). When a server option is enabled, reset, or dropped, any other server options that are in use are not affected.
An ALTER SERVER statement within a given unit of work (UOW) cannot be processed (SQLSTATE 55007) under either of the following conditions:
- The statement references a single data source, and the UOW already includes one of the following:
  - A SELECT statement that references a nickname for a table or view within this data source
  - An open cursor on a nickname for a table or view within this data source
  - Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within this data source
- The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes one of the following:
  - A SELECT statement that references a nickname for a table or view within one of these data sources
  - An open cursor on a nickname for a table or view within one of these data sources
  - Either an INSERT, DELETE, or UPDATE statement issued against a nickname for a table or view within one of these data sources
If the server option is set to one value for a type of data source, and set to another value for an instance of this type, the second value overrides the first one for the instance. For example, assume that PLAN_HINTS is set to 'Y' for server type ORACLE, and to 'N' for an Oracle data source named DELPHI. This configuration causes plan hints to be enabled at all Oracle data sources except DELPHI.
You can only alter set or alter drop server options for a category of data sources that was enabled by a prior alter add server option operation (SQLSTATE 42704).
When altering the server version, no verification occurs to ensure that the specified server version matches the remote server version. Specifying an incorrect server version can result in SQL errors when you access nicknames that belong to the database server definition. This is most likely when you specify a server version that is later than the remote server version. In that case, when you access nicknames that belong to the server definition, the database server might send SQL that the remote server does not recognize.

Examples

Example 1: Ensure that when authorization IDs are sent to your Oracle 8.0.3 data sources, the case of the IDs will remain unchanged. Also, assume that the local federated server CPU is twice as fast as the data source CPU. Inform the optimizer of this statistic.
```
   ALTER SERVER
     TYPE ORACLE
     VERSION 8.0.3
     OPTIONS
        (ADD FOLD_ID 'N',
        SET CPU_RATIO '2.0')
```
Example 2: Indicate that the Documentum data source called DCTM_SVR_ASIA has been changed to Version 4.
```
   ALTER SERVER DCTM_SVR_ASIA
     VERSION 4      
```