Configure the SQL repository

XL 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 XL 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

The following databases are supported:

  • PostgreSQL versions 9.3, 9.4, 9.5, 9.6, and 10.1 Note: The archiving database and the normal database must point to different external databases.
  • MySQL versions 5.7 and 8.0
  • Oracle 11g and 12c
  • Microsoft SQL Server 2012 and later
  • DB2 versions 10.5 and 11.1.

External database recommendation

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

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

Clustering and backup

For details about database clustering and backup, see the following topics:

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 XL 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 XL 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 XL Release database in MySQL, execute the following script:

CREATE DATABASE xlrelease;
CREATE DATABASE xlarchive;

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 XL Release functions on MySQL, change the following settings in the MySQL configuration file. To locate this file, see MySQL documentation.

Setting Value
skip-character-set-client-handshake
collation_server utf8_unicode_ci
character_set_server utf8

Oracle 11g

To create the XL Release database in Oracle 11g, execute the following script:

ALTER SYSTEM SET disk_asynch_io = FALSE SCOPE = SPFILE;

CREATE USER xlarchive IDENTIFIED BY xlarchive;
GRANT CONNECT,RESOURCE,DBA TO xlarchive;
GRANT CREATE SESSION TO xlarchive WITH ADMIN OPTION;

CREATE USER xlrelease IDENTIFIED BY xlrelease;
GRANT CONNECT,RESOURCE,DBA TO  xlrelease;
GRANT CREATE SESSION TO xlrelease WITH ADMIN OPTION;

save /dblibs/touch.log create;

Microsoft SQL Server

To create the XL 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 = xlrelease;
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. XL 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 XL 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.

XL 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

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.jdbc.AutoloadedDriver” use xl.database.db-driver-classname
db-url Database jdbc url “jdbc:h2:file:./repository/db;DBCLOSEONEXIT=FALSE;AUTOSERVER=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 XL 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

  1. Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
  2. 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: XL 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/xlrelease?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: For Oracle 12c, use either the 12.1.0.1 driver (ojdbc7.jar) or the 12.2.x driver (ojdbc8.jar). XebiaLabs recommends using only the thin drivers. Refer to the Oracle JDBC driver FAQ for more information.

  1. Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
  2. 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

  1. Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
  2. 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      

SQL Server

Driver: Microsoft JDBC driver for SQL Server

  1. Place the driver JAR file in the XL_RELEASE_SERVER_HOME/lib folder.
  2. 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"
  }
  ...
}