Skip to main content
Skip table of contents

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.

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:

XML
  <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:

XML
  <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:

XML
  <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

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

of the router's configuration 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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.