Database creation and configuration for Bonita engine and BDM

Connect to the database as the System Administrator.

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

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

select comp_name, version, status from dba_registry;

Add XA elements:

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

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

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;

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;

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

Copy the sqljdbc_xa.dll from %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_6.0\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\.

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.

Execute the query in the Query Editor.

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_.

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

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

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.

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

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

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

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

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

Click Apply, then click OK

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

Create the BONITA database: CREATE DATABASE BONITA GO.

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