When creating a JDBCProvider in IBM Integration Bus (IIB), IBM provides a few default database services, these include.

DB2

Informix

Microsoft SQL Server

Oracle

Sybase

That’s a pretty limited group, many popular databases are not included here but understand that these are merely templates. Any Database that can interact via JDBC can be configured to work in IIB. This tutorial covers the steps to create, configure and test a MySQL JDBCProvider in IIB. The assumption is their is a local MySQL database running on port 3306. Additionally the database name connecting to in this example is called “sandbox”. Edit values as needed to make this tutorial work for your particular situation.

1. Download the MYSQL J Connector Libraries

Download the library file from the Mysql site “http://dev.mysql.com/downloads/connector/j”. As of Feb 2015 the windows download is a zip or tar file, unzip this and grab the jar file at the root. Example file name “mysql-connector-java-5.0.8-bin.jar”. Place this in an accessible location.

2. Test Simple Connection outside of IIB

Using the MySQL J Connector jar file prove a connection to the MySQL DB. A quick way to do this is to create a java class that connects to the MySQL database. Add the MySQL J Connector to the classpath for compilation and runtime. This code snippet will connect and execute a sql statement in MySQL.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class HelloMySQL { public static void main(String[] args) throws Exception { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/sandbox? user=sandbox&password=sandbox&database=sandbox"); stmt = conn.createStatement(); rs = stmt.executeQuery("select now()"); while(rs.next()) System.out.println(rs.getString(1)); }catch(Exception e) { throw e; }finally { rs.close(); stmt.close(); conn.close(); } } }

Run the Java code and verify you get the result of a timestamp, if you do.. you have verified MYSL J Connector is working.

3. Create Secret Identity object

In IIB a User name/password combination is stored as a SecurityIdentity Object. It is created separately from the associated Configureable service. The SecurityIdentity object is the input to a Configureable service not actual user name/password details.

#comand to create a security Identity mqsisetdbparms IB9NODE -n jdbc::mysqlLocalRoot -u root -p mypassword

4. Create Configureable Service

Generate the MYSQL JDBC Provider by executing the following command in IIB Console

#command to create a MYQSL JDBC Provider mqsicreateconfigurableservice IB9NODE -c JDBCProviders -o mysql_local_root -n connectionUrlFormat,connectionUrlFormatAttr1,connectionUrlFormatAttr2,connectionUrlFormatAttr3,connectionUrlFormatAttr4,connectionUrlFormatAttr5,databaseName,databaseSchemaNames,databaseType,databaseVersion,description,environmentParms,jarsURL,jdbcProviderXASupport,maxConnectionPoolSize,portNumber,securityIdentity,serverName,type4DatasourceClassName,type4DriverClassName -v "jdbc:mysql://[serverName]:[portNumber]/[databaseName]?user=[user]&password=[password]&database=[databaseName]","","","","","","sandbox","useProvidedSchemaNames","MYSQL","default_Database_Version","default_Description","default_none","C:\lib\mysql","false","0","3306","mysqlLocalRoot","localhost","com.mysql.jdbc.jdbc2.optional.MysqlDataSource","com.mysql.jdbc.Driver"

Here is a more readable version that generates the initial JDBCProvider then updates the values one property at a time. Note The initial command of “mqsicreateconfigurableservice” and subsequent “mqsichangeproperties” calls. When going this route remember to restart your control broker (integration node) after completing all the updates.

mqsicreateconfigurableservice IB9NODE -c JDBCProviders -o mysql_local_root mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n securityIdentity -v mysqlLocalRoot mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n connectionUrlFormat -v "jdbc:mysql://[serverName]:[portNumber]/[databaseName]?user=[user]&password=[password]&database=[databaseName]" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n databaseName -v "sandbox" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n jarsURL -v "C:\lib\mysql" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n portNumber -v "3306" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n type4DatasourceClassName -v "com.mysql.jdbc.jdbc2.optional.MysqlDataSource" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n type4DriverClassName -v "com.mysql.jdbc.Driver" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n serverName -v "localhost" mqsichangeproperties IB9NODE -c JDBCProviders -o mysql_local_root -n databaseType -v "MYSQL"

6. Test your JDBC Provider in IIB Toolkit

Create a simple flow that contains a Java Compute Node.

Wire up the queue names properly and place the following code into the Java Compute Node.

import java.sql.Connection; import com.ibm.broker.javacompute.MbJavaComputeNode; import com.ibm.broker.plugin.MbElement; import com.ibm.broker.plugin.MbException; import com.ibm.broker.plugin.MbMessage; import com.ibm.broker.plugin.MbMessageAssembly; import com.ibm.broker.plugin.MbOutputTerminal; import com.ibm.broker.plugin.MbUserException; public class DBTest_JavaCompute extends MbJavaComputeNode { public void evaluate(MbMessageAssembly inAssembly) throws MbException { MbOutputTerminal out = getOutputTerminal("out"); MbMessage inMessage = inAssembly.getMessage(); MbMessageAssembly outAssembly = null; try { // create new message as a copy of the input MbMessage outMessage = new MbMessage(inMessage); MbElement root = outMessage.getRootElement(); outAssembly = new MbMessageAssembly(inAssembly, outMessage); Connection conn = getJDBCType4Connection("mysql_local_root", JDBC_TransactionType.MB_TRANSACTION_AUTO); } catch (MbException e) { throw e; } catch (RuntimeException e) { throw e; } catch (Exception e) { throw new MbUserException(this, "evaluate()", "", "", e.toString(), null); } out.propagate(outAssembly); } }

Testing the message flow should be successful

If something went wrong along the way open the Windows event viewer and take a look at the logs most likely the culprit is a misspelt/missing value or incorrect jarURL property. You may want to restart the broker as well just to be safe.

IBM Reference

Interacting with databases by using the JavaCompute node

Enabling JDBC connections to the databases

List of properites and there defintions in a JDBCProvider