JBossMQ JDBC2 Persistence Configuration

This service persists messages to the database and also implements the CacheStore

The configuration can be found in deploy{-hasingleton}/jms/hsqldb-jdbc2-service.xml

It is recommended you change to a real database for production.

Alternates for other databases can be found in docs/examples/jms.

You also need to deploy the relevent datasource for your chosen database.

Default Configuration

  <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=OBJECT_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) TXID FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)
      DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID 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 WHERE TXOP=? AND JMS_MESSAGES.TXID IN (SELECT TXID FROM JMS_TRANSACTIONS)
      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 CACHED TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
         DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
         MESSAGEBLOB OBJECT, 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 CACHED TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
      CREATE_TABLES_ON_STARTUP = TRUE
    </attribute>
    <!-- Uncomment to override the transaction timeout for recovery per queue/subscription, in seconds -->
    <!--attribute name="RecoveryTimeout">0</attribute-->
    <!-- The number of blobs to load at once during message recovery -->
    <attribute name="RecoverMessagesChunk">0</attribute>
  </mbean>

Note: for 3.2.x, the depends should be jboss.jca:service=LocalTxCM,name=DefaultDS or jboss.jca:service=XATxCM,name=DefaultDS depending whether the datasource is local-tx-datasource or xa-datasource

Configurable Attributes

• ConnectionManager

  - the object name of the ConnectionManager controlling the DataSource

• ConnectionRetryAttempts

  - the number of times to retry to get a database connection at startup with a wait of 1.5 seconds in between - this is hack to workaround a problem with hsqldb not being immediately available

• SQLProperties

  - the sql statements used to implement the service

• RecoveryTimeout

  - the override transaction timeout during queue/subscription recovery - default is 0 (zero) or no override (from 4.0.3 & 3.2.8)

• RecoveryRetries

  - the number of times to retry the initial transaction resolution - default 0 (from 4.0.3 & 3.2.8)

• RecoverMessagesChunk

  - the number of "blobs" to load at once (from 4.0.4 & 3.2.8 - see (1))

• StatementRetries

  - the number of times to retry message removal on an sql failure with a small delay (upto .5 seconds) in between -default 5. Works around MySQL bug, and possibly Oracle RAC bug but certainly many extraenous table locking in MS SQL Server? (from 4.0.5)

(1) Currently there are two options

• RecoverMessagesChunk

  =0 - which does the default processing of loading message id and blobs together and letting the result set handle the "paging"

• RecoverMessagesChunk

  =1 - which just loads the keys then loads each blob individually, i.e. n+1 style loading - this is a workaround for at least MySQL which does not do "paging" of result sets without doing some very vendor specific processing

SQL Properties

*NOTE: The default schemas created by this configuration are NOT optimized.

You will want to create indexes and other configuration inside the database which may require you to create the tables by hand*

• CREATE_TABLES_ON_STARTUP - whether to create tables during the boot process if they don't exist

• BLOB_TYPE - which sql statement to use

BLOB Types

This controls which SQL statement to use, this must be matched with a suitable definition of MESSAGEBLOB in CREATE_MESSAGE_TABLE

• OBJECT_BLOB - Statement.setObject()

• BYTES_BLOB - Statement.setBytes()

• BINARYSTREAM_BLOB - Statement.setBinaryStream()

JMS_MESSAGES

Stores messages

• MESSAGEID - the jboss internal message id

• DESTINATION - the jboss internal destination name (can include Topic subscription name)

• TXID - any transaction id associated with this message

• TXOP - the operation associated with message

• MESSAGEBLOB - the message

Operations include

• A - Message added

• D - Message deleted

• T - Message added using the CacheStore interface

JMS_TRANSACTIONS

Stores outstanding transactions

• TXID - the transaction id