DB2 10.5 for Linux, UNIX, and Windows

CREATE SERVER statement

The CREATE SERVER statement defines a data source to a federated database.

In this statement, the term 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


>>-CREATE SERVER--server-name--+-------------------+------------>
                               '-TYPE--server-type-'   

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

>--+--------------------------------------------------------------+-->
   '-AUTHORIZATION--remote-authorization-name--PASSWORD--password-'   

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

server-version

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

Description

server-name

Names the data source that is being defined to the federated database. The name must not identify a data source that is described in the catalog. The server-name must not be the same as the name of any table space in the federated database.

A server definition for relational data sources usually represents a remote database. Some relational database management systems, such as Oracle, do not allow multiple databases within each instance. Instead, each instance represents a server within a federated system.

For nonrelational data sources, the purpose of a server definition varies from data source to data source. Some server definitions map to a search type and daemon, a website, or a web server. For other nonrelational data sources, a server definition is created because the hierarchy of federated objects requires that data source files (identified by nicknames) are associated with a specific server object.

TYPE server-type

Specifies the type of data source denoted by server-name. This parameter is required by some wrappers.

VERSION

Specifies the version of the data source denoted by server-name. This parameter is required by some wrappers.

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').

WRAPPER wrapper-name

Names the wrapper that the federated server uses to interact with the server object specified by server-name.

AUTHORIZATION remote-authorization-name

Required only for DB2® family data sources. Specifies the authorization ID under which any necessary actions are performed at the data source when the CREATE SERVER statement is processed. This authorization ID is not used when establishing subsequent connections to the server.

This ID must hold the authority (BINDADD or its equivalent) that the necessary actions require. If the remote-authorization-name is specified in mixed or lowercase characters (and the remote data source has case sensitive authorization names), the remote-authorization-name should be enclosed by double quotation marks.

PASSWORD password

Required only for DB2 family data sources. Specifies the password associated with the authorization ID represented by remote-authorization-name. If the password is specified in mixed or lowercase characters (and the remote data source has case sensitive passwords), the password should be enclosed by double quotation marks.

OPTIONS

Indicates the options that are enabled when the server definition is created. Server options are used to configure the server definition. Some server options can be used to create the server definition for any data source. Some server options are specific to a particular data source.

server-option-name: Names a server option that will be used to either configure or provide information about the data source denoted by server-name.
string-constant: Specifies the setting for server-option-name as a character string constant.

Notes

The password should be specified when the data source requires a password. If any letters in password must be in lowercase, enclose password in quotation marks.
If the CREATE SERVER statement is used to define a DB2 family instance as a data source, DB2 may need to bind certain packages to that instance. If binding is required, the remote-authorization-name in the statement must have BIND authority. The time required for the bind operation to complete is dependent on data source speed and network connection speed.
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.
Syntax alternatives: The following syntax is supported for compatibility with previous versions of DB2:
- ADD can be specified before server-option-name string-constant.

Examples

Register a server definition to access a DB2 for z/OS® and OS/390®, Version 7.1 data source. CRANDALL is the name assigned to the DB2 for z/OS and OS/390 server definition. DRDA is the name of the wrapper used to access this data source. In addition, specify that:
- GERALD and drowssap are the authorization ID and password under which packages are bound at CRANDALL when this statement is processed.
- The alias for the DB2 for z/OS and OS/390 database that was specified with the CATALOG DATABASE statement is CLIENTS390.
- The authorization IDs and passwords under which CRANDALL can be accessed are to be sent to CRANDALL in uppercase.
- CLIENTS390 and the federated database use the same collating sequence.
```
  CREATE SERVER CRANDALL
    TYPE DB2/ZOS
    VERSION 7.1
    WRAPPER DRDA
    AUTHORIZATION "GERALD"
    PASSWORD drowssap
    OPTIONS
        (DBNAME 'CLIENTS390',
        FOLD_ID 'U',
        FOLD_PW 'U',
        COLLATING_SEQUENCE 'Y')
```
Register a server definition to access an Oracle 9 data source. CUSTOMERS is the name assigned to the Oracle server definition. NET8 is the name of the wrapper used to access this data source. In addition, specify that:
- ABC is the name of the node where the Oracle database server resides.
- The CPU for the federated server runs twice as fast as the CPU that supports CUSTOMERS.
- The I/O devices at the federated server process data one and a half times as fast as the I/O devices at CUSTOMERS.
```
  CREATE SERVER CUSTOMERS
    TYPE ORACLE
    VERSION 9
    WRAPPER NET8
    OPTIONS
        (NODE 'ABC',
        CPU_RATIO '2.0',
        IO_RATIO '1.5')
```
Register a server definition for the Excel wrapper. The server definition is required to preserve the hierarchy of federated objects. BIOCHEM_LAB is the name assigned to the Excel server definition. EXCEL_2000_WRAPPER is the name of the wrapper used to access this data source.
```
  CREATE SERVER BIOCHEM_DATA
    WRAPPER EXCEL_2000_WRAPPER
    
```