JDBC Store Swiftlet

The JDBC Store Swiftlet is a replacement for the standard Store Swiftlet and utilizes a database management system to store persistent messages, durable subscribers, and 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 supports 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 the BLOB column.

XALOG

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

DURABLES

The table DURABLES is used to store durable subscriptions.

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 an operation is performed, a connection is checked out of the pool and checked in after the operation has been finished or an error has occurred. In the latter case, the connection is marked as invalid and removed from 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 from the pool. The same will be done with connections that 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 at each checkout. 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 (maybe) 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 special handling to store Blob values whose 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 iscom.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 is 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 specified which ensures that files may not grow too 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.

Element "jdbc", Parent Element: "swiftlet"

JDBC Settings.

Definition

Values

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

JDBC Connection Pool.

Definition

Values

Element "statements", Parent Element: "swiftlet"

SQL Statements.

Definition

Values

Element "swap", Parent Element: "swiftlet"

Swap Settings.

Definition

Values