Database configuration

This page describes the steps to create and customize the databases for Bonita Engine and Business Data Model during Runtime configuration.

Database usage

Bonita uses an RDBMS (Relational DataBase Management System) for the following purposes:

  • One database schema is required by Bonita Engine to store information about deployed process definitions, process configurations, history of process execution, users, as well as Bonita Platform configuration information.

  • We recommend that you configure a different database schema if your project uses business data.

Bonita supports PostgreSQL with all editions and MySQL, Microsoft SQL Server, and Oracle only with the Subscription edition. You can find the list of RDBMSs versions supported on the supported database list page.

Default H2 database

The Bonita Studio and the Tomcat bundle come by default with an embedded H2 RDBMS. The database is automatically created at first startup, and can be used for all purposes described earlier.

However, H2 is only suitable for testing purposes. When building a production environment, you must switch to another RDBMS.

Steps to switch to another RDBMS

Please note this procedure cannot be performed using the Bonita Studio. The Bonita Studio can run only on the H2 Database. To use Bonita on another RDBMS, please use a bundle or set up a standalone server.

Here are the steps to follow. They are the same for the Bonita Engine database and the business data database:

  1. Create the database

  2. Customize RDBMS to make it work with Bonita

  3. Add the JDBC driver to the bundle if the database is proprietary

  4. Configure the bundle with database information

Steps 1 and 2 are detailed further down this page, steps 3 and 4 are part of the configuration part of the Tomcat bundle page.

All RDBMSs require specific customization, which must be done before you complete your installation.
If you do not complete the specific configuration for your RDBMS, your installation may fail.

Jdbc drivers

Bonita provides out of the box the Jdbc drivers for H2, PostgreSQL, Microsoft SQL Server, Oracle and MySQL.

Oracle Database

Bonita 7.10.+ is compatible with Oracle 12.2.0.x and Oracle 19c (19.3.0.0).

Create the database(s)

The first step in configuring Bonita with your RDBMS is to create the new databases (i.e. schemas): one for the Bonita Engine data, and another one, optionally, if you use business data.

If you use different RDBMS users for the Bonita Engine database and for the business data database, then you must grant the privileges to both users.

To create each Bonita Engine and business data database, you need a RDBMS user account that has sufficient privileges (i.e. privileges to create each new schemas).

Next, the RDBMS user owner of each new schema (i.e. Bonita Engine data and business data) must own the following privileges:

  • CREATE TABLE

  • CREATE INDEX

  • SELECT, INSERT, UPDATE, DELETE on created TABLE

To create the database(s), we recommend that you refer to your RDBMS documentation:

Your database(s) must be configured to use the UTF-8 character set.
Bonita is not attached to a specific RDBMS user. Therefore, the RDBMS user that bonita uses to connect to the database can be changed after the platform creation, provided the new user has the required access rights. The same goes for the RDBMS user used to connect to the business data database, if it is different.

Customize RDBMS to make it work with Bonita

PostgreSQL

Configure the database to use UTF-8.

Edit postgresql.conf and set a non-zero value for max_prepared_transactions. The default value, 0, disables prepared transactions, which is not compatible with Bonita Engine.
The value should be at least as large as the value set for max_connections (default is typically 100).
See the PostgreSQL documentation for details.

See also how to finely tune performance for PostgreSQL.

Oracle Database (Subscription only)

Make sure your database is configured to use the AL32UTF8 character set. If your database already exists, see the Oracle documentation for details of how to migrate the character set.

Bonita Engine uses datasources that handle global transactions that span resources (XADataSource), so the Oracle user used by Bonita Engine, requires some specific privileges, and there are also specific settings for XA activation.

Oracle 19c

The procedure below is used to create the settings to enable Bonita Engine to connect to the Oracle database.

It is assumed in the procedure that:

  • Oracle product is already installed and running

  • An 'Oracle' OS user is already created

  • A database already exists

  • The environment is already set:

  ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
  ORACLE_SID=...

  1. Connect to the database as the System Administrator.

    oracle@ubuntu:~$ sqlplus sys@ORCLPDB1 as sysdba;

  2. Check that the following components exist and are valid:
    SQL query >

    select comp_name, version, status from dba_registry;
Comp_name Version Status

Oracle Database Catalog Views

19.0.0.0.0

VALID

Oracle Database Packages and Types

19.0.0.0.0

VALID

JServer JAVA Virtual Machine

19.0.0.0.0

VALID

Oracle XDK

19.0.0.0.0

VALID

Oracle Database Java Packages

19.0.0.0.0

VALID

  1. Add XA elements:

     sql
 @"$ORACLE_HOME/rdbms/admin/xaview.sql"

    This only needs to be done once, after the installation of Oracle.

  2. Create the database user to be used by the Bonita Engine and grant the required rights:

    The following queries must be done for each new RDBMS user: i.e. one user = one database schema.

    The queries must be done for both the Bonita Engine and the business data RDBMS users.
    For example, if the users for the Bonita Engine and business data databases are bonita and business_data respectively (the actual values correspond to the ones set in the properties db.user and the bdm.db.user defined in the <TOMCAT_HOME>/setup/database.properties), you need to run the queries for both users.

    For the Bonita Engine database RDBMS user (e.g. bonita):

    SQL query >

    CREATE USER bonita IDENTIFIED BY bonita;
