Registering the server definitions for an ODBC data source

You must register each ODBC server that you want to access in the federated database.

1. Issue the CREATE SERVER statement from the command line and specify the required server options.
   Example for DSN mode connection:

   CREATE SERVER server_definition_name TYPE server_type 
       VERSION version_number WRAPPER wrapper_name 
       OPTIONS (NODE 'node_name', CODEPAGE 'codepage_name', 
       DBNAME 'database_name');

   Example for DSN-less mode connection:

   CREATE SERVER server_definition_name TYPE server_type 
       VERSION version_number WRAPPER wrapper_name 
       OPTIONS (HOST 'ip_address', PORT 'port_number', 
       CODEPAGE 'codepage_name', DBNAME 'database_name');

   Although NODE, HOST, CODEPAGE, and DBNAME are server options in the CREATE SERVER statement, they are required for ODBC data sources:

   Note: For Oracle ODBC data source, for DSN-less mode connection, the server option "SERVICE_NAME" is required.

   SERVER server_definition_name

   Specifies the name that is assigned to the ODBC data source server. Duplicate server definition names are not allowed.

   TYPE server_type

   This parameter is required. It specifies the type of data source server to which you are configuring to access. The detailed server type name refers to the following table:

   Data Source Name	Server type
   Apache Hive 1 2 4	HIVE
   Apache Spark SQL 1 2 4	SPARK_ODBC
   MariaDB 1 2 4	MARIADB
   Microsoft Azure 1 2 4	AZURE
   Microsoft SQL Server (with ODBC wrapper) 1 2 4	MSSQL_ODBC
   Oracle MySQL Enterprise Edition1 2 4	MYSQL
   Oracle MySQL Community Edition1 2 4	MYSQL_CE
   Oracle (with ODBC wrapper) 1 2 4	ORACLE_ODBC
   Pivotal Greenplum 1 2 4	GREENPLUM
   PostgreSQL 1 2 4	POSTGRESQL
   IBM PureData System for Analytics (formerly Netezza) 1 2 34	PDA (the type NETEZZA can also be used but is deprecated)

   ¹ nnstat method 1 is not supported. There is no error when using method 1, but no data will be returned.

   ² Transparent DDL is not supported.

   ³ You can use the ENABLE_BULK_INSERT server option to take advantage of bulk insert operations to the Netezza data source. Bulk insert operations are not supported in pass-through sessions.

   ⁴ IUD is not supported.

   VERSION version_number

   The version of the ODBC data source that you want to access. This parameter is optional.

   WRAPPER wrapper_name

   The wrapper name that you specified in the CREATE WRAPPER statement.

   NODE 'odbc_node'

   The name of the node (the system DSN name) that was assigned to the ODBC data source when the DSN was defined. On federated servers that run Windows, this value must be the name of a system DSN in the ODBC Data Source Administrator window. On federated servers that run UNIX, the name of the node is the DSN defined in the ODBC configuration file. The ODBC configuration file is usually called odbc.ini.

   Although the name of the node is specified as an option in the CREATE SERVER statement, it is required for ODBC data sources, unless you choose DSN-less connection mode.

   HOST 'ip_address'

   In DSN-less mode, the HOST server option must be set to the data source IP address. If NODE option is specified, then HOST option can be ignored, otherwise, HOST option is required. If both NODE option and HOST option are specified, just NODE option will take effect.

   For example: If the remote data source is HIVE server, the IP address is 9.181.139.59 (svt1.cn.ibm.com), then the HOST option should be set to ‘9.181.139.59’ or ‘svt1.cn.ibm.com’.

   PORT 'port_number'

   Specifies the port of the data source server, without specify this parameter, it will use the default port number as following table: Default port number for ODBC data sources.

   Data Source Name	Default port number
   Apache Hive	10000
   Apache Spark SQL	10001
   MariaDB	3306
   Microsoft Azure	1433
   Microsoft SQL Server (with ODBC wrapper)	1433
   Oracle MySQL Enterprise Edition	3306
   Oracle MySQL Community Edition	3306
   Oracle (with ODBC wrapper)	1521
   Pivotal Greenplum	5432
   PostgreSQL	5432
   IBM PureData System for Analytics (formerly Netezza)	5480

   CODEPAGE 'codepage_name'

   The CODEPAGE server option must be set to the code page of the data source.

   DBNAME ‘database_name'

   The DBNAME server option must be set to the name of the data source database that you want to access.

   For example If the ODBC data source name is venice, then the DBNAME option should be set to venice.

2. After the server definition is created, use the ALTER SERVER statement to add or drop server options.