SQL Reference

ALTER SERVER

The ALTER SERVER statement ⁵⁸ is used to:

• 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.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The authorization ID of the statement must include either SYSADM or DBADM authority on the federated database.

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. version must be an integer.

release

Specifies the number of the release of the version denoted by version. release must be an integer.

mod

Specifies the number of the modification of the release denoted by release. mod 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. The server type must be one that is listed in the catalog.

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. Refer to Server Options for descriptions of server-option-names and their settings.

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

• This statement does not support the DBNAME and NODE server options (SQLSTATE 428EE).
• 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 under either of the following conditions:
  • The statement references a single data source, and the UOW already includes a SELECT statement that references a nickname for a table or view within this data source (SQLSTATE 55007).
  • The statement references a category of data sources (for example, all data sources of a specific type and version), and the UOW already includes a SELECT statement that references a nickname for a table or view within one of these data sources (SQLSTATE 55007).
• If the server option is set to one value for a type of data source, and set to another value for an instance of the 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.

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 these data sources have started to run on an upgraded CPU that's half as fast as your local 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 a DB2 Universal Database for AS/400 Version 3.0 data source called SUNDIAL has been upgraded to Version 3.1.

   ALTER SERVER SUNDIAL
      VERSION 3.1      

⁵⁸

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. For information about federated systems, see DB2 Federated Systems. For information about DRDA application servers, see Distributed Relational Database.