Preparing the federated server to access data sources through ODBC (Linux, UNIX)

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 verify the settings in the odbc.ini file, and test the connection to ODBC data sources. For DSN-less connection mode, you can configure the driver path by specifying the "DRIVER=" keyword in a file named odbcinst.ini. The file odbcinst.ini must exist when an ODBC wrapper works, and this is already set by default for type 1 data sources.

Before you begin

For the Linux® for System z® operating system, you must set the EnableDescribeParam parameter to 1 in the odbc.ini configuration file of the DSN when you use the ODBC wrapper to connect to to Hive data source.

EnableDescribeParam=1

If you do not set the EnableDescribeParam parameter to 1, the 1822N error message is thrown for binding non-char and varchar host variables when the ODBC SERVER object is set to PUSHDOWN mode.

Procedure

Verify that the odbc.ini(for DSN mode) and odbcinst.ini (for DSN-less mode, configured by default for type 1 data sources) files have been updated on the federated server. If the files do not exist, you can create them in a text editor. Consult the documentation from the ODBC client vendor for information about the odbc.ini and odbcinst.ini files.

Configure odbc.ini file in DSN connection mode:

Add the odbc.ini file location into db2dj.ini. For example:
```
ODBCINI=$INSTANCE_HOME/odbc.ini
```

The default driver directory is

$INSTANCE_HOME/sqllib/federation/odbc/lib/

About the parameters in odbc.ini, for MariaDB please refer MariaDB driver official website, for other data sources please refer to Data Direct official website.
The default driver directory for type 1 data sources please see the following table:

Data Source Name	Default driver directory
Apache Hive	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOhive27.so
Apache Spark SQL	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOspark28.so
MariaDB	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/libmaodbc.so
Microsoft Azure	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOsqls28.so
Microsoft SQL Server (with ODBC wrapper)	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOsqls28.so
Oracle MySQL Enterprise Edition	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOmysql27.so
Oracle MySQL Community Edition	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/libmaodbc.so
Oracle (with ODBC wrapper)	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOora28.so
Pivotal Greenplum	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOgplm27.so
PostgreSQL	Driver=$INSTANCE_HOME/sqllib/federation/odbc/lib/FOpsql27.so
IBM PureData System for Analytics (formerly Netezza)	Driver=$INSTANCE_HOME/sqllib/federation/netezza/lib64/libnzodbc.so

Take data source MySQL and MariDB for example, odbc.ini file like this, “mysqltest” and “mariadbtest” will be the node name when you execute create server command.

Note: MySQL community edtion and MariaDB use the same ODBC connector libmaodbc.so, and MySQL enterprise use the connector FOmysql27.so

[ODBC]
InstallDir=/home/db2inst1/sqllib/federation/odbc

[mysqltest]
Driver=/home/db2inst1/sqllib/federation/odbc/lib/FOmysql27.so
HostName=x.x.x.x
PortNumber=3306

[mariadbtest]
Driver=/home/db2inst1/sqllib/federation/odbc/lib/libmaodbc.so
DLOpenMode=10
DriverUnicodeType=1
ServerName=x.x.x.x
PortNumber=3306
Database=testdb

[mysql_ce_test]
Driver=/home/db2inst1/sqllib/federation/odbc/lib/libmaodbc.so
DLOpenMode=10
DriverUnicodeType=1
ServerName=x.x.x.x
PortNumber=3306
Database=testdb

What to do next

After you complete this task, you can register the wrapper.