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

This blog 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

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 /odbc directory 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

Sample ODBC Files provided with IIB

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

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

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

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

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

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

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 mqsisetdbparms command 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

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

Verifying Integration Node’s connectivity to database using specified DSN

Advertisements

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s