Database support

The EbMS Adapter supports the following databases

• Database support
  • Database Scripts
  • Database Configuration
    • Common Properties
    • DB2
    • H2
    • HSQLDB
    • MariaDB
    • MS SQL Server
      • XA Driver
      • Quartz
    • MySQL
      • XA Driver
    • Oracle
      • XA Driver
    • PostgreSQL
      • XA Driver
  • Flyway

Database Scripts

The database master scripts can be found here
The database update scripts can be found here
ebms-core also supports automatic database migration through Flyway

Database Configuration

You can find the JDBC settings for the supported databases as well as links to the JDBC drivers below.

Common Properties

ebms.jdbc.username=<username>
ebms.jdbc.password=<password>

DB2

since v2.14.0

# JDBC driver
ebms.jdbc.driverClassName=com.ibm.db2.jcc.DB2Driver
# or XA driver
ebms.jdbc.driverClassName=com.ibm.db2.jcc.DB2XADataSource
ebms.jdbc.url=jdbc:db2://<host>:<port>/<dbname>

Tested with DB2 11.5.4.0

Download drivers here

H2

since v2.17.2

# JDBC and XA driver
ebms.jdbc.driverClassName=org.h2.Driver
# or XA driver
ebms.jdbc.driverClassName=org.h2.jdbcx.JdbcDataSource
# In memory
ebms.jdbc.url=jdbc:h2:mem:<dbname>
# or file
ebms.jdbc.url=jdbc:h2:<path>
# or server
ebms.jdbc.url=jdbc:h2:tcp://<host>:<port>/<path>

Tested with H2 1.4.200

HSQLDB

# JDBC driver
ebms.jdbc.driverClassName=org.hsqldb.jdbcDriver
# or XA driver
ebms.jdbc.driverClassName=org.hsqldb.jdbc.pool.JDBCXADataSource
# In memory
ebms.jdbc.url=jdbc:hsqldb:mem:<dbname>
# or file
ebms.jdbc.url=jdbc:hsqldb:file:<path>
# or server
ebms.jdbc.url=jdbc:hsqldb:hsql://<host>:<port>/<dbname>

Tested with HSQLDB 2.5.1

MariaDB

# JDBC driver
ebms.jdbc.driverClassName=org.mariadb.jdbc.Driver
# or XA driver
ebms.jdbc.driverClassName=org.mariadb.jdbc.MySQLDataSource
ebms.jdbc.url=jdbc:mariadb://<host>:<port>/<dbname>

Tested with MariaDB 10.3.22

Download drivers here

Download the right flyway-mysql driver here and add it to the classpath next to the database driver Check the pom.xml of ebms-admin for the right version of the flyway-mysql library

MS SQL Server

danger

We strongly advise to not use a MSSQL Database with the EbMS Adapter if you expect a moderate to high message load, because MSSQL cannot handle that because of Page Locking.

# JDBC driver
ebms.jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
# or XA driver
ebms.jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerXADataSource
ebms.jdbc.url=jdbc:sqlserver://<host>:<port>;[instanceName=<instanceName>;]databaseName=<dbname>;

Tested with MS SQL Server 2019

Download drivers here

Download the right flyway-sqlserver driver here and add it to the classpath next to the database driver Check the pom.xml of ebms-admin for the right version of the flyway-sqlserver library

XA Driver

When using the XA driver execute the following script

EXEC sp_sqljdbc_xa_install
EXEC sp_addrolemember [SqlJDBCXAUser], '<username>'

Quartz

When deliveryTaskHandler.type is set to QUARTZor QUARTZ_JMS then set

deliveryTaskHandler.quartz.jdbc.selectWithLockSQL=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

MySQL

danger

MySQL support is removed in version 2.19.0

# JDBC driver
ebms.jdbc.driverClassName=com.mysql.cj.jdbc.Driver
# or XA driver
ebms.jdbc.driverClassName=com.mysql.cj.jdbc.MysqlXADataSource
ebms.jdbc.url=jdbc:mysql://<host>:<port>/<dbname>

Tested with MySQL 8.0.21

Download drivers here

XA Driver

When using the XA driver add line the following line to my.ini or my.cnf

default-time-zone='+02:00'

Oracle

# JDBC driver
ebms.jdbc.driverClassName=oracle.jdbc.OracleDriver
# or XA driver
ebms.jdbc.driverClassName=oracle.jdbc.xa.client.OracleXADataSource
ebms.jdbc.url=jdbc:oracle:thin:@<host>:<port>:<dbname>

Tested with Oracle XE 18c

Download drivers here

XA Driver

When using the XA driver execute the following script

grant select on sys.dba_pending_transactions to <username>;
grant select on sys.pending_trans$ to <username>;
grant select on sys.dba_2pc_pending to <username>;

PostgreSQL

# JDBC driver
ebms.jdbc.driverClassName=org.postgresql.Driver
# or XA driver
ebms.jdbc.driverClassName=org.postgresql.xa.PGXADataSource
ebms.jdbc.url=jdbc:postgresql://<host>:<port>/<dbname>

Tested with PostgreSQL 12.4

Download drivers here

XA Driver

If you get the following error when using the XA driver

org.postgresql.util.PSQLException: ERROR: prepared transactions are disabled Hint: Set max_prepared_transactions to a nonzero value.

then enable the max_prepared_transactions attribute in postgresql.conf

Flyway

Database migration through Flyway is enabled through the following EbMS property (since v2.17.2)

ebms.jdbc.update=true

If you already have an existing database and want to use Flyway, then you first have to initialize Flyway. Otherwise you can just enable the property.