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