MQSeries.net :: View topic - Broker and H2 database through JDBC

t603 · Posted: Thu Jan 30, 2014 5:43 am Post subject:

Broker and H2 database through JDBC - short HowTo

Hello,

if there is someone who:

is familiar with H2 (or HSQLDB) pure Java SQL database http://www.h2database.com,
want to use H2 for retrieving and-or storing data from WebSphere Message Broker, for example as another "global cache" (beside Singleton or IA91 or new and *recommended* Global Cache since 8.0.0.1) or just as (in-memory or not) database (beside *supported* solidDB like HSQLDB, Derby, TimesTen...),
knows, that following solution is unsupported and bla, bla...

Here is short and fast step-by-step instruction manual to set-up H2 1.3.169, WSMB 8.0.0.1 on MS Windows 7 HE x64 and Oracle Java 1.7.0_09 followed by short example of JavaCompute node with SQL INSERT command below. This manual is intended to whom familiar with features of such kind of databases to minimize his / her effort to bind H2 with WSMB.

Stepan

My environment (very common MS Widnows 7 64bit with Java 1.7.0 configuration):

Microsoft Windows 7 Home Premium, 6.1.7601 Service Pack 1 Build 7601, x64-based PC, running inside VMware Virtual Platform 5.0.1, using 2 cores of IntelÂ® Coreâ„¢ i5-2400S Processor and 3068 RAM
Oracle Java(TM) SE Runtime Environment (build 1.7.0_09-b05), Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode) as a system Java
IBM WebSphere Message Broker and Toolkit, both in version 8.0.0.1

Installation and configuration of H2 database

Download the latest stable H2 database ZIP distribution from http://www.h2database.com/html/download.html. As at 2012-12-15 it is 1.3.169.
Unzip downloaded ZIP distribution to e.g. "C:\Program Files\h2", so the path to the H2 JAR is "C:\Program Files\h2\bin\h2-1.3.169.jar".
Optional: Read the H2 documentation on "C:\Program Files\h2\docs\h2.pdf" or "C:\Program Files\h2\docs\index.html".
Create directory e.g. "C:\Temp\H2Data", where will be stored database data.
Create "C:\Program Files\h2\startH2server.bat" (optional with -trace and or other H2 startup parameters and Java -Xmx and other parameters) as:

Code:

java -cp .\bin\h2*.jar org.h2.tools.Server -tcp -tcpPort 9092 -baseDir "C:/Temp/H2Data" -trace
Create "C:\Program Files\h2\stopH2server.bat" as:

Code:

java -cp .\bin\h2*.jar org.h2.tools.Server -tcpShutdown "tcp://localhost"
Run "C:\Program Files\h2\startH2server.bat" to start H2 server.

Connect to already started H2 server using e.g. SQuirreL SQL client 3.4.0 using:

Code:

Driver definition:
Name: H2 TCP Server
Example URL: jdbc:h2://<server>:<9092>/<db-name>
Website URL: http://www.h2database.com
Java Class Path:<keep unchanged>
Extra Class Path: "C:\Program Files\h2\bin\h2-1.3.169.jar"
Class Name: org.h2.Driver

Code:

Alias definition:
Name: H2 TCP Server for WSMB
Driver: H2 TCP Server
URL: jdbc:h2:tcp://localhost:9092/WSMB
User Name:<keep null>
Password:<keep null>

Create user e.g. myUser and (optional for example below) table e.g. PUBLIC.POKUS:

Code:

drop user if exists myUser;
create user if not exists myUser password 'myPassword';
--below is optional part of this SQL batch
drop table if exists PUBLIC.POKUS;
create table if not exists PUBLIC.POKUS (CISLO int, RAZITKO timestamp);
grant all on PUBLIC.POKUS to myUser;
insert into PUBLIC.POKUS (CISLO, RAZITKO) values (1, current_timestamp());
select CISLO, RAZITKO from PUBLIC.POKUS;

Optional: Keep connected in SQuirreL SQL client to see changes. You can re-connect as user myUser.

Configuration of WebSphere Message Broker to interact with H2
KEEP ALL COMMANDS ON SINGLE LINE

Create JDBC configurable service H2; stop and start the broker. REMOVE SPACES AMONG ITEMS OF -n AND -v VALUES FROM THE COMMAND BELOW BEFORE SENDING TO THE BROKER

Code:

