IBM Integration Bus: Enabling ODBC Connectivity for Integration Node on Linux – Illustrated for Oracle Database

This post covers the steps required for configuring ODBC connectivity to remote database for Integration node running on Linux Server, in IBM Integration Bus v9. The steps are illustrated using Oracle XE Database.

Pre-requisites (Out-of-scope of this blog)

1. IBM Integration Bus Runtime Component installed on the Linux machine
2. Integration Node to be used is created
3. Database installed and configured on remote machine

Step 1: Configuring odbc.ini File For ODBC Connectivity in IBM Integration Bus Runtime Server (Linux)

1. Create a new directory, say /odbc, to place the ODBC related ini files and configuring them to our requirement.

User Defined ODBC directory

• Copy the odbc.ini & odbcinst.ini sample files supplied with IIB Runtime component, located in <IIB_Install_dir>/ODBC/unixodbc/ directory, to the /odbcdirectory we had created for the purpose. Each broker service user ID on the system can therefore use its own DSN definitions.

Sample ODBC Files provided with IIB

Copying Sample ODBC files provided with IIB to the user defined /odbc directory

• Change the ownership of the /odbc/odbc.ini file to mqbrkrs group and also its permissions to 664

Changing the ownership of /odbc/odbc.ini file to mqbrkrs group and setting permission level to 664

• Change the ownership of /odbc/odbcinst.ini file to mqm user and mqbrkrs group and retain its permission as per the sample file provided with IIB.

Changing the ownership of /odbc/odbcinst.ini file to mqm user & mqbrkrs group

• Login to the IIB Admin user account, say iibadmin, and configure the user’s profile file to set the ODBC related environment variables – ODBCINI & ODBCSYSINI

export ODBCINI = <path>/odbc.ini

export ODBCSYSINI = <path of odbcinst.ini file>

Setting the ODBC environment variables in the IIB administrator profile file .profile (or) .bash_profile in the user’s home directory

• Edit the odbc.ini file, in /odbc folder to edit the last stanza of it to specify the required driver details and adding the required DSN entry for the Oracle XE database

Adding ODBC DSN entry for Oracle database in the ODBC.ini

• Create a new DSN Entry NEBULADB in the /odbc/odbc.ini file to the Oracle XE database specifying the connection details as shown

DSN entry added to /odbc/odbc.ini specifying Oracle database connection details

Step 2: Configuring Integration Node for ODBC Connectivity

• Login to the IIB Server using the IIB administrator user id, say iibadmin, and start the Integration Node, if not running already
• Associate the ODBC DSN to the Integration node using the mqsisetdbparmscommand as shown below and enter the db2 password when prompted.
  mqsisetdbparms <Integration Node> -n <DSN Name> -u <DB user>
   e.g: To associate our DSN IIBDB_ODBC to the TRNGBRK Integration Node using the user id db2admin, use the following command. Enter the password for the db2admin user when prompted mqsisetdbparms TRNGBRK -n NEBULADB -u nebula

Associating ODBC DSN to Integration Node

• To verify the Integration Node connectivity to the DB2 database using the associated DSN, use the command mqsicvp as shown below

mqsicvp <Integration Node Name> -n <DSN Name>

 e.g: To test connectivity to DB2 database using NEBULADB DSN from TRNGBRK integration node, use the following command

 mqsicvp TRNGBRK -n NEBULADB

Verifying Integration Node’s connectivity to database using specified DSN