RelationalDbHazelcastStore Extension (Cluster Storage)

Introduction

The RelationalDbHazelcastStore Extension provides one of the possible options for Cluster Storage, in this case allowing using a Relational Database to store Platform data. In particular, this Extension provides support for the following Relational Database Management Systems:

  • MySQL
  • DB2
  • Oracle
  • MS-SQL
  • Postgres

Database Sizing

Before starting with the steps necessary to setup the database let's do some consideration about the database size. FNZ Studio uses the database in a key-value store fashion. Keys are Strings and values are BLOBs. The BLOBs either contain textual data (XML or JSON) or binary data. Using a simple formula we can have an approximation of the size required:

Copy 
size = (a + b + c) x encoding

where:
size: Size of the Cluster Storage
a: Size of Process data (Process Instances and Value Stores) in XML
b: Size of Business Objects in XML
c: Size of all Cluster Files
encoding: The BLOB encoding overhead of the database (e.g. HEX-encoding)

Database Setup

If the relational database is your preferred choice as Cluster Storage, the first step to execute is set up the database. The steps are the same, but they differ based on the relational DB implementation.

MySQL Setup

  1. Perform a standard installation.

  2. Create a database (e.g. appway).

  3. Execute the DDL statements in this file ddlstatementsmysql.sql for the MySQL dialect to create tables and indexes.

    Before executing this last step, check the following section. If you are installing or upgrading to MySQL 8.0.2 or higher, you need to consider some additional information.

Installing or Upgrading to MySQL 8.0.2 or higher

The RelationalDbHazelcastStore extension 15.2.4 (and higher) provides the com.nm.cluster.relationaldb.tablename.prefix configuration property, which allows adding a prefix to table names. As MySQL 8.0.2 uses groups as a reserved word, and groups is used in table names by the RelationalDbHazelcastStore extension, this new configuration property is necessary to make the RelationalDbHazelcastStore extension compatible with MySQL 8.0.2 (and higher).

Two basic scenarios are possible at this point:

  1. You want to upgrade an existing Platform installation to use MySQL 8.0.2 as a cluster storage
  2. You install the Platform from scratch using MySQL 8.0.2 or higher as a cluster storage

Upgrading an existing Platform installation to use MySQL 8.0.2 or higher In this case a Solution is using a MySQL database as a cluster storage, and the customer wants to upgrade MySQL to version 8.0.2 or higher. Since 'groups' is a reserved word, tables named 'groups' can no longer be created. The following steps are necessary for the upgrade:

  1. The DB administrator needs to choose a prefix to add to the existing tables and rename all the table in the Platform database. We recommend the three-character prefix aw_.
  2. The Platform administrator has to make sure the RelationalDbHazelcastStore extension is version 15.2.4 or higher. Then, in the extension configuration, he/she needs to assign the same prefix chosen by the DB Admin to the configuration property com.nm.cluster.relationaldb.table.prefix.

Installing the Platform from scratch using MySQL 8.0.2 or higher In this case a customer wants to set up a new Platform installation using a MySQL 8.0.2 or higher as a cluster storage. The following steps are necessary for the installation:

  1. The Platform administrator has to make sure the RelationalDbHazelcastStore extension is version 15.2.4 or higher. Then, in the extension configuration, he/she needs to assign a prefix to the configuration property com.nm.cluster.relationaldb.table.prefix, which will be used to create all the table names. We recommend the three-character prefix aw_.
  2. At the first Platform startup, all the necessary tables are created automatically using the chosen prefix. However, if the customer prefers to create the tables manually, the DB administrator needs to prepend the chosen prefix to the table names in the file ddlstatementsmysql.sql. For instance, if the prefix is aw_, groups tables will need to be renamed aw_groups.

DB2 Setup

  1. Perform a standard installation.
  2. Create a database (e.g. appway).
    • Use a page size of 32 Kilobytes
    • Create three table spaces: tspc_data, tspc_index, tspc_long.
  3. Execute the DDL statements in this file ddlstatementsdb2.sql for the DB2 dialect to create tables and indexes.

