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 has been tested with Oracle9i, DB2 Version 7 and TimesTen 4.3 (a very fast in-memory database), however, it can be configured to use any JDBC 2.0 compliant database that support the BLOB data type.
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.
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. |
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. |
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. |
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>
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 from 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>
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>
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.