Creating a MYSQL JDBCProvider in IIB 9.0

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.

simple-flow

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

test-evidence

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.

log-evidenceIBM Reference

Interacting with databases by using the JavaCompute node

Enabling JDBC connections to the databases
List of properites and there defintions in a JDBCProvider

Network+ Post Exam wrap up

I took and passed the Comptia Network+ exam the other day (Friday March 14 2014) . My score was 820 on a scale of 100-900, passing being 720. This blog entry is a brain dump of what was on the exam, some opinions regarding it and a review of the materials I took during my study of the exam.

Background

I started my career in IT as a Computer Technician and took the Comptia A+ exam early in my IT career.  Not longer after that I changed  paths to systems development, since then I have had plenty of exposure to production support type of issues but not in a Network Admin type role. Never having taken a deep dive into the networking world, I found preparing for the Network+ exam pretty rewarding. I gained knowledge that will be well utilized as I move into  Architect type roles. I highly recommend individuals looking to advance their career in IT take this exam.

Disclaimer:

This is my experience from the test, obviously everyone’s will be different so take this all this with a grain of salt.

Section 1 of the Exam; The Simulation questions

My test began with 7-10 multi-part, interactive, drag and drop/fill in the details Simulation questions. By far this was the most difficult section of the exam, the worse part about this is their are so many little points to these questions and it is unclear how its graded. Dealing with these questions first was a little jarring, none of the online or book based practice exams contained these types of questions. By the 10th question or so I was convinced I was going to fail the Network+ exam. Eventually this simulation questions where over and the familiar multiple choice questions started. Looking back the fact that these are front loaded is probably a good thing, relatively speaking they are the most stressful part of the test.

Section 2; The Multiple Choice questions

The Network+ exam covered a diverse range (as expected) but their are 2 points that I noticed from this exam.

In general, the questions did not dive as deep as expected.  Considering the size of the content covered this should not be too surprising.

Some of the more modern networking subjects were not covered. For instance I do not recall any questions pertaining to IPV6, MPLS, Multicasting, Failover technologies or Virtualization.

Materials I used to prepare for the test

3 books where the foundation of my studies

  • The Comptia Authorized Network+® Certification Guide eBook authored by Kevin Wallace was a good general guide to the test and subject material.
  • Network Warrior by Gary O’Donoghue;  I found this book to be a great supplement whenever I needed some deeper understanding of a subject. I highly recommend the Network Warrior book in particular.
  • CompTIA Network+® Certification Practice Exams by Robb Tracy; This eBook is a collection of practice questions with an explanation of all the answers. I found the questions to be more difficult than most of the other Exam prep books and the exam itself.

From these I created my own study guide for the exam, this can be found here.

There were a number of websites and mobile apps I used that offered practice exams

Brain dump of the exam

1 question regarding recognizing a topology

A few basic switching questions

A few VLAN questions, one in particular about improving a VOIP situation by moving the VOIP traffic into a VLAN.

Nothing about STP, autonegotiation, trunking or multicasting,

A couple related to RJ45 standards; One question was a drag and drop that asked to put T-568B in the proper order, I was confident regarding the active cables Orange stripe, Orange, Green Stripe and Green at slots 1,2,3 and 6 but did not bother to memorize the blue and brown locations.

1 or 2 Power over Ethernet questions

Decent amount of Wireless LAN questions, In fact one of the simulations was a scenario to set the appropriate antennas for connectivity between 2 building and for the floor plans within the buildings.

Wireless LAN, VPN and Security in general  were well represented on the test but none of the questions where deep dive type of things.

There was an APIPA question

No Netbui or IPX questions

There was 1 or 2 routing protocol questions comparing the different types,

There was a question about NATing with Ports, answer being PAT

There were 1 or 2 Identify the device and its usage, for instance a TDR to identify a short in a cable. But there were no questions where an image of a tool or connector was displayed on the screen and the testee had to identify what it was.

1 RAID question

I was asked to identify ports for DHCP, SSH, DNS, SMTP and SNMP this was a drag and drop type questions where the question was the “useage” and drag the protocol and also its correlating port to an empty slot.  Example: Protocol used to securely connect to a terminal, one would be required to drag ssh and port 22 to the correct slots.

1 Email protocol and port questions

There were 2-3 voip related questions,

A couple of network troubleshooting questions where deciding on the correct order of steps was required

2-5 questions related to IP and subnets, some were easy (identify the class) others where the trickier subnetting type of questions. There was also a  CIDR question

 There was at least 1 identify the difference between cache engine, load balancer, type of questions.

There was a Syslog question

 I don’t recall if their was a single command line question, if their was it was so simple it wasn’t memorable.

There was a multiple choice tools question where NMAP was the answer

There was a RADIUS/TACAS+ question

Dont recall a single “Identify the attack” security question

There was an IDS question

1 or 2 Identify the Connector questions

A SONET size question , 1 DSL question, not a lot of WAN questions in general though.

1 DNS question regarding identify the usage A name, PTR record, MX, etc..

No Active Directory, Windows user account questions