Version: Release 22.2

Configure the SQL Repository

Digital.ai Release stores its data in a repository. By default, this repository is an embedded database stored in XL_RELEASE_SERVER_HOME/repository. Completed releases and reporting information are stored in another database called archive. By default, this is also an embedded database stored in XL_RELEASE_SERVER_HOME/archive. Embedded databases are automatically created when Release is started for the first time. They are used to simplify the setup for evaluation and test environments. For production use, you must use an industrial-grade external database server.

Supported databases

See Installation Prerequisites.

External database recommendation

When setting up a production-ready environment for Release which includes an active/active server configuration, you must configure the Digital.ai Release repository to use an external database.

important

You cannot migrate an existing installation with an embedded database to an external one, so it is recommended to configure your initial install to use an external database.

Backing up the database

For details about how to backup and restore the Release database, see Back up Release.

Preparing the external database

To use an external database, create two empty database schemas:

xlrelease - to store active release data and configuration data
xlarchive - to store completed releases and reporting data

note

When migrating from a previous version of Release with the archive configured in the archive directory as an embedded database, the data will remain in the embedded database and this schema should not be created in the external database.

The account that accesses the database must be able to create tables during the initial installation and, later, it must be able to write to and delete from tables.

The following set of SQL privileges are required.

During installation or upgrade:

REFERENCES
INDEX
CREATE
DROP

During operation:

SELECT
INSERT
UPDATE
DELETE

Configuration examples

Here are some configuration examples for the supported database types.

important

The scripts provided in this section are intended only as examples. When implementing your database configuration in a production environment, you should engage your database administrator to ensure that your organization's security and least privilege standards are met.

PostgreSQL

To create the Release database in PostgreSQL, execute the following script:

CREATE USER xlrelease WITH
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  ENCRYPTED PASSWORD 'xlrelease';

CREATE USER xlarchive WITH
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  ENCRYPTED PASSWORD 'xlarchive';

CREATE DATABASE xlrelease OWNER xlrelease;
CREATE DATABASE xlarchive OWNER xlarchive;

MySQL

To create the Release database in MySQL, execute the following script:

CREATE DATABASE xlrelease CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;
CREATE DATABASE xlarchive CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;

CREATE USER 'xlrelease'@'%' IDENTIFIED BY 'xlrelease';
GRANT ALL PRIVILEGES ON xlrelease.* TO 'xlrelease'@'%';

CREATE USER 'xlarchive'@'%' IDENTIFIED BY 'xlarchive';
GRANT ALL PRIVILEGES ON xlarchive.* TO 'xlarchive'@'%';

FLUSH PRIVILEGES;

When running Release functions on MySQL, change the following settings in the MySQL configuration file. To locate this file, see MySQL documentation.

Locate the [mysqld] section in the file, and add or modify the following parameters:

Setting	Value
`skip-character-set-client-handshake`
`collation_server`	`utf8mb3_general_ci`
`character_set_server`	`utf8mb3`
`max_allowed_packet`	`64M`

note

Character set of utf8mb3 is required, and a warning to move to utf8mb4 can be ignored. Therefore, utf8mb4 is not supported.

For other recommended settings please see Production environment install section

Oracle 12c/19c

To create the Release database in Oracle 12c/19c, execute the following script:

ALTER SYSTEM SET disk_asynch_io = FALSE SCOPE = SPFILE;

CREATE USER xlarchive IDENTIFIED BY xlarchive;
GRANT CONNECT,RESOURCE TO xlarchive;
GRANT CREATE SESSION TO xlarchive;

CREATE USER xlrelease IDENTIFIED BY xlrelease;
GRANT CONNECT, RESOURCE TO xlrelease;
GRANT CREATE VIEW TO xlrelease;

– Liquibase needs to access the DBA_RECYCLEBIN table so we can automatically handle the case where constraints are deleted and restored. Since Oracle doesn't properly restore the original table names referenced in the constraint, we use the information from the DBA_RECYCLEBIN to automatically correct this issue. GRANT SELECT ON SYS.DBA_RECYCLEBIN TO xlrelease; save /dblibs/touch.log create;

