Lately, we have been seeing some problem records (PMRs) from WebSphere Message Broker (WMB) V8 customers reporting ODBC connectivity problems when making SQL calls from a message flow ESQL compute node to an external database.

Once the message flow containing the SQL calls is deployed to the Execution group and it receives its first message that causes the SQL call to be processed, then the broker would attempt to make its first connection with the database. This connection is facilitated by the ODBC driver that allows the broker to connect to the database server directly.

WMB V8 ships unixODBC as its driver manager that uses DataDirect database drivers to interface with Oracle, Sybase, and SQLServer databases. These drivers can be located under /opt/IBM/mqsi/8.0.0.1/ODBC/V6.0/lib directory. For broker to use the correct ODBC driver and DB connection-related properties for the concerned database, there is some required configuration to be performed at the broker level.

Here is the checklist for the configuration steps to enable ODBC connections on distributed systems:

1. Configure your odbc.ini and odbcinst.ini files as discussed in Sample WebSphere Message Broker ODBC configuration files
   
   
2. Ensure that you source /opt/IBM/mqsi/8.0.0.x/bin/mqsiprofile
   
   
3. Database managers typically provide a database environment setup profile to be sourced on the applications (such as WMB) accessing them. This may or may not be required for the database you are trying to connect to.
   For example, db2profile for DB2, oraenv for Oracle. For details,please see Command environment: Linux and UNIX® systems
   
   
4. The following environment variables are exported in broker service Id's .profile:
   ODBCINI pointing to the actual odbc.ini file. For example, /var/mqsi/ODBC/V6.0/odbc.ini
   ODBCSYSINI pointing to the directory containing odbcinst.ini file. For example, /var/mqsi/ODBC/V6.0
   
   
5. To set the datasource userId at broker level, run the following command:
   mqsisetdbparms <broker> -n <DB name> -u <user Id> -p <password>
   
   
6. Connect to your database directly to verify the above user Id and password from broker machine (if DB client is installed) or from the DB machine directly.
   
   
7. Run the commands:
   mqsicvp <broker>
   mqsicvp -n <DSN name> -u <user Id> -p <password>
   
   
8. Once the above test is successful, then you should be able to process the transactions through your message flow.
   

In case of any problems related to database configuration, see this document as a quick reference "Webcast replay: Debugging top ten WebSphere Message Broker V7.x/8.x problems with databases on Windows/UNIXes". It covers quite a few ODBC problems in great detail with their solutions.

Let me know if there are any questions.



Tags:  odbc wmb unix websphere message broker