Valid since:
XL Release 7.5.0

This document describes the database configuration for XL Release 7.5 and later versions. For previous versions that use the JackRabbit (JCR) repository, refer to Configure the XL Release JCR repository in a database.

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.

The embedded databases are automatically created when XL Release is started for the first time.

The purpose for the embedded databases is the easy setup in evaluation and test environments. For production use, it is strongly recommended to use an industrial-grade external database server. 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.5, 5.6, and 5.7
  • Oracle 11g and 12c
  • Microsoft SQL Server 2012 and later
  • DB2 versions 10.5 and 11.1

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

To run XL Release in a cluster setup (Active/active or active/hot standby) it is required to have the repository stored in an external database.

Note: It is currently not possible to migrate the repository from an embedded database to an external database. Ensure that you configure production setup with an external database from the start. When migrating from a previous JCR version of XL Release (version 7.2 and earlier) make sure to migrate to an external database.

Important: When migrating from XL Release version 7.2 and earlier, refer to Upgrade to XL Release 7.5 for detailed migration instructions.

More information:

Preparing the external database

To use an external database, two empty database schemas should be created.

  1. xlrelease - active release data and configuration data.
  2. xlarchive - 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 / upgrade:

  • REFERENCES
  • INDEX
  • CREATE
  • DROP

During operation:

  • SELECT, INSERT, UPDATE, DELETE

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;

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

    FLUSH PRIVILEGES;

To ensure that XL Release functions correctly when running on MySQL, change the following settings in the MySQL configuration file. To locate the file, refer to the 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

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

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

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

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;

Note: To use DB2 as an external database, ensure that you increase the pagesize to 32K.

XL Release requires that DB2 is set in MySQL compatible mode in order for it 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: xl-release.conf

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

This file is in 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 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;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 XL Release

PostgreSQL

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.

This is a sample configuration for PostgreSQL:

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"
    }
   ...
}

Note: As of XL Release version 8.0.0, 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.

MySQL

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.

This is a sample for MySQL:

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"
     }
    ...
}

Note: As of XL Release version 8.0.0, 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.

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

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

Oracle

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.

This is a sample for Oracle:

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"
    }
    ...
}

Note: As of XL Release version 8.0.0, 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.

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, refer to the Oracle documentation.

DB2

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.

This is a sample for DB2:

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"
    }
    ...
}

Note: As of XL Release version 8.0.0, 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 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:

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.

This is a sample for SQL Server:

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"
    }
    ...
}

Note: As of XL Release version 8.0.0, 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.