Skip to main content
Pentaho Documentation

Specify Connections

Overview

Explains how to specify connections to the DI Repository.

Image of the DI Server installation steps.

After your repository has been configured, you must configure the web application servers to connect to the DI Repository. In this step, JDBC and JNDI connections are made to the Hibernate, Jackrabbit, and Quartz databases. These databases were installed on your DI Repository database during the Initialize Repository and Configure Repository sections of these instructions.

By default, the DI Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and only the Tomcat context.xml file must be modified. Complete the following tasks.

Perform Tomcat-Specific Connection Tasks

If you plan to run the DI Server on Tomcat, you must modify JDBC Connection information.

Copy Solution Database JDBC Drivers

For the DI Server to connect to the DI Repository database of your choice, add the DI Repository's database JDBC driver library to the appropriate place in the web application server on which the DI Server will be deployed. The default web application server for the archive installation process is Tomcat.

If you are using Redshift, you will need to replace our PostgreSQL 9.x driver with the PostgreSQL 8.4 driver that is recommended by Redshift.

  1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.

    Due to licensing restrictions, Pentaho does not distribute the necessary JDBC driver JARs.

  2. Copy the JDBC driver JAR you just downloaded to the /tomcat/lib/ directory.

Download and Install H2 JDBC Drivers

Install the H2 JDBC driver.
  1. Download the version 1.2.131 of the H2 JDBC JAR from the http://code.google.com/p/h2database/downloads/list.
  2. Copy the JDBC driver JAR you just downloaded to the tomcat/lib/ directory.

Modify JDBC Connection Information in the Tomcat context.xml File

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your BA Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment. You also modify the values for the validationQuery parameters in this file if you have choosen to use an BA Repository database other than PostgreSQL.

  1. Consult your database documentation to determine the JDBC class name and connection string for your BA Repository database.
  2. Go to the biserver-ee\tomcat\webapps\pentaho\META-INF directory and open the context.xml file with any file editor.
  3.  Follow the directions below for your database. Be sure to adjust the port numbers and passwords to reflect your environment, if necessary.

    For PostgreSQL:

    Comment out the resource references that refer to databases other than PostgreSQL, such as MySQL and Oracle. Then, add the following code to the file if it does not already exist.

    <Resource validationQuery="select 1" url="jdbc:postgresql://localhost:5432/hibernate" driverClassName="org.postgresql.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
    <Resource validationQuery="select 1" url="jdbc:postgresql://localhost:5432/hibernate" driverClassName="org.postgresql.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
    <Resource validationQuery="select 1" url="jdbc:postgresql://localhost:5432/quartz" driverClassName="org.postgresql.Driver" password="password" username="pentaho_user" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
    <Resource validationQuery="select 1" url="jdbc:postgresql://localhost:5432/hibernate" driverClassName="org.postgresql.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
    <Resource validationQuery="select 1" url="jdbc:postgresql://localhost:5432/hibernate" driverClassName="org.postgresql.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/> 
    

    For MySQL:

    Comment out the resource references that refer to databases other than MySQL, such as PostgreSQL and Oracle. Then, add the following code to the file if it does not already exist.

    <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
    <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
    <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/quartz" driverClassName="com.mysql.jdbc.Driver" password="password" username="pentaho_user" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
    <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
    <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
    

    For Oracle:

    Comment out the resource references that refer to databases other than Oracle, such as PostgreSQL and MySQL. Then, add the following code to the file if it does not already exist.

    <Resource validationQuery="select 1 from dual" url="jdbc:oracle:thin:@localhost:1521/XE" driverClassName="oracle.jdbc.OracleDriver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
    <Resource validationQuery="select 1 from dual" url="jdbc:oracle:thin:@localhost:1521/XE" driverClassName="oracle.jdbc.OracleDriver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
    <Resource validationQuery="select 1 from dual" url="jdbc:oracle:thin:@localhost:1521/XE" driverClassName="oracle.jdbc.OracleDriver" password="password" username="quartz" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
    <Resource validationQuery="select 1 from dual" url="jdbc:oracle:thin:@localhost:1521/XE" driverClassName="oracle.jdbc.OracleDriver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
    <Resource validationQuery="select 1 from dual" url="jdbc:oracle:thin:@localhost:1521/XE" driverClassName="oracle.jdbc.OracleDriver" password="password" username="hibuser" maxWait="10000" maxIdle="5" maxActive="20" factory="org.apache.commons.dbcp.BasicDataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
    
  4. Modify the username, password, driver class information, IP address (or domain name), and port numbers so they reflect the correct values for your environment.
  5. Make sure that the validationQuery variable for your database is set to this.
    • PostgreSQL: validationQuery="select 1"
    • MySQL: validationQuery="select 1"
    • Oracle: validationQuery="select 1 from dual"
  6. Save the context.xml file, then close it.
  7. To make sure that the changes that you made in the context.xml file take effect when Tomcat is started, navigate to the tomcat\conf\Catalina directory. If the pentaho.xml file is in the present, delete it. It will be generated again when you start the BA Server, but will contain the changes that you just made in the context.xml file.