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.

Description

server-name

Specifies the name of 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 of the table spaces 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.

TYPE server-type

Specifies the type of the data source that is being defined to the federated database, and determines the default wrapper that is used.

Table 1. Server types and default wrappers
Data source	Type	Default Wrapper
Apache Hive	HIVE	ODBC
Cloudera Impala	IMPALA	ODBC
IBM BigInsights	BIGSQL	DRDA
IBM Db2® Warehouse on Cloud	DASHDB	DRDA
IBM Db2 on Cloud	DASHDB	DRDA
IBM Db2 Warehouse	DASHDB	DRDA
IBM Db2	DB2/LUW	DRDA
Db2 for z/OS®	DB2/ZOS	DRDA
Db2 for IBM® i	DB2/ISERIES	DRDA
IBM Db2 Hosted	DB2/LUW	DRDA
IBM Db2 Server for VSE and VM	DB2/VM	DRDA
IBM PureData System for Analytics (formerly Netezza)	PDA (the type NETEZZA can also be used but is deprecated)	ODBC
IBM PureData System for Operational Analytics	DB2/LUW	DRDA
IBM PureData System for Transactions	DB2/LUW	DRDA
JDBC	JDBC	JDBC
Microsoft SQL Server (with ODBC wrapper)	MSSQL_ODBC	ODBC
ODBC	ODBC	ODBC
Oracle (with ODBC wrapper)	ORACLE_ODBC	ODBC
Oracle Cloud	ORACLE_CLOUD	ODBC

This parameter is required by some wrappers. For example, it is required by an ODBC wrapper that is operating in DSN-less connection mode.

¹ Server type MONGODBREST uses RESTAPI to connect to MongoDB. The MONGODBREST option depends on an HTTP interface being configured correctly and running on MongoDB version 3.4, and earlier versions have a built-in HTTP interface.

² Server type MONGODRIVER uses the native MongoDB driver to connect to MongoDB. This approach has less dependency compared to other approaches and provides more performance advantages because this method leverages MongoDB full native API.

³ Server type RESTHEART uses RESTAPI to get data. However, RestHeart is a third-party tool that must be installed separately. RestHeart supports more features than the MongoDB HTTP interface, and provides better performance than using MONGODBREST server type because it supports more filter functionality.

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

Specifies the name of the wrapper that the federated server is to use to interact with the newly created server object. The default depends on the type of the remote server (see Table 1.

If the default wrapper does not already exist, it is created with the SET DB2_FENCED 'Y' option. A default wrapper is created only once. For example, if a wrapper with the name DRDA does not already exist, and if you issue two CREATE SERVER statements (one to create a BIGSQL server and another to create a DB2/ZOS server), and if you do not specify a wrapper name for either server explicitly, the first statement causes a default wrapper with the name DRDA to be created and both new servers use that wrapper.

Note: If a wrapper with the same name as a not-yet-created default wrapper already exists (for example, because it was created by a CREATE WRAPPER statement), that wrapper is not used as a default for a CREATE SERVER statement. In this situation, no default is available and the CREATE SERVER statement must specify a wrapper name explicitly.

AUTHORIZATION remote-authorization-name

Required only for IBM database 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 IBM database 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

Specify configuration options for the for the server to be created. Which options you can specify depends on the data source of the object for which a server is being created. For a list of data sources and the server options that apply to each, see Data source options. Each option value is a character string constant that must be enclosed in single quotation marks.

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 an IBM database instance as a data source, your database might 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.
The TYPE server-type is optional for DSN connection mode but mandatory for DSN-less connection mode.

Examples

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
    
```
Register a server definition for the ODBC wrapper. To access Apache Hive within DSN-less mode, the server type must be specified. HOST is the Hive server address. PORT is the connection port number that is used to access this data source. PORT is an optional parameter. If PORT is not defined, the default number is 10000.
```
  CREATE SERVER HIVE_SERV TYPE HIVE
		WRAPPER ODBC AUTHORIZATION ‘root’ PASSWORD ‘hadoop’
         OPTIONS (HOST ‘hives.cn.ibm.com’, PORT ‘10000’, 
                 DBNAME ‘default’, PASSWORD ‘Y’, PUSHDOWN ‘Y’)    
```