Oracle Setup

  1. Perform a standard installation.
  2. Create a database (e.g. appway).
  3. Execute the DDL statements in this file ddlstatementsoracle.sql for the Oracle dialect to create tables and indexes.

MS SQL Setup

  1. Perform a standard installation.
  2. Create a database (e.g. appway).
  3. Execute the DDL statements in this file ddlstatementsmssqlserver.sql) for the MS SQL dialect to create tables and indexes.

PostgreSQL Setup

  1. Perform a standard installation.
  2. Create a database (e.g. appway).
  3. Execute the DDL statements in this file ddlstatementspostgres.sql for the PostgreSQL dialect to create tables and indexes.

Extension Configuration

The extension has some general configuration properties, while others are specific to JDBC or JNDI connections.

General Configuration Properties

com.nm.cluster.relationaldb.store.maps:
A comma-separated list of persistent maps that use the relational database as storage. If blank, all maps use the relational database. Default: blank

com.nm.cluster.relationaldb.store.generic:
If true, the Relational DB Hazelcast Store extension is used as the generic store for persistent maps. Default: true

com.nm.cluster.relationaldb.dialect:
Defines the database dialect. Possible values are Db2, MsSQLServer, MySQL, Oracle, or Postgres. If the property is blank, the Platform tries to infer the correct dialect from other configuration properties such as com.nm.cluster.relationaldb.connection.url or com.nm.cluster.relationaldb.jndi.path. If the dialect cannot be inferred, startup is aborted. Default: blank

com.nm.cluster.relationaldb.schema:
Defines the schema to use. If blank, no or the default schema is used. Default: blank

com.nm.cluster.relationaldb.batching.enabled:
Defines if batching in INSERT, UPDATE and DELETE queries is used or not. Default: true

If you are using an MS SQL Server database, make sure this property is set to `false` to avoid deadlocks.

JDBC Configuration Properties

com.nm.cluster.relationaldb.driver.name:
The database-vendor-specific, fully qualified class name of the JDBC driver for the respective database. Drivers are usually installed in an application-server-specific folder which is part of the classpath. Default: com.mysql.jdbc.Driver

com.nm.cluster.relationaldb.state.script:
This script is executed to validate the connection to the database. Default: select 1

com.nm.cluster.relationaldb.connection.url:
A driver-specific JDBC connection URL. Default: jdbc:mysql://localhost/appway

com.nm.cluster.relationaldb.username:
The user name of the user that is connecting to the database. Default: appway

com.nm.cluster.relationaldb.password:
The password of the user connecting to the database. Default: 123

com.nm.cluster.relationaldb.jdbc.connectionpool.maxActive:
Defines the maximum number of active connections in the pool. When the pool is exhausted, retrieving a connection fails and throws an exception. A retry mechanism tries to establish the connection again. Default: 40

com.nm.cluster.relationaldb.jdbc.connectionpool.maxIdle:
The maximum number of idle connections. If this number is reached, the extra connections in idle state are closed. Default: 40

com.nm.cluster.relationaldb.jdbc.connectionpool.maxWait:
The maximum time (in milliseconds) to wait for retrieving a new connection when the pool is exhausted. Default: 1000

com.nm.cluster.relationaldb.jdbc.connectionpool.timeBetweenEvictionRuns:
Sets the time (in milliseconds) to sleep between runs of the idle object evictor thread. This thread cleans idle connections that are eligible for eviction. Default: 30000

com.nm.cluster.relationaldb.jdbc.connectionpool.minEvictableIdleTime:
Sets the minimum amount of time (in milliseconds) an object may sit idle in the pool before it is eligible for eviction by the idle object evictor. Default: 600000

com.nm.cluster.relationaldb.warn.threshold.read.query.ms:
Sets the read query time threshold (in milliseconds) after which a warning is logged. Default: 20000

