This document has been updated for CM1 5.3-SR1.

By default, the Percussion DTS server deploys with a fully functional embedded Derby database.  A Best Practice in production settings is to configure the DTS services to point to a Production RDBMS such as Microsoft SQL Server, MySQL, MariaDB, or Oracle.   Customer's deploying in a clustered or fail over DTS configuration are required to use an external database.  It is also recommended creating an account in which the password to the SQL server does not expire; if the password does expire the DTS will no loner be able to retrieve information from the database.

What's new in Version 5.3 SR1

• Support for MariaDB
• Support for Oracle 11 and 12
• Default Connection pool updated to HikariCP
• DTS applications updated to use 2nd level cache to minimize RDBMS round trips

Understanding the DTS Connection Pool

With CM1 5.3 SR1 the default connection pool for the DTS has been changed to HikariCP.  Previous versions used the BoneCP connection pool.  

The role of the connection pool is to establish a pool of database connections that are ready to receive application requests.  In the DTS example, requests are driven by traffic to pages that house DTS widgets.  The size of the connection pool and other parameters can be configured by editing the Deployment/Server/conf/perc/perc-datasources.xml.  The sample file contains sane defaults, the full HikariCP options can be found here.

The new connection pool has been tested under conditions of extreme load and performs better in high stress situations with a much smaller connection pool than the previous connection pool BoneCP.  We recommend that upgrading customers update their configuration to use the new Connection Pool for the performance improvement. 

DTS Configurations Files

The applications deployed with the DTS can be configured by modifying (or creating) .properties files in the /Deployment/Server/conf/perc directory.  There are several files that can be configured here with .sample files supplied with example configuration. 

The two configuration files that affect the database setup are:

• perc-datasources.properties: Supplies database connection parameters
• perc-datasources.xml: Configures the connection pool.

The .properties files must follow Java properties files format and syntax rules.  # may be used to comment a line.  \ is required to escape special characters in the file.  Any database created for use with Percussion must use UTF-8 formatting.  The sample files contains example connection strings for the various databases. 

Example Configuration Steps

1. Ensure that your Percussion DTS instance is not running
2. Create an empty database for Percussion DTS
   1. If you're using MS SQL Server, simply create an empty database using SQL Management Studio's default settings
   2. If you're using MySQL, run this statement against the database to create the database using the UTF-8 character set: create database [database_name] character set UTF8 collate utf8_general_ci
3. (If you will be using a SQL Server database, skip to step 4) Download the MySQL JDBC driver from: http://dev.mysql.com/downloads/connector/j/
   1. Select “Platform Independent” and download the compressed archive
   2. Locate the mysql-connector-java-*** JAR file within the archive, and copy that to:

<dts_root>/Deployment/Server/lib

4. In your <dts_root>/Deployment/Server/conf/perc directory, find these two files:

perc-datasources.properties.sample

perc-datasources.xml.sample-<RDBMS>

Remove the “.sample” or “.sample-<RDBMS>" extension from both files, renaming them to:

perc-datasources.properties

perc-datasources.xml

5.3 SR1 Upgrade Note:  Customers upgrading and switching to the new connection pool implementation will need to replace their existing perc-datasources.xml file with the new perc-datasources.xml.sample file.   The perc-datasource.properties file does NOT need to be replaced as part of this upgrade unless the database parameters have changed. 

5. Open your newly renamed perc-datasources.properties file and follow the comments to ensure that each property is properly configured for your environment, like so:

 
db.username=

This should be set to the database username you used to create the DTS database in step 2

db.password=

Input the database user’s password as plain text -- when the server reads this properties file, the password will automatically become encrypted

Note: the password encryption process may hang for certain special characters, so it is recommended to only use alpha-numerical characters in your password

db.schema=

Set to dbo for MS SQL Server, leave blank for MySQL and MariaDB, set to the user name for Oracle.

jdbcDriver=

For SQL Server, set to: net.sourceforge.jtds.jdbc.Driver
For MySQL, set to: com.mysql.jdbc.Driver or org.mariadb.jdbc.Driver (if you have a preference)

For MariaDB, set to: org.mariadb.jdbc.Driver

For Oracle, set to: oracle.jdbc.OracleDriver

jdbcUrl=

For SQL Server, set to: jdbc\:jtds:sqlserver\://<host>\:<port>/<database>
For MySQL, set to: jdbc\:mysql\://<host>\:<port>/<database>?useUnicode\=true&characterEncoding\=UTF8

For Oracle set to: jdbc\:oracle\:thin\:@//<host>\:<port>/<service>

For MariaDB set to: 

Where <host>, <port>, and <database> match your environment)

datasource=percDataSource

Leave this property untouched

hibernateProperties=percHibernateProperties

Leave this property untouched

hibernate.dialect=

For SQL Server, set to: com.percussion.delivery.rdbms.PSUnicodeSQLServerDialect

For MySQL and MariaDB, set to: org.hibernate.dialect.MySQL5InnoDBDialect

For Oracle, set to: org.hibernate.dialect.Oracle10gDialect

hibernate.query.substitutions=

Leave this property blank for all databases except Derby.

A fully configured perc-datasources.properties file for SQL Server should look something like this (omitting comments):

jdbcUrl=jdbc:jtds:sqlserver://<host_name>:<port>/<database_name>
datasource=percDataSource
hibernate.dialect=com.percussion.delivery.rdbms.PSUnicodeSQLServerDialect
db.username=<db_username>
db.password=<db_password>
hibernate.query.substitutions=
db.schema=DBO
hibernateProperties=percHibernateProperties
jdbcDriver=net.sourceforge.jtds.jdbc.Driver

A fully configured perc-datasources.properties file for MySQL should look something like this:

jdbcUrl=jdbc:mysql://<host>:<port>/<database>?useUnicode=true&characterEncoding=UTF8
datasource=percDataSource
db.username=<db_username>
db.password=<db_password>
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.query.substitutions=
db.schema=
hibernateProperties=percHibernateProperties
jdbcDriver=com.mysql.jdbc.Driver  

6. At this point you should be all set to start up your Percussion DTS instance.
   1. You should notice that your specified database fills up with new tables once the DTS instance successfully connects to it. If it does not, to troubleshoot:  Look in your /Deployment/Server/logs folder and scan recently modified logs for SQL connection errors.  Connection errors may be written to any of the log files in this directory.