JDBC Store Swiftlet

The JDBC Store Swiftlet is a replacement of the standard Store Swiftlet and utilizes a database management system to store persistent messages, durable subscribers, and to log XA transactions. Only temporary swap files for non-persistent messages are used on the local file system. The JDBC Store Swiftlet can be configured to use any JDBC 2.0 compliant database that support the BLOB data type.

JDBC Setup

SwiftMQ Tables

The distribution contains scripts to create and drop SwiftMQ tables for Oracle, DB2, and TimesTen under the sql directory. SwiftMQ uses 3 tables, one for persistent messages, one to log prepared XA transactions, and one to store durable subscriptions.

MESSAGES

The table MESSAGES contains persistent messages. The actual message is stored in BLOB column.

Column Name	Java Type	Content
QUEUENAME	String	The queue name (primary key).
ID	long	Unique message id (primary key). The maximum value is determined on startup and wrapped to 0 by reaching Long.MAX_VALUE.
PRIORITY	int	The message priority.
DELIVERYCOUNT	int	The message delivery count.
EXPIRATIONTIME	long	The message expiration time.
CONTENT	Blob	The actual JMS message.

XALOG

The table XALOG is used to store prepare log records during XA transactions. The prepare log record is stored in a BLOB column.

Column Name	Java Type	Content
ID	long	Unique id (primary key). The maximum value is determined on startup and wrapped to 0 by reaching Long.MAX_VALUE.
CONTENT	Blob	The actual prepare log record.

DURABLES

The table DURABLES is used to store durable subscriptions.

Column Name	Java Type	Content
CLIENTID	String	The client id (primary key).
DURABLENAME	String	The durable subscriber name (primary key).
TOPICNAME	String	The topic name.
SELECTOR	String	The message selector (may be null).
NOLOCAL	String	The "nolocal" flag.

JDBC Driver

The JDBC driver's class name and connection URL is specified within the jdbc element.

Example:

  <swiftlet name="sys$store">
    <jdbc driver-classname="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@localhost:1521:ORCL"
          username="system"
          password="blabla"/>
    ...
  </swiftlet>

JDBC Connection Pool

The JDBC Store Swiftlet uses a JDBC connection pool internally. Before a operation is performed, a connection is checked out of the pool and checked in after the operation has been finished or an error has occured. In the latter case the connection is marked as invalid and removed out of the pool. If no connection is available to check out, the specific thread will wait until one is available again.

The size of the connection pool is determined by the 2 attributes min-connections and max-connections. The default for min-connections is 5, for max-connections -1 (unlimited). Further attributes are idle-timeout and keepalive-interval. The idle-timeout is to remove valid but old connections out of the pool. After a connection is checked into the pool, it is marked as idling. During the keepalive-interval a keepalive SQL statement (a simple select) is executed on each idling connection. If it fails, the connection is marked as invalid and removed out of the pool. The same will be done with connections which are valid but have reached the idle-timeout. At the end of the keepalive processing, the pool will be filled up with new connections up to the size specified in min-connections.

If a database server goes down, the connections used from SwiftMQ will throw exceptions which will lead to an abort of current SwiftMQ transactions. The connection pool tries to establish a new connection on each check out. So once the database server is coming up again, valid connections are provided by the connection pool and operation continues.

The JDBC connection pool uses a connection factory to create JDBC connections. The default connection factory can be used for DB2 and (may be) other JDBC 2.0 compliant databases. When using Oracle, the connection factory com.swiftmq.impl.store.jdbc.pool.PooledOracleConnectionFactory needs to be specified in the connection-factory attribute. The reason is that Oracle requires a special handling to store Blob values which size is greater than 4 KB. The connection created from the PooledOracleConnectionFactory takes care of this and performs this special handling if the message size exceeds 4 KB.

If the TimesTen database is used, the connection factory is com.swiftmq.impl.store.jdbc.pool.PooledTimesTenConnectionFactory.

Example:

  <swiftlet name="sys$store">
    <jdbc driver-classname="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@localhost:1521:ORCL"
          username="system"
          password="blabla"/>
    <connection-pool connection-factory="com.swiftmq.impl.store.jdbc.pool.PooledOracleConnectionFactory"
                     min-connections="3"
                     max-connections="30"
                     idle-timeout="300000"
                     keepalive-interval="120000"/>
    ...
  </swiftlet>

SQL Statements