Microsoft SQL Server

To create the Release database in Microsoft SQL Server, execute the following script:

CREATE DATABASE xlrelease COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
USE xlrelease;
GO
CREATE LOGIN xlrelease WITH PASSWORD = 'xlrelease', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = xlrelease;
GO
CREATE USER [xlrelease] FOR LOGIN [xlrelease];
EXEC sp_addrolemember N'db_owner', N'xlrelease';
GO

CREATE DATABASE xlarchive COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
USE xlarchive;
GO
CREATE LOGIN xlarchive WITH PASSWORD = 'xlarchive', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = xlarchive;
GO
CREATE USER [xlarchive] FOR LOGIN [xlarchive];
EXEC sp_addrolemember N'db_owner', N'xlarchive';
GO

Enable snapshot isolation mode

To enable snapshot isolation mode, execute the following commands on a SQL Server:

ALTER DATABASE xlrelease SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE xlrelease SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE xlarchive SET READ_COMMITTED_SNAPSHOT ON;

MVCC

important

Unlike other supported databases, MS SQL Server does not have Multi Version Concurrency Control (MVCC) activated by default. Release requires that this feature is enabled. For more information on the settings described below, see this MSDN article.

When MVCC is enabled, you must add a weekly maintenance task to MS SQL Server. This task will maintain the indexes and query statistics:

Recompute statistics by running EXEC sp_updatestats
Clear buffers by running DBCC DROPCLEANBUFFERS
Clear cache by running DBCC FREEPROCCACHE
Rebuild indexes that are fragmented more than 30%

IBM DB2

To create the Release database in DB2, execute the following script:

create database xlr using codeset UTF8 territory us PAGESIZE 32K;
connect to xlr;

CREATE BUFFERPOOL TMP_BP SIZE AUTOMATIC PAGESIZE 32K;
connect reset;

connect to xlr;
CREATE SYSTEM TEMPORARY TABLESPACE TMP_TBSP PAGESIZE 32K MANAGED BY SYSTEM USING ("<PATH>") BUFFERPOOL TMP_BP;
CREATE SCHEMA xlrelease AUTHORIZATION xlrelease;
CREATE SCHEMA xlarchive AUTHORIZATION xlarchive;
connect reset;

Pagination

Important To use DB2 as an external database, you must increase the pagesize to 32K.

Release requires that DB2 is set in MySQL compatible mode to support pagination queries. Run the following command on your DB2 database to enable MySQL compatible mode:

$ db2set DB2_COMPATIBILITY_VECTOR=MYS
$ db2stop
$ db2start

The configuration file

All configuration is completed in the XL_RELEASE_SERVER_HOME/conf/xl-release.conf.

This file is in the HOCON format.

After the first run, passwords in the configuration file will be encrypted and replaced with the base64-encoded encrypted values.

Configuration keys available

note

