ALTER SERVER statement

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

server-version

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 new server option.

SET

Changes the setting of a server option.

server-option-name

The server option that is to be added or reset. Which options you can specify depends on the data source of the object for which a wrapper is being created. For a list of data sources and the server options that apply to each, see Data source options.

string-constant

The server option setting as a character string constant enclosed in single quotation marks.

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.
• Server option HOST and NODE cannot be dropped at the same time (SQLSTATE 428EG).
• Both HOST and NODE keywords are included in the server option list, NODE is used even if NODE specified an unrecognized data source.
• PORT option is not mandatory. When PORT is dropped, ODBC wrapper uses default PORT number according to remote data source. The default PORT number is listed in CREATE SERVER.

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      

• Example 3: Drop the NODE option, add new HOST option. ODBC driver uses DSN-less mode instead of DSN connection to access a Hive server.

     ALTER SERVER HIVE
  OPTIONS (add HOST ‘9.123.111.214’, DROP NODE)