Icon

Comments have been closed on this page. Please use AppMon & UEM Open Q & A forum for questions about this plugin.

By default, easyTravel uses an internal Derby Database on the Java side and an SQL Server Compact internal database on the .NET side. However you can change this to use other types of RDBMS, this page describes how you can configure this.


Java

Connection settings

In the EasyTravel installation (dist) folder, search for this file:

These properties control the Database:

The above values are part of the standard EasyTravel installation.

They can be changed to use any database desired. The following sets of properties have been
tested against DB/2, SQLServer and Oracle DBMS:

Icon

If external RDBMS is on the same machine, the property:
config.internalDatabasePort=1527
must point to some free port. EasyTravel must start a derby instance, even when it is using external RDBMS

DB/2

Oracle

Icon

Oracle versions 12 is only supported in easyTravel builds 2542 or higher due to a required newer version of Hibernate (see HHH-9044).

Icon

easyTravelBusiness is an Oracle SID
$user_name and $user_pass should be filled with real values

Warning! Due to issue ORA-01882: timezone region not found while connecting to Oracle workaround there is workaround, jvm parameters have to contain -Duser.timezone=GMT. Of course it should be adjusted to your timezone. Example is below:

also files launching jvm like weblauncher/weblauncher.sh should look like below:

Example code for the DB user creation:

Icon

The easyTravel user must be granted the EXECUTE permission on the DBMS_LOCK package (e.g with grant execute on dbms_lock to easytraveluser). DBMS_LOCK grant to a role is not sufficient.

SQL Server

Locale

Icon

Note that easyTravel will insert a number of locations with non-ASCII characters as part of the name, e.g. Århus. Make sure you set up the SQL Server database with a local that supports these characters.

SQL_Latin1_General_CP1_CI_AS is reported to work, also any UTF-8 or UTF-16 based encoding should be fine.

 

There are at least two different JDBC drivers available for SQL Server, depending on which one you want to use the following settings apply:

SQL Server with JTDS-JDBC Driver

Icon

Parameter 'instance' is optional and can be omitted.

There are two stored procedures used by easyTravel but not created automatically:

* sp_findJourneys - if you intend to use the FindJourneysSqlServerQueryOverride easyTravel plugin, you can either
setup the sp_findJourneys stored procedure yourself, or use the provided source file sp_findJourneys.sql,
which is located in the plugin's JAR file root.

* sp_verifyLocation - used by SlowAuthentication plugin. You can create the procedure using provided source file in the sp_verifyLocation.sql which is located in the plugin's JAR file root.

 

SQL Server with Microsoft JDBC Driver

easyTravel cannot ship the Microsoft JDBC driver because of license considerations. To make it work, you need to complete the following steps:

  • Download the MS SQL JDBC drivers at: http://go.microsoft.com/fwlink/?LinkId=245496
  • Unpack "mssql-jdbc-7.0.0.jre8.jar" to C:\Program Files\dynaTrace\easyTravel (x64)\lib
  • Unpack "META-INF/MANIFEST.MF" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
  • Add "lib/mssql-jdbc-7.0.0.jre8.jar" to the end of the Class Path in MANIFEST.MF
  • Overwrite the MANIFEST.MF in "com.dynatrace.easytravel.database.jar" with your changed file.

 

Icon

If you are using a new edition of SQL Server you might encounter an error which looks like "Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'". In such a case, you need to:

  • Unpack "META-INF/persistence.xml" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
  • Modify this file by adding a new property in the properties section:
  • Overwrite the persistence.xml in "com.dynatrace.easytravel.database.jar" with your changed file

 

MySQL

Icon

Using easyTravel with MySQL leads to errors about duplicate entries with special characters in the Journey/Location-Name, e.g. "Ibagué" and "Ibague"! (Detailed informations: https://bugs.mysql.com/bug.php?id=57877). There are two possible solutions for this:

  1. Setting collation to utf8_bin, as described below should remove this problem (tested with MySQL 5.5, 5.6).
  2. You can try to adjust the file "data/Cities.txt" inside the file com.dynatrace.easytravel.database.jar, by removing following cities: Vitória, Ibagué, Århus, Székesfehérvár, Mérida, Gdañsk, Córdoba, Skövde, Tabor, Malmo, Villa Maria.

How to configure MySQL

Create a database and allow access from other machines:
Adjust config at /etc/mysql/my.cnf, in section [mysqld]:

  • add

    character-set-server = utf8
    collation-server = utf8_bin

  • comment out 
    bind-address = 127.0.0.1

http://dev.mysql.com/doc/refman/5.0/en/Install MySQL on Ubuntu 14.04Install MySQL on Ubuntu 16.04

Database schema

EasyTravel will try to automatically create the schema, which works for built-in Derby and the above mentioned DBMS.
This is done with the hibernate feature hibernate.hbm2ddl.auto set to update.

It also creates the database content on startup. Existing content will not be erased.


dotNET

Currently two databases are supported:

  • SQL Server Compact Edition (which is default)
  • SQL Server

to switch to SQL Server you have to modify dotNetPaymentBackend/web.config.

at the bottom of the file you will find the element "connectionStrings". There remove the currently active connection-strings for "PaymentEntities" and "SQLConnection" and uncomment the other two, which are prepared for SQL Server.
Then fill out the place-holders with the corresponding values:

  • **source** --> server
  • **database** --> datebase-name
  • **user** --> db-user
  • **password** --> db-password

Make sure to modify both the SQLConnection and PaymentEntities.

Currently Schema has to be set to "dbo" which is the default value of SQL Server.

To create the tables on SQL Server with the correct constraints the following Script can be used:

  1. Anonymous (login to see details)

    stored procedures are attached to the page now. We had to go hunting in the jar files

    com.dynatrace.easytravel.plugin.sqlserverqueryoverride.jar   &   com.dynatrace.easytravel.plugin.slowauthentication.jar.

    to find them. These are the stored procedures for the SQL server configuration for easytravel

     

  2. Anonymous (login to see details)

    Does the 6.5 version of easytravel not allow for SQL server RDBMS?

  3. Anonymous (login to see details)

    If you set up a local SQL server here is one configuration that works.

    # Starting and accessing the SQL Database from Java
    #################################################################################
    # for starting the internal Derby database in the Launcher, set to "false" if you connect
    # to a different RDBMS
    config.internalDatabaseEnabled=false
    config.internalDatabasePort=1433
    config.internalDatabaseHost=localhost

    # how to connect to a SQL database
    config.databaseDriver=net.sourceforge.jtds.jdbc.Driver
    config.databaseUrl=jdbc:jtds:sqlserver://localhost:1433/easytravel;instance=MSSQLSERVERDT
    config.databaseUser=easytravel
    config.databasePassword=easytravel

  4. Anonymous (login to see details)

    Thanks for trying and posting, but we describe both ways of connecting via the Microsoft JDBC driver and the open source JTDS JDBC driver above, is anything missing there?