Configuring access to data sources through ODBC wrapper

To configure the federated server to access ODBC data sources, you must provide the federated server with information about the data sources and objects that you want to access.

Before configuring the federated server to access ODBC data sources, we should know if you need to download the ODBC driver by yourself, how to configure data sources, and if the data source has been optimized.

On federated servers that run Linux® or UNIX, there are two methods to access ODBC data source, namely DSN connection mode and DSN-less connection mode.

For DSN connection mode, to prepare the federated server, you must configure the data source information in the odbc.ini file, and in CREATE SERVER statement, you must use “NODE” option to specify the remote data source you configured in odbc.ini.

For DSN-less connection mode, you can configure the driver path by specifying the "DRIVER=" keyword in a file named odbcinst.ini which is required when ODBC wrapper is used, and this file has been already set by default for type 1,2,3 data sources, so for these types of data sources, the user doesn’t need to do any configuration manually. For DSN-less connection mode, you must use “HOST” option to specify the remote data source in the CREATE SERVER statement.

Table 1. The difference between DSN and DSN-less
Type name Configuration file Create server statement example
DSN odbc.ini 
CREATE SERVER server_name TYPE mysql
VERSION 5.0 WRAPPER odbcwrapper
OPTIONS (NODE 'mysql_node', DBNAME venice')
DSN-less (only for ODBC wrapper) odbcinst.ini 
CREATE SERVER server_server TYPE mysql
VERSION 5.0 WRAPPER odbcwrapper
OPTIONS (HOST 'mysql.cn.ibm.com’,PORT ‘3306’ DBNAME 'venice')
Table 2. ODBC data sources categorization
Type name If need to download driver If need configuration (for DSN-less connection) If optimized*
Type 1 N N Y
Type 2 Y N Y
Type 3 N N N
Type 4 Y Y N
Note: * Y means the ODBC wrapper is optimized to access these data sources. Data type mapping is optimized to keep the data precision, function mapping and server option are optimized to improve the performance with better SQL pushdown.

N means customer can also use federation server to access them and perform basic operations like querying remote data via ODBC drivers, as long as the required ODBC driver comply with ODBC standard. But they are not optimized so there might be some data types and functions which are not supported correctly, and the performance is not expected to be as good as the optimized data sources.

About the data source and its type please refer to table 3 below.

Table 3. Data source and type mapping table
Data source Type Quick start guide
Apache Hive Type 1 Sample for Apache Hive
Apache Spark SQL Type 1 Sample for Spark SQL
Cloudera Impala Type 1 Sample for Impala
IBM® Red Brick® Warehouse Type 4  
IBM PureData® System for Analytics (formerly Netezza®) Type 1 Sample for PureData System
IBM InfoSphere® Classic Federation Server for z/OS® data sources Type 4  
MariaDB Type 1 Sample for MariaDB
Microsoft Azure Type 1  
Microsoft Excel workbooks Type 4  
Microsoft SQL Server (with ODBC wrapper) Type 1 Sample for SQL Server
Other ODBC data sources Type 4  
Oracle (with ODBC wrapper) Type 1 Sample for Oracle
Oracle MySQL Enterprise Edition Type 1 Sample for MySQL EE
Oracle MySQL Community Edition Type 1 Sample for MySQL CE
Oracle MySQL Type 1  
Pivotal Greenplum Type 1 Sample for Greenplum
Pivotal HAWQ Type 3  
PostgreSQL Type 1 Sample for PostgreSQL
Salesforce Type 3  
SAP HANA Type 2 Sample for SAP HANA
Snowflake Type 2 Sample for Snowflake