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.

About Anton Piatek

I am a Software Developer working for IBM, but a bit of a Ubuntu addict. I love cool tech toys, and am slightly obsessed by photography
This entry was posted in Debian, IBM, Linux, Message Broker, My Job, Ubuntu and tagged , , , , , . Bookmark the permalink.

4 Responses to Message Broker v8 with MySQL

  1. Federico says:

    Hi, thanks for the information!
    Will I be able to use this configuration with version 7, the message broker + suppor IE02 pack?
    Thanks.
    FPG

  2. Anton Piatek says:

    I’m not sure if it will work. IE02 I think is a different version of the UnixODBC driver to that in v8, but I don’t think that will stop it working.

    In v8, UnixODBC I think is the default database driver, whereas in v7 I think UnixODBC was only used for Solid DB databases. You could try it and see if it works.
    You should note that it will not be supported by IBM in this configuration, as IE02 was only tested with Solid DB.

  3. Federico says:

    Anton,

    Thank you very much for the answer.
    So far, we could not make it work with unixODBC Message Broker v7 that comes in IE02.
    This limitation really became a problem for us. We are a bit disillusioned with the product.
    Federico

  4. Anton Piatek says:

    Federico – It was never designed to work in v7. Even in v8 it is not actually supported yet. If you want to use this, you should make sure your IBM Service representative knows about your requirement as that will help make sure that it gets worked into a supported solution sooner.

    Perhaps you could also upgrade to v8, where it does work?

Leave a Reply