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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Microsoft SQL Server as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
  UserName: root
  Password: 1234

  # Microsoft recommends not to use the instanceName and prefers specifying
  # the port in the connection url. If you need to work with the instanceName
  # you must remove the port in the connection url.
  #DriverProperties:
  #  instanceName: SqlServer

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

Instance Name

Microsoft recommends not to use the instanceName and prefer specifying the port in the connection url because for each connection there is a round trip to determine the port. If you still need to work with the instanceName you must define it as additional connection property and you must remove the port in the connection url.

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.

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). SQL Server Authentication must be enabled too. By default only Windows Authentication is enabled which is not supported by JDBC drivers.

Oracle

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# Oracle as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:

  # using oracle service name
  Url: jdbc:oracle:thin:@//localhost:1521/ServiceName

  # using oracle service id
  Url: jdbc:oracle:thin:@localhost:1521:ServiceId

  # using oracle tns
  Url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ServiceName)(SERVER=DEDICATED)))

  UserName: root
  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.