Every SQL statement used from the JDBC Store Swiftlet is configurable. In most cases it is not necessary to change the statements, however, it is possible. SQL statements are stored in the statements element. For the default statements have a look at the Configuration section below. All statements used from the JDBC Store Swiftlet are performed as PreparedStatement and re-used out of a PreparedStatement cache which is bound to a connection.

Each default statement contains a ${schema-prefix} in front of the table name. This variable will be substituted with the value of the schema-prefix attribute. For example, in case the SwiftMQ tables are stored under a schema swiftmq in the database, the table name needs to be prefixed sometimes with the schema name. In this case, the attribute schema-prefix must contain the name of the schema with a trailing dot ., e.g. swiftmq.. This results in swiftmq.messages for the messages table name in the SQL statements.

The following example uses a schema swiftmq and overwrites the keepalive statement.

Example:

  <swiftlet name="sys$store">
    <jdbc driver-classname="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@localhost:1521:ORCL"
          username="system"
          password="blabla"/>
    <connection-pool connection-factory="com.swiftmq.impl.store.jdbc.pool.PooledOracleConnectionFactory"
                     min-connections="3"
                     max-connections="30"
                     idle-timeout="300000"
                     keepalive-interval="120000"/>
    <statements schema-prefix="swiftmq."
                keepalive="select 1 from ${schema-prefix}durables"/>
    ...
  </swiftlet>

Swap Store

If a queue cache if full, the swap store serves as a swapping-out of non-persistent messages. In this case, a RandomAccessFile is created per queue in which messages are swapped-out. The file position, stored within the queue message index, is passed back for any stored message. Thus, the access performance is maximum. Swap files are deleted automatically if all entries are marked as deleted as well as during a router shutdown. Concerning swap files, a roll-over size may be spezified which ensures that files may not grow to large. When reaching this size, a new swap file is created and the previous one persists until it is empty.

Swap files have only a temporary meaning and thus are not stored in the database.

Installation

You must stop the router before you install this Kernel Swiftlet. Then copy the content of the optional-swiftlets/kernel/sys_store_jdbc/deploy subdirectory of this Kernel Swiftlet into the directory kernel/sys$store.

Copy the JDBC driver jar files into kernel/sys$store. Note that you cannot use zip files. If your JDBC driver is provided as a zip file, then you have to convert it to a jar file.

Configuration

The configuration of the JDBC Store Swiftlet is defined within the element

      <swiftlet name="sys$store" .../>

of the router's configuration file. One can use the SwiftMQ Exlorer or CLI for configuration as well. They both save into that file.

Element "jdbc", Parent Element: "swiftlet"

JDBC Settings.

Definition

Attribute	Type	Mandatory	Description
driver-classname	java.lang.String	Yes	Name of the JDBC Drive Class
url	java.lang.String	Yes	JDBC URL
username	java.lang.String	No	JDBC Username
password	java.lang.String	No	JDBC Password

Values

Attribute	Values
driver-classname
url
username
password

Element "connection-pool", Parent Element: "swiftlet"

JDBC Connection Pool.

Definition

Attribute	Type	Mandatory	Description
connection-factory	java.lang.String	No	Pooled Connection Factory
min-connections	java.lang.Integer	No	Minimum Connections
max-connections	java.lang.Integer	No	Maximum Connections
idle-timeout	java.lang.Long	No	Idle Timeout
keepalive-interval	java.lang.Long	No	Keep Alive Interval

Values

Attribute	Values
connection-factory	Default: com.swiftmq.impl.store.jdbc.pool.PooledConnectionFactory
min-connections	Min: 0 Default: 5
max-connections	Default: -1
idle-timeout	Default: 6000000
keepalive-interval	Default: 600000

Element "statements", Parent Element: "swiftlet"

SQL Statements.

Definition

