System Databases

The Axon.ivy Engine system database is used by the server 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 Axon.ivy Engine Config makes you that. In the second case, good defaults 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
11
# MySql as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  Driver: com.mysql.jdbc.Driver
  # 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.

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
11
# MariaDb as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  Driver: org.mariadb.jdbc.Driver
  # 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.

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
8
# Postgres as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  Driver: org.postgresql.Driver
  # 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
# Microsoft SQL Server as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
  # Format: jdbc:sqlserver://<host>[:<port>];DatabaseName=<database name>
  Url: jdbc:sqlserver://localhost:1433;DatabaseName=AxonIvySystemDatabase
  UserName: root
  Password: 1234
  DriverProperties:
    # Instance name of the MSSQL Server
    instanceName: SqlServer

If database will be created by the Axon.ivy Engine the collate is set to Latin1_General_CI_AI. If you want to connect to an existing instance of a MS SQL Server you have to configure an additional connection property that is called instance / instanceName containing the name of the corresponding database instance.

Oracle

1
2
3
4
5
6
7
# Oracle as System Database
# [engineDir]/configuration/ivy.yaml
SystemDb:
  Driver: oracle.jdbc.OracleDriver
  Url: jdbc:oracle:thin:@localhost:1521:serviceid
  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). Grant all necessary permissions to the user so that he can create and alter tables, indexes, sequences. Of course the user must be able to insert, update, delete and select data from the tables of the system database. This is optional you can use an already existing Oracle user or let the Axon.ivy Engine create one for you with the Axon.ivy Engine Configuration.

  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.