SQL Reference

CREATE SERVER

The CREATE SERVER statement ⁷³ defines a data source to a 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 have SYSADM or DBADM authority on the federated database.

Syntax

>>-CREATE SERVER--server-name----+--------------------+--------->
                                 '-TYPE--server-type--'
 
>-----+------------------------------+---WRAPPER--wrapper-name-->
      '-VERSION--| server-version |--'
 
>-----+----------------------------------------------------------------------+>
      '-AUTHORIZATION--remote-authorization-name----+---------------------+--'
                                                    '-PASSWORD--password--'
 
                  .-,-------------------------------------------------.
                  V  .-ADD--.                                         |
>-----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.

TYPE server-type

Specifies the type of the data source denoted by server-name. This option is required with the DRDA, SQLNET, and NET8 wrappers. Refer to Appendix F, Federated Systems for a list of supported data source types.

VERSION

Specifies the version of the data source denoted by server-name.

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

WRAPPER wrapper-name

Names the wrapper that the federated server uses to interact with data sources of the type and version denoted by server-type and 'server-version'.

AUTHORIZATION remote-authorization-name

Specifies the authorization ID under which any necessary actions are performed at the data source when the CREATE SERVER statement is processed. This ID must hold the authority (BINDADD or its equivalent) that the necessary actions require.

PASSWORD password

Specifies the password associated with the authorization ID represented by remote-authorization-name. If password is not specified, it will default to the password for the ID under which the user is connected to the federated database.

OPTIONS

Indicates what server options are to be enabled. Refer to Server Options for descriptions of server-option-names and their settings.

ADD: Enables one or more server options.
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

If remote-authorization-name is not specified, the authorization ID for the federated database will be used.
The password should be specified in the case required by the data source; if any letters in password must be in lowercase, enclose password in quotation marks. If an identifier is specified but not password, the authentication type of the data source denoted by server-name is assumed to be CLIENT.
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 a bind is required, the remote-authorization-name in the statement must have BIND authority. The time required for the bind to complete is dependent on data source speed and network connection speed.
If a server option is set to one value for a type of data source, and this same option set to another value for an instance of this type, the second value overrides the first for the instance. For example, suppose that PASSWORD is set to 'Y' (yes, validate passwords at the data source) for a federated system's DB2 Universal Database for OS/390 data sources. Then later, this option's default ('N') is used for a specific DB2 Universal Database for OS/390 data source named SIBYL. As a result, passwords will be validated at all of the DB2 Universal Database for OS/390 data sources except SIBYL.

Examples

Example 1: Define a DB2 for MVS/ESA 4.1 data source that is accessible through a wrapper called DB2WRAP. Call the data source CRANDALL. In addition, specify that:

MURROW and DROWSSAP will be the authorization ID and password under which packages are bound at CRANDALL when this statement is processed.
CRANDALL is defined to the DB2 RDBMS as an instance called MYNODE.
When the federated server accesses CRANDALL, it will be connected to a database called MYDB.
The authorization IDs and passwords under which CRANDALL can be accessed are to be sent to CRANDALL in uppercase.
MYDB and the federated database use the same collating sequence.

   CREATE SERVER CRANDALL
      TYPE DB2/MVS
      VERSION 4.1
      WRAPPER DB2WRAP
      AUTHORIZATION MURROW
      PASSWORD DROWSSAP
      OPTIONS
          ( NODE 'MYNODE',
          DBNAME 'MYDB',
          FOLD_ID 'U',
          FOLD_PW 'U',
          COLLATING_SEQUENCE 'Y' )

Example 2: Define an Oracle 7.2 data source that's accessible through a wrapper called KLONDIKE. Call the data source CUSTOMERS. Specify that:

CUSTOMERS is defined to the Oracle RDBMS as an instance called ABC.

Provide these statistics for the optimizer:

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 7.2
      WRAPPER KLONDIKE
      OPTIONS
          ( NODE 'ABC',
          CPU_RATIO '2.0',
          IO_RATIO '1.5' )

Footnotes:

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

[ Top of Page | Previous Page | Next Page ]