The configuration keys specified in the table below are used as an example. H2/Derby should not be used in production and is not supported with the current version of XL_Release. You should use a production-grade database. To use other database connection strings in configuration keys, see [Database-specific configuration in Release](##Database-specific configuration in Release)

Here are the configuration keys available in xl.database, xl.reporting and xl.cluster.membership.jdbc:

key	Description	Default value (`xl.database`)	Default value (`xl.reporting`)	Default value (`xl.cluster.membership.jdbc`)
`db-driver-classname`	Driver class name	"org.h2.Driver"	"org.apache.derby.iapi.jdbc.AutoloadedDriver"	use `xl.database.db-driver-classname`
`db-url`	Database jdbc url	"jdbc:h2:file:./repository/db;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE"	"jdbc:derby:archive/db;create=true;upgrade=true"	value of `xl.database.db-url`
`db-username`	Database username	"sa"	""	value of `xl.database.db-username`
`db-password`	Database password	""	""	value of `xl.database.db-password`
`pool-name`	This property represents a user-defined name for the connection pool and appears mainly in logging and JMX management consoles to identify pools and pool configurations.	"RepositoryPool"	"ReportingPool"	"ClusterPool"
`max-pool-size`	This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. This value will determine the maximum number of actual connections to the database backend. A reasonable value for this is best determined by your execution environment. When the pool reaches this size, and no idle connections are available, calls to getConnection() will block for up to `connection-timeout` before timing out.	64	10	1
`connection-timeout`	This property controls the maximum number of milliseconds that a client will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. The lowest acceptable connection timeout is 250 ms	30 seconds	30 seconds	30 seconds
`max-life-time`	This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, when it is closed it then be removed. On a connection-by-connection basis, minor negative attenuation is applied to avoid mass-extinction in the pool. Important: We strongly recommend setting this value. It should be several seconds shorter than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), which is subject to the `idle-imeout` setting.	30 minutes	30 minutes	30 minutes
`idle-timeout`	This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when `minimum-idle` is defined to be less than `max-pool-size`. Idle connections will not be retired once the pool reaches `minimum-idle` connections. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. The minimum allowed value is 10 seconds.	10 minutes	10 minutes	10 minutes
`minimum-idle`	This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool. If the idle connections dip below this value and total connections in the pool are less than `max-pool-size`, HikariCP will make a best effort to add additional connections quickly and efficiently. However, for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection pool.	64	10	1
`leak-connection-threshold`	This property controls the amount of time that a connection can be out of the pool before a message is logged indicating a possible connection leak. A value of 0 means leak detection is disabled. The lowest acceptable value for enabling leak detection is 2 seconds.	15 seconds	15 seconds	15 seconds

Database-specific configuration in Release

You can configure the maximum number of threads for the reporting database by changing the value of the max-pool-size parameter. The default value for the max-pool-size parameter is 10.

PostgreSQL

Driver: PostgreSQL JDBC driver

Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
Configure XL_RELEASE_SERVER_HOME/conf/xl-release.conf to point to the database schema.

This is a sample PostgreSQL configuration:

xl {
    ...
    database {
        db-driver-classname = "org.postgresql.Driver"
        db-url = "jdbc:postgresql://localhost:5432/xlrelease"
        db-username = "xlrelease"
        db-password = "xlrelease"
    }
  reporting {
      db-driver-classname = "org.postgresql.Driver"
      db-url = "jdbc:postgresql://localhost:5432/xlarchive"
      db-username = "xlarchive"
      db-password = "xlarchive"
  }
   ...
}

MySQL

Driver: MySQL JDBC driver

Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.

Next, configure XL_RELEASE_SERVER_HOME/conf/xl-release.conf to point to the database schema.

note

Release supports MySQL versions from 5.7. If you are using MySQL 8 or above, you need to add an extra parameter to the 'db-url' setting allowPublicKeyRetrieval=true, for example:

db-url = "jdbc:mysql://localhost:3306/xlrelease?useSSL=false&nullNamePatternMatchesAll=true&allowPublicKeyRetrieval=true"

note

The maximum number of threads for the reporting database is configurable by changing the value of the max-pool-size parameter. The default value for the max-pool-size parameter is 10.

This is a sample MySQL 8 configuration. To configure it for an earlier version, remove the useSSL parameter from db-url:

xl {
    ...
    database {
        db-driver-classname = "com.mysql.jdbc.Driver"
        db-url = "jdbc:mysql://localhost:3306/xlrelease?useSSL=false&nullNamePatternMatchesAll=true"
        db-username = "xlrelease"
        db-password = "xlrelease"
    }
    reporting {
      db-driver-classname = "com.mysql.jdbc.Driver"
      db-url = "jdbc:mysql://localhost:3306/xlarchive?useSSL=false&nullNamePatternMatchesAll=true"
      db-username = "xlarchive"
      db-password = "xlarchive"
   }
  ...
}

Your MySQL instance should be configured in your cnf file as:

skip-character-set-client-handshake
collation_server=utf8_general_ci
character_set_server=utf8

Oracle

Driver: Oracle JDBC driver

note

