ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Broker and H2 database through JDBC - short HowTo

Post new topic  Reply to topic
 Broker and H2 database through JDBC - short HowTo « View previous topic :: View next topic » 
Author Message
t603
PostPosted: Mon Dec 17, 2012 6:38 am    Post subject: Broker and H2 database through JDBC - short HowTo Reply with quote

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

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

Back to top
View user's profile Send private message
agulowaty
PostPosted: Thu Jan 30, 2014 5:43 am    Post subject: Reply with quote

Newbie

Joined: 21 Aug 2013
Posts: 8

Hello,

Have you tried to configure WMB with H2 running in embedded mode (and completly in-memory) ?

Best regards
Back to top
View user's profile Send private message
t603
PostPosted: Tue Feb 11, 2014 12:49 am    Post subject: Reply with quote

Voyager

Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe

agulowaty wrote:
Have you tried to configure WMB with H2 running in embedded mode (and completly in-memory)?


No, I have not. I tried to use H2 as an something like standard "global cache". So that was my way of experiments with H2.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Broker and H2 database through JDBC - short HowTo
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.