GRANT connect, resource TO bonita;
GRANT select ON sys.dba_pending_transactions TO bonita;
GRANT select ON sys.pending_trans$ TO bonita;
GRANT select ON sys.dba_2pc_pending TO bonita;
GRANT execute ON sys.dbms_system TO bonita;
GRANT execute ON sys.dbms_xa TO bonita;
GRANT FORCE ANY TRANSACTION TO bonita;
ALTER USER bonita quota unlimited on users;

    For the business data database RDBMS user (e.g. business_data):

    Note that the business data database RDBMS user (the one defined in bdm.db.user) requires one additional privilege: CREATE SEQUENCE

    SQL query >

    CREATE USER business_data IDENTIFIED BY business_data;
GRANT connect, resource TO business_data;
GRANT create sequence TO business_data;
GRANT select ON sys.dba_pending_transactions TO business_data;
GRANT select ON sys.pending_trans$ TO business_data;
GRANT select ON sys.dba_2pc_pending TO business_data;
GRANT execute ON sys.dbms_system TO business_data;
GRANT execute ON sys.dbms_xa TO business_data;
GRANT FORCE ANY TRANSACTION TO business_data;
ALTER USER business_data quota unlimited on users;

Microsoft SQL Server (Subscription only)

Use the default collation determined by the host OS’s locale.

XA Transactions

To support XA transactions, SQL Server requires a specific configuration. You can refer to MSDN for more information. Here is the list of steps to perform (as an example, the database name BONITA is used):

  1. Download the zip package of Microsoft SQL Server JDBC Driver and unzip it.

  2. Copy the sqljdbc_xa.dll from %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc\enu\xa\x64\ (x64 for 64 bit version of Windows, x86 for 32 bit version of Windows) to %SQLSERVER_INSTALL_ROOT%\MSSQL13.<instance_name>\MSSQL\Binn\.

  3. Copy/paste the content of install.sql file (located in %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc\6.0\enu\xa) to SQL Server Management Studio’s Query Editor.

  4. Execute the query in the Query Editor.

  5. To confirm successful execution of the script, open the "Object Explorer" and go to: Master > Programmability > Extended Stored Procedures.
    You should have 12 new procedures, each with a name starting with dbo.xp.sqljdbc_xa_.

  6. Assign the new role 'SqlJDBCXAUser' to the user who owns the Bonita Engine database (bonitadev in our example). To do so, execute the following commands in SQL editor:

    USE master;
GO
CREATE LOGIN bonitadev WITH PASSWORD = 'secret_password';
GO
CREATE USER bonitadev FOR LOGIN bonitadev;
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'bonitadev';
GO

  7. In the Windows "Start" menu, select Administrative Tools-> Services.

  8. In the "Services" window, make sure that the Distributed Transaction Coordinator service is set to start automatically. If it’s not yet started, start it.

  9. Make sure that the other services it depends on, namely "Remote Procedure Call" and "Security Accounts Manager", are also set to start automatically.

  10. Run the dcomcnfg command, or go to the "Start" menu, then Administrative Tools > Component Services.

  11. In the left navigation pane, navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator.

  12. Select and right-click on Local DTC and then Properties.

  13. Click on Security tab. Ensure that the checkbox for Enable XA Transactions is checked.

  14. Click Apply, then click OK

  15. Then stop and restart SQLServer to ensure it syncs up with the MS DTC changes.

  16. Create the BONITA database: CREATE DATABASE BONITA GO.

  17. Set bonitadev as owner of BONITA database (use, for example, 'Microsoft SQL Management Studio')

Recommended configuration for lock management

Run the script below to avoid deadlocks:

ALTER DATABASE BONITA SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE BONITA SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE BONITA SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE BONITA SET MULTI_USER

See MSDN.

Recommended configuration for in-doubt xact resolution

Run the script below to avoid that the SQL Server changes the status of databases to SUSPECT during database server startup when in-doubt XA transactions are found.
The value 2 in the block below means presume abort.
To minimize the possibility of extended down time, an administrator might choose to configure this option to presume abort, as shown in the following example

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'in-doubt xact resolution', 2
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
Kerberos authentication

You can use Kerberos for database authentication. Edit the file setup/tomcat-templates/bonita.xml by adding the following attributes to Resources:

  authenticationScheme="JavaKerberos"
  integratedSecurity="true"

You must perform this for each Datasource.

See in-doubt xact resolution Server Configuration Option.

MySQL (Subscription only)

XA Transactions

To support XA transactions, starting from MySQL 8.0, special XA rights must be granted to the RDBMS user. For example, if the RDBMS users for the Bonita Engine and business data databases are bonita and business_data respectively, you should run the following command:

GRANT XA_RECOVER_ADMIN ON *.* to bonita, business_data;

FLUSH PRIVILEGES;

Please note that the above command, by specifying ., is granting privileges for all databases. You may want to limit them to the Bonita Engine and business data databases if there are additional databases in your MySQL server.

Refer to the Privileges section in the official MySQL documentation for further information.

Maximum packet size

MySQL defines a maximum packet size on the server side. The default value for this setting is appropriate for most standard use cases. However, you need to increase the packet size if you see the following error: Error: 1153 SQLSTATE: 08S01 (ER_NET_PACKET_TOO_LARGE) Message: Got a packet bigger than 'max_allowed_packet' bytes

You need to update the file my.ini (for Windows) or my.cnf (for Linux) to avoid the ER_NET_PACKET_TOO_LARGE problem. Look for max_allowed_packet setting and increase the value.

For more information, see the MySQL website.

Surrogate characters not supported

MySQL does not support surrogate characters. If you want to use surrogate characters in your processes, you need to use another type of database.

utf8mb4 charset

Bonita requires MySQL to use UTF-8 encoding. The official MySQL recommendation is to use 'utf8mb4', and it is also the recommended Bonita configuration.

Bundle database configuration

Now that your databases are created and customized to work with Bonita, you are ready to configure the Bonita bundle.