Interacting with databases by using the JavaCompute node
Access databases from Java™ code included in the JavaCompute node.
About this task
If you use JDBCProvider for type 4 connections or MbSQLStatement, the databases that you access can participate in globally coordinated transactions. In all other cases, database access cannot be globally coordinated.
Integration node JDBCProvider for type 4 connections
About this task
You can establish JDBC type 4 connections to interact with databases from your JavaCompute nodes. The integration node supports type 4 drivers, but does not supply them. You must obtain these drivers from your database vendor; for information about supported databases, see IBM® Integration Bus system requirements.
- Use integration node configuration facilities to define the connection, and to provide optional security, in preference to coding these actions.
- Configure the integration node and the databases to coordinate access and updates with other resources that you access from your message flows, except when the integration node is running on z/OS®.
- Use the integration node Java API getJDBCType4Connection to initiate the connection, then perform SQL operations by using the standard JDBC APIs. The integration node manages the connections, thread affinity, connection pooling, and lifecycle. If a connection is idle for approximately 1 minute, or if the message flow completes, the integration node closes the connection.
If the integration node is running on a distributed system, you can configure the databases and the connections to be coordinated with other resource activity. Global coordination on distributed systems is provided by IBM MQ, and can include interactions with local or remote databases, including remote databases that are defined on z/OS systems. If you establish a JDBC type 4 connection to a database from an integration node that is running on z/OS, coordination is not provided. For information about setting up connections and coordination, see Enabling JDBC connections to the databases.
Before you can include this function in the code that you write for the node, you must configure the required environment. Decide whether your database requires security of access, and whether you want the database updates to participate in globally coordinated transactions. For the required and optional tasks, see Enabling JDBC connections to the databases.
When you have configured the JDBCProvider, you can establish a JDBC type 4 connection to the database by using the getJDBCType4Connection call on the MbNode interface. The following code provides an example of its use:
public class MyJavaCompute extends MbJavaComputeNode {
public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = inAssembly.getMessage();
// create new message
MbMessage outMessage = new MbMessage(inMessage);
MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly,outMessage);
try {
// Obtain a java.sql.Connection using a JDBC Type4 datasource - in this example for a
// JDBC broker configurable service called "MyDB2"
Connection conn = getJDBCType4Connection("MyDB2",
JDBC_TransactionType.MB_TRANSACTION_AUTO);
// Example of using the Connection to create a java.sql.Statement
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs0 = stmt.executeQuery("SELECT NAME, CITY FROM MySchema.MyTable");
stmt.executeUpdate("UPDATE MySchema.MyTable SET CITY = \"Springfield\" WHERE Name = \"Bart\"");
.
// Perform other database updates
.
} catch (SQLException sqx ){
sqx.printStackTrace();
} finally {
// Close the artefacts
if (stmt != null) stmt.close();
if (srs0 != null) srs0.close()
// Clear the outMessage
outMessage.clearMessage();
}
}
}
In this example:
- MyDB2 is the name of the JDBCProvider configurable service. Use the name of the service that you have created to connect to your database.
- MySchema is the name of the database schema (not the name of the database).
- MB_TRANSACTION_AUTO defines the level of transaction coordination that is required by the node. Only this value is supported, and indicates that the coordination in the node is inherited from the coordination configured at message flow level.
To indicate a failure (and roll back a transaction), issue an exception from the JavaCompute node and the integration node will handle the rollback.
The primary use of the getJDBCType4Connection call
is in the evaluate()
method of a JavaCompute node, where it
is used to obtain a JDBC connection that is managed by the integration
node.
- Non-pooled connections:
- IBM Integration Bus creates a JDBC connection on demand for each message flow instance that requires one.
- Each JDBC connection is associated with the message flow instance for which it was created. This association is maintained until the connection is closed.
- Each JDBC connection that is idle for 60 seconds is closed, and is no longer associated with a message flow instance.
- After a JDBC connection that was associated with a message flow instance is closed, if the same message flow instance requires a JDBC connection, IBM Integration Bus creates a new JDBC connection on demand.
- Pooled connections:
- When a message flow instance requires a JDBC connection, IBM Integration Bus assigns an unused connection from the pool.
- If all pooled JDBC connections are being used, and the maximum
pool size has not been reached, IBM Integration Bus creates
a new pooled JDBC connection. The maximum pool size is specified in
the
maxConnectionPoolSize
property of the JDBCProviders configurable service. - Each pooled JDBC connection remains associated with a message flow instance only for the processing of one input message.
- When a message flow instance completes the processing of an input message, the association with a JDBC connection is removed, and the JDBC connection is returned to the pool.
- Each pooled JDBC connection that is idle for 15 minutes is closed, and is removed from the pool.
- Pooled JDBC connections are not applicable to the DatabaseRetrieve and DatabaseRoute nodes.
- Do not include code that makes explicit transaction calls such as COMMIT or ROLLBACK. This restriction includes explicit transaction calls in a database stored procedure.
- Do not close a connection, or cache a connection in the JavaCompute node.
- Ensure that your Java code explicitly closes all artifacts, such as JDBC Statements and ResultSets created using a managed JDBC Connection, when those artifacts are no longer used. Otherwise this might lead to integration node JVM memory becoming exhausted.
onitialize()
method of a JavaCompute node. The onitialize()
method
is called once, either during deployment or on integration node startup,
before the message flow starts processing input. You can use the getJDBCType4Connection call
in the onitialize()
method to complete work with
a database before the message flow starts; for example:- To create an in-memory cache of read-only data that is retrieved from a database, to reduce the need to query the database in the message flow
- To prime a database with data before the message flow starts
When using the getJDBCType4Connection in
the onitialize()
method, ensure that any exceptions
that might occur in this processing are handled. Any unhanded exception
causes the deployment or startup of the message flow to fail. For
more information, see JavaCompute node.
MbSQLStatement
About this task
The MbSQLStatement class provides full transactional database access by using ESQL and ODBC. The integration node resource manager coordinates database access when using MbSQLStatement. Global coordination is provided by IBM MQ on distributed systems, and by RRS on z/OS. For information about how to set up the ODBC resources that are required, see Enabling ODBC connections to the databases.
- Calling select() on this object returns the results of the query.
- Calling execute() on this object runs a query where no results are returned, such as updating a table.
MbMessage newMsg = new MbMessage(assembly.getMessage());
MbMessageAssembly newAssembly = new MbMessageAssembly(assembly, newMsg);
String table = "dbTable";
MbSQLStatement state = createSQLStatement( "dbName",
"SET OutputRoot.XMLNS.integer[] = PASSTHRU('SELECT * FROM " + table + "');" );
state.setThrowExceptionOnDatabaseError(false);
state.setTreatWarningsAsErrors(true);
state.select( assembly, newAssembly );
int sqlCode = state.getSQLCode();
if(sqlCode != 0)
{
// Do error handling here
}
getOutputTerminal("out").propagate(assembly);
JDBC API in an unmanaged environment
About this task
You can access standard Java APIs in the code that you write for your JavaCompute nodes, including JDBC calls. You can therefore use JDBC APIs to connect to a database, write to or read from the database, and disconnect from the database. On operating systems other than z/OS, the integration node supports your JDBC connection code calling both type 2 and type 4 JDBC drivers in this environment, but does not supply them. You must obtain these drivers from your database vendor. On z/OS, type 2 drivers are not supported.
If you choose this method to access databases, the integration node does not support managing the transactions; your code must manage the local commit and rollback of database changes. Your code must also manage the connection lifecycle, connection thread affinity, and connection pooling. You must also monitor the access to databases when you use this technique to ensure that these connections do not cause interference with connections made by the integration node. In particular, be aware that type 2 drivers bridge to an ODBC connection that might be in use in message flows that access databases from ESQL.