Registering the server definitions for an ODBC data source
You must register each ODBC server that you want to access in the federated database.
Procedure
- Issue the CREATE SERVER statement from the command line
and specify the required server options. Example for DSN mode connection:
Example for DSN-less 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');
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:
- 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 ODBC ODBC IBM InfoSphere Classic Federation Server for z/OS data sources ODBC
- 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.
For IBM InfoSphere Classic Federation Server for z/OS data sources: The NODE server option must be set to the data source specified on the DATASOURCE keyword in the cac.ini file.
Example: If the cac.ini file specifies DATASOURCE = CACSAMP tcp/150.45.37.49/5000, then the NODE option should be set to CACSAMP
- 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.
- CODEPAGE 'codepage_name'
- The CODEPAGE server option must be set to the code page of the
data source.
For IBM InfoSphere Classic Federation Server for z/OS data sources: The CODEPAGE server option must be set to the code page number of the client code page specified in the cac.ini file. The client code page is the code page of the data source.
Example: If the cac.ini file specifies CLIENT CODEPAGE = IBM-850, then the CODEPAGE option should be set to 850.
- 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.
- After the server definition is created, use the ALTER SERVER statement to add or drop server options.