System Databases

The Axon.ivy Engine system database is used to store configuration, security, content and workflow information. See Configuration to find out how you can create and configure Axon.ivy Engine system databases. Axon.ivy Engine supports the following database systems to host the system database:

You have the choice to create the database without the tables on your own. Or let the Engine Cockpit make you that. In the second case, good default creation parameters will be applied.

Password Encryption

Passwords are stored encrypted in the system database using state of the art encryption algorithms. More information can be found in the chapter System Database Encryption.

Character set and collation

All characters in databases are encoded with a specific charset (e.g. utf8, latin1, cp1257). Lastly it defines which kind of characters can be stored at all.

The collation is a set of rules that defines how the database management system compares and orders the data (e.g. utf8_unicode_ci, latin2_general_ci). Common abbreviations in the name of the collations are the following:

  • ci = case insensitive

  • cs = case sensitive

  • ai = accent insensitive

  • as = accent sensitive

As well as the character set the collation can be defined mostly on several levels: server, database, table or column. Everything about this subject is very dependent on the actual database management system.

Support case insensitive searches

If a case insensitive search is required, it must be guaranteed that the affected column has a case insensitive collation.

  1. Check character set & collation of the column

  2. Change character set & collation if necessary

MySQL

 1# MySql as System Database
 2# [engineDir]/configuration/ivy.yaml
 3SystemDb:
 4  # Format: jdbc:mysql://<host>[:<port>]/<database name>
 5  Url: jdbc:mysql://localhost:3306/AxonIvySystemDatabase
 6  UserName: root
 7  Password: 1234
 8  DriverProperties:
 9    # Very likely to set if not ssl connection is used, to prevent warn logs
10    useSSL: false

We only support InnoDB as storage engine. If database will be created by the Axon.ivy Engine the charset is set to utf8 and collation is set to utf8_unicode_ci.

Warning

The newer charset utf8mb4 is not supported. The creation of the system database tables will fail if you use utf8mb4 charset.

Tip

If you experience bad query performance in Portal, then try to increase the setting innodb-buffer-pool-size in the my.cnf configuration file of the database.

MariaDB

 1# MariaDb as System Database
 2# [engineDir]/configuration/ivy.yaml
 3SystemDb:
 4  # Format: jdbc:mariadb://<host>[:<port>]/<database name>
 5  Url: jdbc:mariadb://localhost:3306/AxonIvySystemDatabase
 6  UserName: root
 7  Password: 1234
 8  DriverProperties:
 9    # Very likely to set if not ssl connection is used, to prevent warn logs
10    useSSL: false

We only support InnoDB as storage engine. If database will be created by the Axon.ivy Engine the charset is set to utf8 and collation is set to utf8_unicode_ci.

Warning

The newer charset utf8mb4 is not supported. The creation of the system database tables will fail if you use utf8mb4 charset.

Tip

If you experience bad query performance in Portal, then try to increase the setting innodb-buffer-pool-size in the my.cnf configuration file of the database.

PostgreSQL

1# Postgres as System Database
2# [engineDir]/configuration/ivy.yaml
3SystemDb:
4  # Format: jdbc:postgresql://<host>[:<port>]/<database system>
5  Url: jdbc:postgresql://localhost:5432/AxonIvySystemDatabase
6  UserName: root
7  Password: 1234

If database will be created by the Axon.ivy Engine the encoding is set to UTF8.

Tip

If you experience bad query performance in Portal, then try to increase the setting work_mem in the postgresql.conf configuration file of the database.

Microsoft SQL Server

There are two basic authentication schemes used to connect to a Microsoft SQL Server, SQL Server Authentication and Windows Authentication.

SQL Server Authentication

With SQL Server Authentication you are connecting to the database with a database user.

 1# Microsoft SQL Server as System Database
 2# [engineDir]/configuration/ivy.yaml
 3SystemDb:
 4  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
 5  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
 6  UserName: root
 7  Password: 1234
 8
 9  # Microsoft recommends not to use the instanceName and prefers specifying
10  # the port in the connection url. If you need to work with the instanceName
11  # you must remove the port in the connection url.
12  #DriverProperties:
13  #  instanceName: SqlServer

Note

On an SQL Server installation, only Windows Authentication is enabled by default. If you face problems connecting to the database, check if SQL Server Authentication is activated.