com.nm.cluster.relationaldb.warn.threshold.write.query.ms:
Sets the write query time threshold (in milliseconds) after which a warning is logged. Default: 40000

com.nm.cluster.relationaldb.warn.threshold.delete.query.ms:
Sets the delete query time threshold (in milliseconds) after which a warning is logged. Default: 20000

Tables can be created automatically using a special admin user with privileges to create tables. If the properties are empty (default), the standard JDBC properties are used:

com.nm.cluster.relationaldb.admin.connection.url
com.nm.cluster.relationaldb.admin.username
com.nm.cluster.relationaldb.admin.password

JNDI Configuration Properties

com.nm.cluster.relationaldb.jndi.enabled:
Set to true to enable JNDI. If false, JDBC is enabled. Default: false

com.nm.cluster.relationaldb.jndi.path:
The JNDI path as defined in the application server's directory service. For example: java:/com/end/jdbc/oracleDB Default: blank

All additional properties starting with java. or javax. are used as JNDI environment properties. For example: java.naming.factory.iniital

Tables can be created automatically using a special admin user with privileges to create tables. If the properties are empty (default), the standard JNDI properties are used:

com.nm.cluster.relationaldb.admin.jndi.path Default: blank

All additional properties starting with com.nm.cluster.relationaldb.admin.jndi. are used as JNDI environment properties for the admin connection.

Extension Configuration Examples

In this section, some examples of the configurations explained in the previous sections are illustrated. The first examples are based on JDBC and the DBMS chosen, while the last one is based on JNDI.

MySQL

Copy 
com.nm.cluster.relationaldb.driver.name    = com.mysql.jdbc.Driver
com.nm.cluster.relationaldb.state.script   = select 1
com.nm.cluster.relationaldb.connection.url = jdbc:mysql://localhost/appway
com.nm.cluster.relationaldb.username       = appway
com.nm.cluster.relationaldb.password       = 123

DB2

Copy 
com.nm.cluster.relationaldb.driver.name    = com.ibm.db2.jcc.DB2Driver
com.nm.cluster.relationaldb.state.script   = select 1 FROM sysibm.sysdummy1
com.nm.cluster.relationaldb.connection.url = jdbc:db2://10.199.20.60:50000/APPWAY
com.nm.cluster.relationaldb.username       = db2inst1
com.nm.cluster.relationaldb.password       = db2inst1

Oracle

Copy 
com.nm.cluster.relationaldb.driver.name    = oracle.jdbc.OracleDriver
com.nm.cluster.relationaldb.state.script   = select 1 from dual
com.nm.cluster.relationaldb.connection.url = jdbc:oracle:thin:@localhost:1521:appway
com.nm.cluster.relationaldb.username       = C##APPWAY
com.nm.cluster.relationaldb.password       = test

MS SQL

Copy 
com.nm.cluster.relationaldb.driver.name    = com.microsoft.sqlserver.jdbc.SQLServerDriver
com.nm.cluster.relationaldb.state.script   = select 1
com.nm.cluster.relationaldb.connection.url = jdbc:sqlserver://10.0.20.5:1433;databaseName=smith
com.nm.cluster.relationaldb.username       = smith
com.nm.cluster.relationaldb.password       = 12345
com.nm.cluster.relationaldb.batching.enabled = false

PostgreSQL

Copy 
com.nm.cluster.relationaldb.driver.name    = org.postgresql.Driver
com.nm.cluster.relationaldb.state.script   = select 1
com.nm.cluster.relationaldb.connection.url = jdbc:postgresql://localhost:5432/appway
com.nm.cluster.relationaldb.username       = postgres
com.nm.cluster.relationaldb.password       = 123

JNDI

Copy 
com.nm.cluster.relationaldb.jndi.enabled   = true
com.nm.cluster.relationaldb.jndi.path      = resources/dbConnectionFactory