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)
IBM Integration Bus Runtime Component installed on the Linux machine
Integration Node to be used is created
Database installed and configured on remote machine
Step 1: Configuring odbc.ini File For ODBC Connectivity in IBM Integration Bus Runtime Server (Linux)
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 /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
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 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
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