This document describes how to make Journyx 8.x or higher work with an external database such as Microsoft SQL Server, Oracle, or PostgreSQL.

 

All Journyx customers who wish to use an external database should read this page carefully as the requirements and supported databases have changed in Journyx 8. An "external database" is any database system that is not supplied by Journyx. Only three external database platforms are supported by Journyx:


Please make sure you have reviewed and complied with the hardware requirements, supported operating systems, and supported database platforms before proceeding. These requirements have changed as of Journyx 8.0.

 

All customers who are setting up external database installations should at least read the General Notes section and the section pertaining to your specific database system. All of the instructions and guidelines on this page apply equally to Windows and Unix servers unless otherwise noted.

 

You must follow all of the steps in this page for a successful external database install of Journyx. Any optional steps are clearly noted. Please read this page thoroughly before beginning your Journyx setup process.

 

High Level Overview

 

These are the general steps to follow to install Journyx to an external database.

 

1. If your external database is not already up and running, you must install it according to the vendor's instructions. Make sure you have installed all available security patches and hotfixes.
2. Set up a database (also called tablespace) for the Journyx data. Make sure to choose the appropriate character set and collation as described under the section for each particular type of database. The collation affects the linguistic sort order of items in various screens.
3. Set up a database user account for just for Journyx. Each Journyx installation needs its own separate database user account.
4. Install your database vendor's client access software on the Journyx server. Journyx does not provide either Windows or Unix/POSIX database drivers.
5. (Windows only) Set up a Windows System DSN that connects to the appropriate database (tablespace) as the appropriate database user. Test the connection from the ODBC control panel with the Journyx account name and password before proceeding.
6. (Unix only) If you are using Oracle, you must obtain and install the Oracle Instant Client software for your operating system. Read and follow the included instructions. This package is available directly from Oracle. Only the "Basic" package is required for Journyx. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Journyx.
7. Install the Journyx application according to the instructions. When prompted during the Windows installation, type in the name of the System DSN and the database user's ID and password. On Unix you give the connection information to your database. For full details see the platform-specific instructions below.
8. The Journyx installation process will automatically create all the necessary tables, indexes, and other schema objects. On Windows it will then forward your browser to the site login screen. On Unix the URL of the site will be printed in the terminal.

 

General Notes for ALL Databases

 

Vendor-Specific Setup Instructions

 

Each supported DBMS (Database Management System) has its own set of specific instructions. In addition to that there are general instructions that apply to all database systems. Please review these general considerations, then check below for special instructions for your specific database system. After that you can follow the rest of the instructions in the "Journyx Setup for All Databases" section.

 

Character sets, collations, national languages, 'special' characters, etc.

 

Journyx 8.0 and higher uses Unicode character storage which can represent virtually any human language. However only a single "collation" or linguistic sort ordering can be used on a Journyx site. The collation affects the ordering of results results, among other things. Generally you can only pick the collation when you initially set up the database. Once the character set and collation are chosen they cannot be easily changed later, so please read and follow these guidelines carefully. Note that on Microsoft SQL Server you must choose a collation that is marked "CS" or "Case Sensitive." Please refer to the Journyx Support Unicode page for more details on choosing a database encoding. See also the Wikipedia article on Unicode.

  • PostgreSQL: Always select "Unicode" / "UTF8" for the character set when creating a new database for Journyx. The collation is determined by your operating system environment - the "Locale" on Unix and "Region and Language" settings on Windows. The collation is set when database is created (initdb) and cannot be changed later. Before you install on Unix you can run the command "locale" to print out the currently selected locale. For instance, en_US.utf-8 indicates English (en) as used in the United States (US). And de_CH.utf-8 indicates German (de) as used in Switzerland (CH). The command psql -l will print the encoding and collation of all databases in the system.
  • Oracle: Journyx will automatically use the Unicode character set on Oracle regardless of which character set is selected. However it will default to using UTF-16 which is less efficiently processed for most Western European languages. It is recommend you install to a UTF-8 "national character set" for optimum efficiency. See the Unicode support page and the external Oracle instructions for more details. The National Language setting (NLS_LANG) affects the collation (sort order). National Language and related settings are a complex topic in Oracle. Please speak with your Oracle DBA regarding the optimal settings for your users.
  • Microsoft SQL Server 2005+: Be sure to pick a "Case Sensitive" collation such as SQL_Latin1_General_Cp1_CS_AS. Microsoft denotes case sensitive with the letters CS in the collation name. Journyx will automatically use the Unicode character set on Microsoft SQL Server but the collation affects the sort ordering of search results.


Database user accounts

 

You must always create a separate Database user (login) and database (tablespace) for Journyx in your Database Management System (DBMS). Do not let Journyx use the "sa", "sys", or "system" accounts, and do not give the Journyx user access to any other database objects outside of its own tablespace / schema. Failing to follow Best Practices in this regard could put the security or availability of your DBMS in jeopardy.

 

In some versions of Microsoft SQL Server, by default the administrator account (which has complete control over all data) is "sa" and the password is "" (a blank password.) If you have not already changed the SA password, Journyx strongly recommends you do so as a first step before completing the rest of these instructions. Contact your organization's Database Administrator or SQL Server documentation for details on how to change account passwords.

 

