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). This statement can only be executed while in the default SYSTEM tenant (SQLSTATE 58004).

Authorization

The privileges held by the authorization ID of the statement must include DBADM authority.

Syntax

server-version

Description

server-nameSpecifies 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.

For non-relational 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 non-relational 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 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
Amazon Redshift	REDSHIFT	ODBC
Apache Hive	HIVE	ODBC
Apache Spark	SPARK	JDBC
Apache Spark SQL	SPARK_ODBC	ODBC
Cloudera Impala	IMPALA	ODBC
CouchDB	COUCHDB	NoSQL
database.com	DATABASE.COM	ODBC
force.com	FORCE.COM	ODBC
HDFS parquet	HDFSPARQUET	NoSQL
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
Informix® (with INFORMIX wrapper)	INFORMIX	INFORMIX
Informix (with ODBC wrapper)	INFORMIX_ODBC	ODBC
JDBC	JDBC	JDBC
MariaDB	MARIADB	ODBC
Microsoft Azure	AZURE	ODBC
Microsoft SQL Server (with MSSQLODBC3 wrapper)	MSSQLSERVER	MSSQLODBC3
Microsoft SQL Server (with ODBC wrapper)	MSSQL_ODBC	ODBC
MongoDB	MONGODBREST¹, MONGODRIVER², RESTHEART³	NoSQL
ODBC	ODBC	ODBC
Oracle (with NET8 wrapper)	ORACLE	NET8
Oracle (with ODBC wrapper)	ORACLE_ODBC	ODBC
Oracle Cloud	ORACLE_CLOUD	ODBC
Oracle MySQL	MYSQL	ODBC
Pivotal Greenplum	GREENPLUM	ODBC
Pivotal HAWQ	HAWQ	ODBC
PostgreSQL	POSTGRESQL	ODBC
Progress OpenEdge	OPENEDGE	ODBC
Salesforce	SALESFORCE	ODBC
SAP HANA	HANA	ODBC
SAP Sybase	SYBASE	CTLIB
SAP Sybase IQ	SYBASEIQ	ODBC
SAP Sybase ASE	SYBASE_ODBC	ODBC
Teradata (with TERADATA wrapper)	TERADATA	TERADATA
Teradata (with ODBC wrapper)	TERADATA_ODBC	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 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

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 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.
The AUTHORIZATION keyword and PASSWORD keyword in the CREATE SERVER statement become optional if following conditions are true:
- Db2 family data source.
- Native wrapper or Db2 JDBC wrapper.
- Server option SSO_AUTH value set to 'Y'.
Syntax alternatives: The following syntax is supported for compatibility with previous product versions:
- ADD can be specified before server-option-name string-constant.
The TYPE server-type is optional for DSN connection mode but mandatory for DSN-less connection mode.

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
    
```
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’)    
```
Register a server definition to access to the Db2 data source, which uses the default DRDA wrapper. The server option SSO_AUTH indicates that remote data sources uses a single sign-on (SSO) authentication mechanism, so the AUTHORIZATION keyword and PASSWORD keyword can be removed.
```
CREATE SERVER server1 TYPE DB2/LUW VERSION 11 OPTIONS(HOST ‘hives.cn.ibm.com’, PORT 50000, DBNAME sample, SSO_AUTH 'Y')
```