Message Broker v8 with MySQL

Now that you know that you can use Message Broker v8 on Ubuntu for development, you may be wondering if you can use some of the databases that are more common on Linux, such as MySQL. You can!

IBM WebSphere Message Broker now uses UnixODBC to connect to many databases. Though not supported by IBM, you can actually connect to MySQL from Message Broker using UnixODBC.

If you are on Ubuntu, you can install the MySQL UnixODBC dirver and the MySQL server with apt-get:

apt-get install libmyodbc mysql-server

Your environment variable $ODBCINI should point to your odbc.ini, mine points to /var/mqsi/odbc/odbc.ini and the base file was copied from /opt/IBM/mqsi/8.0.0.0/ODBC/unixodbc/odbc.ini

You need to set a database definition for a mysql database, something like the following (assuming “wmb” is the name of your MySQL database created later)

[MYSQL]
Description     = MYSQL DB
Driver          = MySQL
Database        = wmb
Server          = 127.0.0.1
Port            = 3306

You also need to make sure your $ODBCSYSINI points to the folder (not the file) where your odbcinst.ini file has been copied to. Mine is set to /var/mqsi/odbc and the odbcinst.ini file was copied from /opt/mqsi/8.0.0.0/ODBC/unixodbc/odbcinst.ini, you also need to add a definition to explain to UnixODBC how to connect to a mysql database

The following should be enough to get UnixODBC working with MySQL

#Sample from package libmyodbc
[MySQL]
Description     = MySQL driver
Driver          = /usr/lib/odbc/libmyodbc.so
Setup           = /usr/lib/odbc/libodbcmyS.so
CPTimeout       =
CPReuse         =

Now create a mysql database (mysqladmin create wmb). You may need to GRANT some permissions to your user in MySQL, or add yourself to the mysql group and logout and back in again.

You can now check the database connection using the WMB mqsicvp command, which will also describe which database functions are available:

mqsicvp -n MYSQL -u anton -p password

BIP8270I: Connected to Datasource 'MYSQL' as user 'anton'. The datasource platform is 'MySQL', version '5.1.58-1ubuntu1'.
===========================
databaseProviderVersion      = 5.1.58-1ubuntu1
driverVersion                = 05.01.0006
driverOdbcVersion            = 03.51
driverManagerVersion         = 03.52.0002.0002
driverManagerOdbcVersion     = 03.52
databaseProviderName         = MySQL
datasourceServerName         = 127.0.0.1 via TCP/IP
databaseName                 = wmb
odbcDatasourceName           = MYSQL
driverName                   = libmyodbc5.so
supportsStoredProcedures     = Yes
procedureTerm                = stored procedure
accessibleTables             = No
accessibleProcedures         = No
identifierQuote              = `
specialCharacters            = None
describeParameter            = No
schemaTerm                   =
tableTerm                    = table
sqlSubqueries                = 31
activeEnvironments           = 0
maxDriverConnections         = 0
maxCatalogNameLength         = 192
maxColumnNameLength          = 192
maxSchemaNameLength          = 0
maxStatementLength           = 8192
maxTableNameLength           = 192
supportsDecimalType          = Yes
supportsDateType             = Yes
supportsTimeType             = No
supportsTimeStampType        = No
supportsIntervalType         = No
supportsAbsFunction          = Yes
supportsAcosFunction         = Yes
supportsAsinFunction         = Yes
supportsAtanFunction         = Yes
supportsAtan2Function        = Yes
supportsCeilingFunction      = Yes
supportsCosFunction          = Yes
supportsCotFunction          = Yes
supportsDegreesFunction      = Yes
supportsExpFunction          = Yes
supportsFloorFunction        = Yes
supportsLogFunction          = Yes
supportsLog10Function        = Yes
supportsModFunction          = Yes
supportsPiFunction           = Yes
supportsPowerFunction        = Yes
supportsRadiansFunction      = Yes
supportsRandFunction         = Yes
supportsRoundFunction        = Yes
supportsSignFunction         = Yes
supportsSinFunction          = Yes
supportsSqrtFunction         = Yes
supportsTanFunction          = Yes
supportsTruncateFunction     = Yes
supportsConcatFunction       = Yes
supportsInsertFunction       = Yes
supportsLcaseFunction        = Yes
supportsLeftFunction         = Yes
supportsLengthFunction       = Yes
supportsLTrimFunction        = Yes
supportsPositionFunction     = Yes
supportsRepeatFunction       = Yes
supportsReplaceFunction      = Yes
supportsRightFunction        = Yes
supportsRTrimFunction        = Yes
supportsSpaceFunction        = Yes
supportsSubstringFunction    = Yes
supportsUcaseFunction        = Yes
supportsExtractFunction      = Yes
supportsCaseExpression       = Yes
supportsCastFunction         = Yes
supportsCoalesceFunction     = Yes
supportsNullIfFunction       = Yes
supportsConvertFunction      = No
supportsSumFunction          = Yes
supportsMaxFunction          = Yes
supportsMinFunction          = Yes
supportsCountFunction        = Yes
supportsBetweenPredicate     = Yes
supportsExistsPredicate      = Yes
supportsInPredicate          = Yes
supportsLikePredicate        = Yes
supportsNullPredicate        = Yes
supportsNotNullPredicate     = Yes
supportsLikeEscapeClause     = Yes
supportsClobType             = Yes
supportsBlobType             = Yes
charDatatypeName             = char
varCharDatatypeName          = varchar
longVarCharDatatypeName      = long varchar
clobDatatypeName             = N/A
timeStampDatatypeName        = N/A
binaryDatatypeName           = binary
varBinaryDatatypeName        = varbinary
longVarBinaryDatatypeName    = long varbinary
blobDatatypeName             = N/A
intDatatypeName              = integer
doubleDatatypeName           = double
varCharMaxLength             = 0
longVarCharMaxLength         = 0
clobMaxLength                = 0
varBinaryMaxLength           = 0
longVarBinaryMaxLength       = 0
blobMaxLength                = 0
timeStampMaxLength           = 0
identifierCase               = Mixed
escapeCharacter              = \
longVarCharDatatype          = -1
clobDatatype                 = 0
longVarBinaryDatatype        = -4
blobDatatype                 = 0

BIP8273I: The following datatypes and functions are not natively 
supported by datasource 'MYSQL' using this ODBC driver:
Unsupported datatypes: 'TIME, TIMESTAMP, INTERVAL' Unsupported functions: 'CONVERT'

Examine the specific datatypes and functions not supported
natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the
associated data processing is done within WebSphere Message
Broker rather than being processed by the database provider.  

Note that "functions" within this message can refer to functions or predicates. 

BIP8071I: Successful command completion.

There – done! Now you can use MySQL with WebSphere Message Broker on Ubuntu. Don’t forget to set the database password with mqsisetdbparms before trying to actually connect from your database nodes.

Also, the name of the database according to WMB is “MYSQL” as that is what I defined in the odbc.ini. The name of the database according to mysql is “wmb”. They don’t need to be different, but it might help having unique names if you are trying to track down a database connection issue.

Of course, the mysql database server does not need to be local to the broker – The mysql odbc driver should allow you connect to any accessible tcpip mysql server.