mqsicreateconfigurableservice MB8BROKER -c JDBCProviders -o H2 -n "connectionUrlFormat", "connectionUrlFormatAttr1", "connectionUrlFormatAttr2", "connectionUrlFormatAttr3", "connectionUrlFormatAttr4", "connectionUrlFormatAttr5", "databaseName", "databaseSchemaNames", "databaseType", "databaseVersion", "description", "environmentParms", "jarsURL", "jdbcProviderXASupport", "maxConnectionPoolSize", "portNumber", "securityIdentity", "serverName", "type4DatasourceClassName", "type4DriverClassName" -v "jdbc:h2:tcp://[serverName]:[portNumber]/[databaseName];USER=[user];PASSWORD=[password]", "", "", "", "", "", "WSMB", "PUBLIC", "H2", "1.3.169 (2012-09-09)", "H2 TCP Server for WSMB", "default_none", "C:\Program Files\h2\bin", "false", "0", "9092", "H2SecurityIdentity", "localhost", "org.h2.jdbcx.JdbcXAConnection", "org.h2.Driver"

See properties of JDBC configurable service H2

Code:

mqsireportproperties MB8BROKER -c JDBCProviders -o H2 -r

Optionaly change properties of JDBC configurable service H2; stop and start the broker. REMOVE SPACES AMONG ITEMS OF -n AND -v VALUES FROM THE COMMAND BELOW BEFORE SENDING TO THE BROKER

Code:

mqsichangeproperties MB8BROKER -c JDBCProviders -o H2 -n "connectionUrlFormat", "connectionUrlFormatAttr1", "connectionUrlFormatAttr2", "connectionUrlFormatAttr3", "connectionUrlFormatAttr4", "connectionUrlFormatAttr5", "databaseName", "databaseSchemaNames", "databaseType", "databaseVersion", "description", "environmentParms", "jarsURL", "jdbcProviderXASupport", "maxConnectionPoolSize", "portNumber", "securityIdentity", "serverName", "type4DatasourceClassName", "type4DriverClassName" -v "jdbc:h2:tcp://[serverName]:[portNumber]/[databaseName];USER=[user];PASSWORD=[password]", "", "", "", "", "", "WSMB", "PUBLIC", "H2", "1.3.169 (2012-09-09)", "H2 TCP Server for WSMB", "default_none", "C:\Program Files\h2\bin", "false", "0", "9092", "H2SecurityIdentity", "localhost", "org.h2.jdbcx.JdbcXAConnection", "org.h2.Driver"

Set username and password; stop and start the broker

Code:

mqsisetdbparms MB8BROKER -n jdbc::H2SecurityIdentity -u myUser -p myPassword
Assign security indentity to JDBC configurable service H2; stop and start the broker

Code:

mqsichangeproperties MB8BROKER -c JDBCProviders -o H2 -n securityIdentity -v H2SecurityIdentity
H2 database runs in its own Java VM. Keep in mind configuration of JDBC Connection Pooling for JDBC configurable service H2 within WSMB for massive usage.

Optional: Creating and deploying message flow, that works with H2 database

Create three queues within MB8BROKER: "in_q_A", "out_q_A", "out_q_Error".
Create message flow e.g. "testC.msgflow" containg four nodes: MQInput: MQ Input, JavaCompute: Java Compute, MQOutput: MQ Output and MQOutput: errorQ.

Create Java code for node JavaCompute: Java Compute as "testC_JavaCompute.java" using Toolkit wizzard and after gerenation of the template change it to:

Code:

import com.ibm.broker.javacompute.MbJavaComputeNode;
import com.ibm.broker.plugin.*;
import java.sql.Connection;
//import java.sql.ResultSet;
//import java.sql.SQLException;
import java.sql.Statement;

public class testC_JavaCompute extends MbJavaComputeNode {
public void evaluate(MbMessageAssembly contact admin) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = contact admin.getMessage();
MbMessage outMessage = new MbMessage(inMessage);
MbMessageAssembly outAssembly = new MbMessageAssembly(contact admin, outMessage);
try {
Connection conn = getJDBCType4Connection("H2", JDBC_TransactionType.MB_TRANSACTION_AUTO);
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into PUBLIC.POKUS (CISLO) values(2)");
conn.commit();//if You want to commit transaction by Yourself
out.propagate(outAssembly);
} catch (Throwable e) {
MbUserException mbue = new MbUserException(this, "evaluate()", "", "", e.toString(), null);
throw mbue;
}
}
}

Deploy "testC.msgflow" to any execution group e.g. "default" on MB8BROKER.
Optionaly debug "testC.msgflow", if You already set up any Flow Debug Port on deployed execution group.
Put any XML message to "in_q_A".
Message should end in "out_q_A". There should be another record in PUBLIC.POKUS - see it in SQuirreL SQL client.
Run "C:\Program Files\h2\stopH2server.bat" to stop H2 server.

Optional: Removing JDBC configurable service H2

Delete JDBC configurable service H2; stop and start the broker

Code:

mqsideleteconfigurableservice MB8BROKER -c JDBCProviders -o H2