IBM Integration Bus, ODBC and DB2

Following on from my previous post: -

Ah, ODBC, it's been a while

having set up DB2 and ODBC on my Red Hat box, I then configured IBM Integration Bus (IIB) to utilise it.

Firstly, I tested the connectivity from IIB: -

mqsicvp IB9NODE

BIP8873I: Starting the component verification for component 'IB9NODE'. 
BIP8876I: Starting the environment verification for component 'IB9NODE'. 
BIP8894I: Verification passed for 'Registry'. 
BIP8894I: Verification passed for 'MQSI_REGISTRY'. 
BIP8894I: Verification passed for 'Java Version - 1.7.0 IBM Linux build pxa6470sr8fp10-20141219_01(SR8 FP10)
BIP8894I: Verification passed for 'MQSI_FILEPATH'. 
BIP8878I: The environment verification for component 'IB9NODE' has finished successfully. 
BIP8882I: Starting the WebSphere MQ verification for component 'IB9NODE'. 
BIP8886I: Verification passed for queue 'SYSTEM.BROKER.ADMIN.QUEUE' on queue manager 'IB9QMGR'. 
BIP8886I: Verification passed for queue 'SYSTEM.BROKER.EXECUTIONGROUP.QUEUE' on queue manager 'IB9QMGR'. 
BIP8886I: Verification passed for queue 'SYSTEM.BROKER.EXECUTIONGROUP.REPLY' on queue manager 'IB9QMGR'. 
BIP8884I: The WebSphere MQ verification for component 'IB9NODE' has finished successfully. 
BIP8290I: Verification passed for the ODBC environment. 
BIP8270I: Connected to Datasource 'SAMPLE' as user 'db2inst1'. The datasource platform is 'DB2/LINUXX8664', version '10.05.0005'. 
BIP8275I: Verification passed for User Datasource 'SAMPLE'. 
BIP8292I: '1' User data sources were not verified, because they do not have mqsisetdbparms credentials. 
BIP8874I: The component verification for 'IB9NODE' has finished successfully. 
BIP8071I: Successful command completion. 

and: -

mqsicvp IB9NODE -n SAMPLE

BIP8290I: Verification passed for the ODBC environment. 

BIP8270I: Connected to Datasource 'SAMPLE' as user 'db2inst1'. The datasource platform is 'DB2/LINUXX8664', version '10.05.0005'. 
===========================
databaseProviderVersion      = 10.05.0005
driverVersion                = 10.05.0005
driverOdbcVersion            = 03.51
driverManagerVersion         = 03.52.0002.0002
driverManagerOdbcVersion     = 03.52
databaseProviderName         = DB2/LINUXX8664
datasourceServerName         = db2inst1
databaseName                 = SAMPLE
odbcDatasourceName           = SAMPLE
driverName                   = libdb2.a
supportsStoredProcedures     = Yes
...

BIP8071I: Successful command completion. 

Now, purely for the record, I discovered that IIB "stores" it's ODBC configuration here: -

/var/mqsi/registry/IB9NODE/CurrentVersion/DSN/

As an example, when I registered the DSN ( Data Source Name ) for IIB: -

mqsisetdbparms IB9NODE -n SAMPLE -u db2inst1 -p passw0rd

this is what I get: -

cat /var/mqsi/registry/IB9NODE/CurrentVersion/DSN/SAMPLE/UserId

db2inst1

cat /var/mqsi/registry/IB9NODE/CurrentVersion/DSN/SAMPLE/Password 

8f30eede9d43eead9934a99ddb46bff9

I tell you this purely because I spent quite a while pulling my hair out, whilst I was trying to resolve an IIB > ODBC > DB2 exception, as evidenced in the IIB logs

cat /var/log/user.log 

...

Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 2/7) BIP2230E: Error detected whilst processing a message in node 'customerService.Compute'.
Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 3/7) BIP2488E:  (.customerService_Compute.Main, 6.3) Error detected whilst executing the SQL statement 'retrieveEmployee(dbResultSetRef);'.
Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 4/7) BIP2934E: Error detected whilst executing the function or procedure 'retrieveEmployee'.
Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 5/7) BIP2488E:  (DatabaseService.EMPLOYEE_OPS_GROUP.retrieveEmployee, 3.2) Error detected whilst executing the SQL statement 'SET dbResultSetRef.row[ ] = SPECIFICPASSTHRU('SELECT EMPNO, FIRSTNME, LASTNAME, MIDINIT FROM DB2INST1.EMPLOYEE', Database.SAMPLE);'.
Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 6/7) BIP2393E: Database error: ODBC return code '-1' from data source '' using ODBC driver manager '/opt/ibm/IE02/2.0.1/lib/libodbcinterface.so'.
Jun 18 11:28:21 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 7/7) BIP2322E: Database error: SQL State '08001'; Native Error Code '-1013'; Error Text '[unixODBC][IBM][CLI Driver] SQL1013N  The database alias name or database name " " could not be found.  SQLSTATE=42705 '.
Jun 18 11:28:22 bpmdemo IIB[80541]: IBM Integration Bus v9002 (IB9NODE.IIB9) [Thread 92667] (Msg 1/1) BIP2648E: Message backed out to a queue; node 'customerService.Input'.
...

Note - As per this post - IBM Integration Bus - Oh, that's where the logs are hiding ... - I have forced rsyslog to locate the IIB logs in /var/log/user.log

The solution ?

The Compute Node that I was using inside my Message Flow .... yes, that wasn't configured to actually use the datasource that I'd spent ages creating :-)

Once I fixed that, it all started working :-)

But that's another post for another day .....