Attribute	Type	Mandatory	Description
schema-prefix	java.lang.String	No	Schema Prefix for Tables
keepalive	java.lang.String	No	Keep Alive Statement
messages-select-all	java.lang.String	No	Messages/Select All
messages-select-single	java.lang.String	No	Messages/Select Single
messages-select-max-id	java.lang.String	No	Messages/Select max(id)
messages-delete-all	java.lang.String	No	Messages/Delete All
messages-delete-single	java.lang.String	No	Messages/Delete Single
messages-delete-sequence	java.lang.String	No	Messages/Delete Sequence
messages-update-single	java.lang.String	No	Messages/Update Single
messages-update-sequence	java.lang.String	No	Messages/Update Sequence
messages-insert	java.lang.String	No	Messages/Insert
messages-insert-oracle-1	java.lang.String	No	Messages/Insert/Oracle/1
messages-insert-oracle-2	java.lang.String	No	Messages/Insert/Oracle/2
messages-insert-oracle-3	java.lang.String	No	Messages/Insert/Oracle/3
durables-select-all	java.lang.String	No	Durables/Select All
durables-select-single	java.lang.String	No	Durables/Select Single
durables-insert	java.lang.String	No	Durables/Insert
durables-delete	java.lang.String	No	Durables/Delete
xalog-select-all	java.lang.String	No	XA Log/Select All
xalog-select-single	java.lang.String	No	XA Log/Select Single
xalog-select-max-id	java.lang.String	No	XA Log/Select max(id)
xalog-insert	java.lang.String	No	XA Log/Insert
xalog-insert-oracle-1	java.lang.String	No	XA Log/Insert/Oracle/1
xalog-insert-oracle-2	java.lang.String	No	XA Log/Insert/Oracle/2
xalog-insert-oracle-3	java.lang.String	No	XA Log/Insert/Oracle/3
xalog-delete	java.lang.String	No	XA Log/Delete

Values

Attribute	Values
schema-prefix
keepalive	Default: select 1 from ${schema-prefix}xalog
messages-select-all	Default: select id,priority,deliverycount,expirationtime from ${schema-prefix}messages where queuename = ? order by id
messages-select-single	Default: select priority,deliverycount,expirationtime,content from ${schema-prefix}messages where queuename = ? and id = ?
messages-select-max-id	Default: select max(id) from ${schema-prefix}messages where queuename = ?
messages-delete-all	Default: delete from ${schema-prefix}messages where queuename = ?
messages-delete-single	Default: delete from ${schema-prefix}messages where queuename = ? and id = ?
messages-delete-sequence	Default: delete from ${schema-prefix}messages where queuename = ? and id between ? and ?
messages-update-single	Default: update ${schema-prefix}messages set deliverycount = deliverycount + 1 where queuename = ? and id = ?
messages-update-sequence	Default: update ${schema-prefix}messages set deliverycount = deliverycount + 1 where queuename = ? and id between ? and ?
messages-insert	Default: insert into ${schema-prefix}messages (queuename,id,priority,deliverycount,expirationtime,content) values (?,?,?,?,?,?)
messages-insert-oracle-1	Default: insert into ${schema-prefix}messages (queuename,id,priority,deliverycount,expirationtime,content) values (?,?,?,?,?,empty_blob())
messages-insert-oracle-2	Default: select content from ${schema-prefix}messages where queuename = ? and id = ?
messages-insert-oracle-3	Default: update ${schema-prefix}messages set content = ? where queuename = ? and id = ?
durables-select-all	Default: select clientid,durablename,topicname,selector,nolocal from ${schema-prefix}durables
durables-select-single	Default: select topicname,selector,nolocal from ${schema-prefix}durables where clientid = ? and durablename = ?
durables-insert	Default: insert into ${schema-prefix}durables (clientid,durablename,topicname,selector,nolocal) values (?,?,?,?,?)
durables-delete	Default: delete from ${schema-prefix}durables where clientid = ? and durablename = ?
xalog-select-all	Default: select id,content from ${schema-prefix}xalog
xalog-select-single	Default: select content from ${schema-prefix}xalog where id = ?
xalog-select-max-id	Default: select max(id) from ${schema-prefix}xalog
xalog-insert	Default: insert into ${schema-prefix}xalog (id,content) values (?,?)
xalog-insert-oracle-1	Default: insert into ${schema-prefix}xalog (id,content) values (?,empty_blob())
xalog-insert-oracle-2	Default: select content from ${schema-prefix}xalog where id = ?
xalog-insert-oracle-3	Default: update ${schema-prefix}xalog set content = ? where id = ?
xalog-delete	Default: delete from ${schema-prefix}xalog where id = ?

Element "swap", Parent Element: "swiftlet"

Swap Settings.

Definition

Attribute	Type	Mandatory	Description
path	java.lang.String	No	Path of Swap Files
roll-over-size	java.lang.Long	No	Roll Over Size

Values

Attribute	Values
path	Default: ./
roll-over-size	Min: 1048576 Default: 10485760

SwiftMQ Documentation

Administration

Swiftlets

APIs

How To

In Depth

Administration

Swiftlets

JDBC Setup

SwiftMQ Tables

MESSAGES

XALOG

DURABLES

JDBC Driver

JDBC Connection Pool

SQL Statements

Swap Store

Installation

Configuration