Servers

A server is a federation object that represent a remote database instance.

About this task

The following table shows the relationship between data source, server type (as specified in the CREATE SERVER statement), and default wrapper.

Table 1. Relationship between data source, server type, and default wrapper
Data Source	Server Type	Wrapper	Notes®
DB2® LUW	DB2/UDB	DRDA
DB2 z/OS®	DB2/ZOS	DRDA
Oracle	ORACLE	NET8
Oracle	ORACLE_ODBC	ODBC	4.2.5 only
Microsoft SQL Server	MSSQLSERVER	MSSQLODBC3
Microsoft SQL Server	MSSQL_ODBC	ODBC	4.2.5 only
Netezza®	NETEZZA	ODBC
Teradata	TERADATA	TERADATA

Example

DB2 data sources (>= 4.2)

CREATE SERVER MYDB2
  TYPE DB2/UDB
  VERSION 11
  AUTHORIZATION “newton” PASSWORD “Password”
  OPTIONS (HOST '<ip-address>', PORT '5000', DBNAME 'MYDB');
DROP SERVER MYDB2;
CREATE SERVER MYDB2
  TYPE DB2/ZOS
  AUTHORIZATION “newton” PASSWORD “Password”
  OPTIONS (HOST '<ip-address>', PORT '480', DBNAME 'DATDB', PUSHDOWN 'N');

HOST: An IP address, fully qualified domain name, or recognized alias from the local hosts file
PORT: The port on which the remote DB2 server is listening
DBNAME: The name of a remote database instance
PUSHDOWN: An option that prevents all functions or orderings from being executed at the remote server

Teradata data sources
```
CREATE SERVER TERA
  TYPE TERADATA
  VERSION 15
  (NODE '<ip-address>')
```
- NODE: An IP address, fully qualified domain name, or recognized alias from the local hosts file

Oracle data sources (<= 4.2)

CREATE SERVER ORA
  TYPE ORACLE
  WRAPPER NET8
  VERSION 11
  OPTIONS (NODE 'oracle11r2');

NODE: An entry in tnsname.ora

Oracle data sources (4.2.5)
```
CREATE SERVER ORA
  TYPE ORACLE_ODBC
  OPTIONS (HOST '<ip-address>', PORT '1521', SERVICE_NAME 'orcl.ibm.com');
```
- HOST: An IP address, fully qualified domain name, or recognized alias from the local hosts file
- PORT: The port on which the remote server is listening
- SERVICE_NAME: The service name for the database instance
Netezza data sources (<= 4.2)
```
CREATE SERVER NTZ
  TYPE NETEZZA
  WRAPPER ODBC
  VERSION 7
  OPTIONS (NODE 'netezza');
```
- VERSION: The version number for the ODBC driver
- NODE: An entry in odbc.ini
Netezza data sources (4.2.5)
```
CREATE SERVER NTZ
  TYPE NETEZZA
  OPTIONS (HOST '<ip-address>', DBNAME 'TESTDB');
```
- HOST: An IP address, fully qualified domain name, or recognized alias from the local hosts file
- DBNAME: The name of a remote database

Microsoft SQL Server data sources (<= 4.2)

CREATE SERVER MSSQL
  TYPE MSSQLSERVER
  WRAPPER MSSQLODBC3
  VERSION 2012
  OPTIONS (NODE 'mssql2016csdl1');

NODE: An entry in odbc.ini

Microsoft SQL Server data sources (4.2.5)
```
CREATE SERVER MSSQL
  TYPE MSSQL_ODBC
  VERSION 2016
  OPTIONS (HOST '<ip-address>', DBNAME 'mssql2016db1');
```
- HOST: An IP address, fully qualified domain name, or recognized alias from the local hosts file
- DBNAME: The name of a remote database instance