Similarly, Oracle has a set of default administration passwords that should be changed. The administrative passwords should be changed immediately after installation and a separate database user account for Journyx should be created.

 

Vendor Specific Instructions

 

Please find the section below appropriate to your specific database and follow those setup instructions. Then skip down to the "Journyx Setup for All Databases" section below.


Journyx Setup for All Databases

 

In order to connect your Journyx server to your database, you must obtain the database vendor's drivers and install them on the Journyx application server. Journyx does not supply ODBC drivers or other database drivers. Before you install Journyx, you should make sure that you have a working database connection to your database server. You should test it with the connection tools provided by the database vendor.

 

On Windows, it is possible to change the DSN at any time in Journyx by running the "Setup Web Server and Database" program under the Journyx menu. This program will allow you to select a data source from the list of DSNs on your system. It must be a System DSN, not a User DSN.

 

The Database Must Be Blank!

 

You can only use a blank database with Journyx: one that does not already have any tables. The Journyx Database Integration program will not let you proceed if there are already tables in the database. This is in order to prevent you from installing over an existing Journyx site and deleting all of your existing Time and Expense data.

 

Normally we recommend that you simply create a new user and tablespace for your new install and leave your old external database in place in case you need it. To reuse a database that already has tables, you must first 'wipe' the database by dropping all tables. Your Database Administrator or Journyx Support can help you with this.

 

Once you have a working database connection, you may install Journyx. If you have already installed it, run the "Setup Web Server and Database" program again. This program will configure your web site and database connection. If it detects that there are already tables in that connection, the program will print a message about this and exit. (See above.) Otherwise, it will prompt you to either create a new Journyx site or "Cancel" (do nothing.) Usually you will want to click the "Create a new site" option. You can always run the restoredb utility from the command line at any time. If you hit "Cancel" here, no modifications will be made to the database, but Journyx itself will not be working.

 

Windows: Setup Journyx for your Database

 

This section describes how to set up a database connection for Windows Journyx servers. Unix Journyx administrators should consult the instructions in the Oracle setup instructions or the PostgreSQL setup instructions.

 

1. Create an ODBC Data Source

  • Select Start -> Settings -> Control Panel to bring up the control panel. Click Administrative Tools, then Data Sources. Or type "Data Sources" into the search field.
  • Select the 'System DSN' tab to bring the list of System Data Sources in view.
  • Select 'Add' to create a new data source. This will bring up a list of ODBC client drivers. Select the either the 'SQL Server Driver' or the Oracle driver or other appropriate driver for your External database, and click on 'Finish' to create the DSN. This will bring up a panel to setup the DSN.
  • Fill in the Data Source Name (for example, jtimedsn). This is the name you will give to the Journyx configuration page to allow connection to this data source.
  • Fill in the 'Server' with the name of the SQL Server host. If Journyx is installed on the same host as MS SQL Server, then you can choose (localhost). For Oracle and other drivers, you will need to supply an appropriate connection name. Oracle users may have to use the Oracle Net8 Assistant to configure a Service Name on the machine that runs Journyx.
  • If using SQL Server, depending on which SQL Server ODBC Driver you have installed, you need to complete setup in one of these two ways:
    • Method One (there's an 'Options' button):Select 'Options' and fill in the database Name with the name you created earlier (eg: journyx).Method Two (there is no 'Options' button, but rather a 'Next' button):
    • Select 'Next'.
    • Select 'SQL Server authentication'.
    • Make sure 'Connect to SQL Server' is checked.
    • Fill in DBA administrator user name and password.
    • Make sure that 'Change the default database to' is checked and then select the new database (journyx)
    • Left-click 'Next' twice.
    • Left-click 'Finish' to complete setup.
    • Now click 'Test Data Source' to verify the setup.
    • If the test was successful, left-click 'OK'; otherwise, verify your settings and try again.


2. Finally, select 'OK' to complete configuration of this DSN.
3. Complete the Journyx Configuration.

  • Normally the "Setup Web Server and Database" program (also known as Integration) runs automatically after the installer reboots your computer.
  • To run it again, simply click on Start, then Programs, then "Journyx", then "Setup Web Server and Database."
  • The program will prompt you to select a database type (SQL Server, Oracle, or PostgreSQL.)
  • The program will ask you for the database login name and password. Use the account name and password that you created by following the instructions above.
  • Some databases have extra parameters to specify tablespaces for indexes and data.
  • Using the information you provided, the program will attempt to establish a database connection. If it fails, you will have the opportunity to try it again or specify a new connection.
  • The most common cause of failure is usually a wrong or mistyped password. Other common causes of failure include incorrect network configuration that may be specific to your particular DBMS system. Make sure that the "Test" button works in the DSN Properties.
  • If you still cannot establish a connection, first try contacting your local Database Administrator. He or she may need to see the IntegrationLog.txt file that is in the directory where you installed Journyx.
  • If you are the local Database Administrator, please contact Journyx Support. Be sure to send the IntegrationLog.txt file when you email.