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' SSL_KEYSTORE 'ssl_keystore' );

   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', SSL_KEYSTORE 'ssl_keystore');

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

   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
   SAP HANA1 2 3	HANA

   ¹ 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.

   ³ 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
   SAP HANA	30015

   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.

   SSL_KEYSTORE 'ssl_keystore'

   Specifies the keystore file path when Transport Layer Security (TLS) certificate is enabled. This parameter is optional. This option is only valid for SAP HANA data source. Before you can use this option, the DB2LIBPATH and SECUDIR variables in db2dj.ini must be configured to the path of the SAP Common Crypto Library.

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