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.

Download Drivers and Install with the JDBC Distribution Tool

For the BA Server to connect to the BA Repository database of your choice, you will need to download and install a JDBC driver to the appropriate places on the web application server which contains the Pentaho Server(s). The default web application server for the archive installation is Tomcat.

Due to licensing restrictions, Pentaho does not distribute the necessary JDBC driver JARs. This is why you have to download the file yourself and install it.

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.
  2. Copy the JDBC driver JAR you just downloaded to the pentaho/jdbc-distribution directory.
  3. Open a cmd prompt and point it at the pentaho/jdbc-distribution directory and do this:
    distribute-files.bat <name of JDBC driver JAR>
    
  4. Restart your Pentaho Server(s) and Client tools.
  5. Verify that the appropriate drivers are installed as shown in this table.
List of Products and Corresponding Locations for JDBC Drivers
Server or Design Tool Directory
Business Analytics (BA) Server pentaho/server/biserver-ee/tomcat/lib
Data Integration (DI) Server pentaho/server/data-integration-server/tomcat/lib
Pentaho Data Integration (Spoon) pentaho/design-tools/data-integration/lib
Pentaho Report Designer (PRD) pentaho/design-tools/report-designer/lib/jdbc
Pentaho Aggregation Designer (PAD) pentaho/design-tools/aggregation-designer/drivers
Pentaho Schema Workbench (PSW) pentaho/design-tools/schema-workbench/drivers
Pentaho Metadata Editor (PME) pentaho/design-tools/metadata-editor/libext/JDBC

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/di_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/di_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/di_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/di_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/di_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/di_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/di_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/di_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.