(WMB) DB Call in JavaCompute Node

This post shows how to connect to DB in Java compute node & how to construct output message from database result set.
Below diagram shows the database table :

Database (TEST) Schema ( ADMIN) Table (EMP)

Schema :

mxsd

Here is the evaluate method :

public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = inAssembly.getMessage();
MbMessage outMessage = new MbMessage();
MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly,outMessage);
try {
copyMessageHeaders(inMessage, outMessage);
MbElement inRootElement = inMessage.getRootElement();
List<MbElement>  idList =  (List) inRootElement.getLastChild().evaluateXPath("/ID");
int id = (Integer)idList.get(0).getValue();

//Creating Output data
MbElement outRootElement = outMessage.getRootElement();
MbElement messageType = outRootElement.getFirstChild().getFirstElementByPath("MessageType");
messageType.setValue("Response");
MbElement parser = outRootElement.createElementAsLastChild("MRM");

// Conventional way of connecting to DB.
Class.forName("com.ibm.db2.jcc.DB2Driver");  //Driver for DB2
String url = "jdbc:db2://localhost:50000/TEST"; // Database name : TEST
 Connection conn = DriverManager.getConnection(url,"username","password"); 

//Connection conn = getJDBCType4Connection("MYDB",JDBC_TransactionType.MB_TRANSACTION_AUTO); 
   // For this need to configure the configurable service.
  Statement stmt = conn.createStatement
  (ResultSet.TYPE_SCROLL_INSENSITIVE,  
   ResultSet.CONCUR_READ_ONLY);                                                 

 ResultSet resultSet = stmt.executeQuery("SELECT COMPANY, SALARY FROM ADMIN.EMP WHERE ID ="+id);

while(resultSet.next())
{
parser.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"COMPANY",resultSet.getString("COMPANY"));
parser.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"SALARY",resultSet.getInt("SALARY"));
}

out.propagate(outAssembly);

} 
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally {


// clear the outMessage even if there's an exception
outMessage.clearMessage();
}
}


Input :
<Request><ID>834</ID></Request>


Output :
<?xml version="1.0"?>
<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<COMPANY>GALAXY</COMPANY>
<SALARY>900000</SALARY>
</Response>

(WMB) Working with Remote DB

In Message Broker, database can be accessed through predefined database nodes like Database, DatabaseDelete, DatabaseInsert, DatabaseUpdate etc.

Database can also be accessed through Compute and JavaCompute nodes.

Compute nodes are written by using ESQL code. Compute nodes are comparatively faster in performance compared to JavaCompute nodes.

Connecting to DB through compute node :

• A Compute node gets access to a database through System DSN.
• System DSN can be configured through ODBC Data Sources.
• In order to connect to a DB it is mandatory that to have DB specific driver available in the machine.
• We need to give 'DataSourceName' & 'Database Alias'.
• Click on Add next to Database Alias option, a new window opens up, fill the necessary details like database name, hostname, port etc.
• Click Ok.

Story is not over yet, broker still does not know what you have done so far , so the next step would be to update broker configuration by following commands :

mqsistop <<BrokerName>>  {Stop the Broker}

mqsisetdbparms <<BrokerName>> -n <<DataSourceName>> -u <<UserName>> -p <<Password>>

mqsireload <<BrokerName>> -e <<ExecutionGroupName>>

mqsistart <<BrokerName>>  {Start the Broker}