Windows Authentication

Microsoft SQL Server can be configured so it uses Windows credentials to log into the database.

There are two ways to do that, one where you configure a specific Windows user (and password), and one where the user credentials of the user running the Axon.ivy Engine are used.

Using specific Windows user:

 1# Microsoft SQL Server as System Database, specifying a Windows user to log
 2# into the database
 3# [engineDir]/configuration/ivy.yaml
 4SystemDb:
 5  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
 6  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
 7  UserName: myWindowsUser
 8  Password: myWindowsPassword
 9  DriverProperties:
10    integratedSecurity: true
11    authenticationScheme: NTLM
12    domain: myWindowsDomain

Using user credentials of the process owner:

 1# Microsoft SQL Server as System Database, using credentials of user running the
 2# Ivy Engine process
 3# [engineDir]/configuration/ivy.yaml
 4# NOTE: In order to use this feature, you need to copy the mssql-jdbc_auth DLL
 5#       file matching the JDBC driver version used by the Axon.ivy Engine into
 6#       the [engineDir]/jre/bin directory before starting the Axon.ivy Engine.
 7#       The easiest way to obtain this DLL is by downloading it from a Maven
 8#       repository, e.g.
 9#       https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver.
10SystemDb:
11  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
12  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
13  DriverProperties:
14    integratedSecurity: true

Instance Name

Microsoft recommends not to use the instanceName and prefers specifying the port in the connection url, as for each connection there is a round trip to determine the port. Therefore you will need to configure a static TCP port (e.g. 1433) in the SQL Server Configuration Manager.

If you still need to work with the instanceName you must define it as an additional connection property and remove the port in the connection url.

Tip

If you cannot connect to a Microsoft SQL Server, check first if the TCP/IP protocol for SQL Server is activated (by default it’s deactivated). If you still face problems then you may check if SQL Server Authentication is activated. By default only Windows Authentication is enabled.

If the database is created by the Axon.ivy Engine the collate is set to Latin1_General_CI_AI.

Tip

If you want to join tables of the system database with tables of another business database, then ensure that all involved databases use the same collate. Otherwise, you will get bad performance when comparing character columns from different databases because the data must be converted from one collate to another.

Oracle

 1# Oracle as System Database
 2# [engineDir]/configuration/ivy.yaml
 3SystemDb:
 4
 5  # using oracle service name
 6  Url: jdbc:oracle:thin:@//localhost:1521/ServiceName
 7
 8  # using oracle service id
 9  Url: jdbc:oracle:thin:@localhost:1521:ServiceId
10
11  # using oracle tns
12  Url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ServiceName)(SERVER=DEDICATED)))
13
14  UserName: root
15  Password: 1234

If database will be created by the Axon.ivy Engine the collate is set to Latin1_General_CI_AI.

Tip

On all (reused) oracle database connections the maximum number of open cursors is set to 1000, independently from the default setting that may be set on the database itself. Those cursors are needed to cache all prepared statements and also for PL/SQL blocks.

It may turn out that the number of open cursors is exceeded, which is indicated by an error message similar to the following:

ch.ivyteam.ivy.persistence.PersistencyException: java.sql.SQLException:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

If this should happen, then you may customize (and increase) the number of open cursors per connection with the Java system property ch.ivyteam.ivy.persistence.db.oracle.MaxOpenCursors. It can be set in the jvm.options.

Note

Schema Creation

Before you can create the system database tables on a Oracle Database you have to do the following steps:

  1. You may want to create a new Oracle database where the Axon.ivy Engine System Database is located. This is optional you can use an already existing Oracle database.

  2. Create a new user (e.g. AxonIvy) or let the Axon.ivy Engine create one for you with the Setup Wizard. You can use the following script to create a new user with sufficient permissions:

    CREATE USER <username> IDENTIFIED BY <password>;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
      CREATE TRIGGER, CREATE PROCEDURE, CREATE SEQUENCE,
      UNLIMITED TABLESPACE TO <username>;

  3. You may want to create a new tablespace (e.g. AxonIvy) where the Axon.ivy Engine System Database can store the table data. This is optional you can use an already existing tablespace.

Warning

Be sure that the configuration of the database connection uses the new database and the Oracle Service ID reflecting the database you want to create the system database tables in.