It is recommended to use only the thin drivers. For more information on version compatibility for Oracle JDBC drivers and other FAQs, see Oracle JDBC FAQ.

Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
Configure XL_RELEASE_SERVER_HOME/conf/xl-release.conf to point to the database schema.

This is a sample Oracle configuration:

xl {
    ...
    database {
        db-driver-classname="oracle.jdbc.driver.OracleDriver"
        db-url="jdbc:oracle:thin:@localhost:1521:XE"
        db-username = "xlrelease"
        db-password = "xlrelease"
    }
  reporting {
      db-driver-classname="oracle.jdbc.driver.OracleDriver"
      db-url="jdbc:oracle:thin:@localhost:1521:XE"
      db-username = "xlarchive"
      db-password = "xlarchive"
  }
  ...
}

If you use the TNSNames Alias syntax to connect to Oracle, you must specify where the driver can find the TNSNAMES file. For more information, see the Oracle documentation.

DB2

Driver: DB2 JDBC driver

Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
COnfigure XL_RELEASE_SERVER_HOME/conf/xl-release.conf to point to the database schema.

This is a sample DB2 configuration:

xl {
    ...
    database {
        db-driver-classname="com.ibm.db2.jcc.DB2Driver"
        db-url="jdbc:db2://127.0.0.1:50000/xlr"
        db-username = "xlrelease"
        db-password = "xlrelease"
    }
  reporting {
      db-driver-classname="com.ibm.db2.jcc.DB2Driver"
      db-url="jdbc:db2://127.0.0.1:50000/xlr"
      db-username = "xlarchive"
      db-password = "xlarchive"
  }
  ...
}

This is a sample XL_RELEASE_SERVER_HOME/conf/xl-release.conf configuration for DB2. Ensure that the DB2 JDBC driver JAR file is located in XL_RELEASE_SERVER_HOME/lib or on the Java classpath.

xl {
  ...
  database {
     db-driver-classname="com.ibm.db2.jcc.DB2Driver"
     db-password="{b64}wyCfV+HXKRAo9GT9QWeqDw=="
     db-url="jdbc:db2://127.0.0.1:50000/xlr"
     db-username=xlrelease
     max-pool-size=20
  }
  ...
}

note

If you are using DB2 version 9.7.2 or later, you can enable support for LIMIT x using the DB2_COMPATIBILITY_VECTOR registry variable:

db2set DB2_COMPATIBILITY_VECTOR=MYS
db2stop
db2start      

Microsoft SQL Server

Note: The quartz property for the SQL server org.quartz.jobStore.acquireTriggersWithinLock=true must be placed inside quartz.properties file under the conf folder.

Driver: Microsoft JDBC driver for SQL Server

Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
Configure XL_RELEASE_SERVER_HOME/conf/xl-release.conf to point to the database schema.

This is a sample SQL Server configuration:

xl {
    ...
    database {
        db-driver-classname = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        db-url = "jdbc:sqlserver://localhost:1433;databaseName=xlrelease"
        db-username = "xlrelease"
        db-password = "xlrelease"
    }
  reporting {
      db-driver-classname = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      db-url = "jdbc:sqlserver://localhost:1433;databaseName=xlarchive"
      db-username = "xlarchive"
      db-password = "xlarchive"
  }
  ...
}

Supported databases​

External database recommendation​

Backing up the database​

Preparing the external database​

Configuration examples​

PostgreSQL​

MySQL​

Oracle 12c/19c​

Microsoft SQL Server​

Enable snapshot isolation mode​

MVCC​

IBM DB2​

Pagination​

The configuration file​

Configuration keys available​

Database-specific configuration in Release​

PostgreSQL​

MySQL​

Oracle​

DB2​

Microsoft SQL Server​

Supported databases

External database recommendation

Backing up the database

Preparing the external database

Configuration examples

PostgreSQL

MySQL

Oracle 12c/19c

Microsoft SQL Server

Enable snapshot isolation mode

MVCC

IBM DB2

Pagination

The configuration file

Configuration keys available

Database-specific configuration in Release

PostgreSQL

MySQL

Oracle

DB2

Microsoft SQL Server