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.
Type name | Configuration file | Create server statement example |
---|---|---|
DSN | odbc.ini |
|
DSN-less (only for ODBC wrapper) | odbcinst.ini |
|
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 |
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.
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 |