Sometimes the simple things can seem difficult, but configuring JDBC with Message Broker is a pretty straight forward task.

Typically, you are connecting to a database from a message flow application, which requires you to configure the message flow node that indicates the actual database call, a JDBC configurable service defined at the broker level, which allows the database call to be sent to the correct database and security, so that the DB knows who is making that call.

There are several built-in nodes provided in WebSphere Message Broker (WMB) that use JDBC by default such as - DatabaseRetrieve node, DatabaseRoute node, and Mapping node. However, most of the time, we see users that are using a Java™ compute node or a custom java plug-in node to make JDBC calls to external application databases.

The only requirement for a WMB-JDBC setup is for a type 4 JDBC driver, that can be procured from the database vendor. It is this driver that facilitates the connection between WMB and the database. For a list of supported drivers, please visit the WMB System Requirements page entitled "System requirements for WebSphere Message Broker 8.0".

Once you install the supported JDBC driver on the broker machine, you can simply follow this procedure for configuring JDBC with broker:

1. Point your message flow node to the datasource to which you want to make a JDBC call. So, if you are using a Java compute node to do so, then ensure that it is coded correctly as shown in the following example.
   Example: Connection DB2_Connection = this. getJDBCType4Connection("DB2_1",JDBC_TransactionType.MB_TRANSACTION_AUTO)
   
   
2. Modify the already existing default JDBCProviders provided with broker installation through mqsichangeproperties as shown in the example below:
   Example: mqsichangeproperties V8BK -c JDBCProviders -o DB2_1 -n databaseVersion -v 9.7
   This can be done for all the attributes you need to set on the configurable service.
   
   OR
   
   Instead of using the default configurable service as shown in the above example, you may have a requirement to create a new configurable service. This can be done using the command mqsicreateconfigurableservice that creates the service with default/custom objects and values as shown in the example below:
   Example: mqsicreateconfigurableservice V8BK -c JDBCProviders -o DB2_1 -n connectionUrlFormat, databaseName, type4DatasourceClassName, type4DriverClassName
   -v "jdbc:db2://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password]; V8_DB, com.ibm.db2.jcc.DB2DataSource,com.ibm.db2.jcc.DB2Driver"
   
   OR
   
   You can do either of the above steps using the WMB Explorer as shown in screenshot below:
   
   
   Explorer example (click image to enlarge)
   
   
   
3. If user and password are specified in connectionUrlFormat, then define the securityIdentity as per following steps:
   Run mqsisetdbparms to associate the securityIdentity with userID and password:
   Example: mqsisetdbparms V61BK -n jdbc::DB2DataSource1 -u myuserid -p secretpw
   
   Once the securityIdentity is created then point your broker configurable service to this securityIdentity as shown below:
   Example: mqsichangeproperties V8BK -c JDBCProviders -o DB2_1 -n securityIdentity -v DB2DataSource1

This finishes the WMB-JDBC set up. After setting up all these parameters, you can simply run the message flow.

If you have any questions on the above set up, please let me know.



Tags:  message connectivity bip6233e broker wmb jdbc bip6233 websphere