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.
- 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.
- Change the ownership of the /odbc/odbc.ini file to mqbrkrs group and also its permissions 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.
- 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>
- 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
- Create a new DSN Entry NEBULADB in the /odbc/odbc.ini file to the Oracle XE database specifying the connection details as shown
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
- 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