IIB v9 ODBC Configuration Error

IIB v9 ODBC Configuration Error while setting up and validating SQLServer database.

Following error messages are displayed while validating the SQLServer database,

BIP2393E: Database error: ODBC return code ‘-1’ from data source

BIP2322E: Database error: SQL State ”01000”; Native Error Code ‘0’;

Resolution

While using SQLSERVER Database make sure that you are giving proper .so file path and this has to be right one other wise above mentioned error will be displayed. SQL Server driver UKsqls26.so was introduced with IBM Integration Bus V9.0.0.3. If you are at an earlier version than V9.0.0.3, SQL Server driver UKmsss26.so should be used.

So the path used in the ODBC.ini file should be as below,

IIB v9.0.0.2 and v9.0.0.1 use: <Your Broker install directory>/ODBC/V7.0/lib/UKmsss26.so

Above IIB v9.0.0.3 use: <Your Broker install directory>/ODBC/V7.0/lib/UKsqls26.so

So the ODBC.ini file will be update as below (assuming v9.0.0.3),

;# UNIX to SQLServer stanza
[SQLSERVERDB]
Driver=<Your Broker install directory>/ODBC/V7.0/lib/UKsqls26.so
Description=DataDirect 7.1 ODBC SQL Server Wire Protocol
Address=<Your SQLServer Machine Name>,<Your SQLServer Port Number>
;# Alternative way to locate server using a named instance
;# Address=<Your SQLServer Machine Name>\<Your SQLServer Instance Name>
AnsiNPW=Yes
Database=<Your Database Name>
QuotedId=No
ColumnSizeAsCharacter=1

Please note that an alternative way is mentioned to locate server using a named instance. We can either use

1. <Your SQLServer Machine Name>,<Your SQLServer Port Number>

or

2. <Your SQLServer Machine Name>\<Your SQLServer Instance Name>

If 1 fails please use 2 for connecting to the SQLServer instance.

IBM Integration Bus Command to validate the Database is,

mqsicvp <brokername> -n <DB name>

As mentioned in previous blog entry, proper editing of the ODBC ini file will resolve these errors.