Configuring database persistence in JBossMQ

From TechnologicalWiki

In our example, JBoss AS (version 4.2.3) is installed in /opt/java/jboss and profile 'default' is started. We are using JBoss

JBoss AS uses Hypersonic for persist JMS messages. Hypesonic is a small database enough to develop but it's not good for production environment: It's a slow system and sometimes the database corrupts and JMS service stops working.

For this reason, you have to change JBossMQ persistence to another database. We are going to change to Oracle.

Note: In newer JBoss AS (I don't remember version), JBossMQ is replaces by JBoss Messaging.

[edit] Configuring Oracle Datasource

Download JDBC driver for Oracle from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html and copy ojdbc.jar to $JAVA_HOME/jre/lib/ext or %JBOSS_HOME/server/default/lib.

Delete the default Hypersonic Datasource linked to jndi java:DefaultDS.

   [root@neodoo-01 ~]# rm $JBOSS_HOME/server/default/deploy/hsqldb-ds.xml

Configure and copy Oracle Datasource to $JBOSS_HOME/server/default/deploy.

   [root@neodoo-01 ~]# cp $JBOSS_HOME/docs/examples/jca/oracle-ds.xml $JBOSS_HOME/server/default/deploy

   [root@neodoo-01 ~]# vi $JBOSS_HOME/server/default/deploy/oracle-ds.xml 
        
   ...        
   
   <datasources>
     <local-tx-datasource>
       <jndi-name>DefaultDS</jndi-name>
       <connection-url>jdbc:oracle:thin:@localhost:1521:NEODOO</connection-url>
       <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
       <user-name>neodoo</user-name>
      <password>neodoo</password>
   
  ....

[edit] Configuring JBossMQ Persistence

First, verify to stop JBoss AS.

   [root@neodoo-01 ~]# $JBOSS_HOME/bin/shutdown.sh -S 

Delete defautl JBossMQ persistence with Hypersonic.

   [root@neodoo-01 ~]# rm deploy/jms/hsqldb-jdbc2-service.xml .

Copy template file for Oracle JBossMQ Persistence and configure it.

   [root@neodoo-01 ~]# cp /opt/java/jboss/docs/examples/jms/oracle-jdbc2-service.xml /opt/java/jboss/server/default/deploy/jms/

   [root@neodoo-01 ~]# vi /opt/java/jboss/server/default/deploy/jms/oracle-jdbc2-service.xml
   
   
   </mbean>
   
   
     <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
            name="jboss.mq:service=PersistenceManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
       <attribute name="SqlProperties">
         BLOB_TYPE=BINARYSTREAM_BLOB
         INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
         INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
         SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
         SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
         SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
         SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
         MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
         UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
         UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
         UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
         DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES MESS WHERE TXOP=:1 AND EXISTS (SELECT TXID FROM JMS_TRANSACTIONS TX WHERE TX.TXID = MESS.TXID)
         DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
         DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
         DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
         DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
         CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
            DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
            MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
         CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
         CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
         CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
         CREATE_TABLES_ON_STARTUP = TRUE
       </attribute>
     </mbean>
   

You can see that there is a list of SQL sentences used to manage JMS messages. By default, JBoss AS create the tables in database when it starts but you can create them directly executing create sentences in Oracle.

Now, you can start JBoss AS and all JMS messages will go to Oracle Database before being consumed.

   [root@neodoo-01 ~]# $JBOSS_HOME/bin/run.sh 

--Fsolans 22:19, 6 October 2009 (UTC)