Skip to main content
Pentaho Documentation

Specify Connections

Overview

Provides instructions on how to specify connections.

Image of the BA Manual installation steps.

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

By default, the BA 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. For JBoss, both JDBC and JNDI connection information must be specified. Since what must be completed varies according to web server, tasks in this section are grouped according to the web application server you have installed.

If you have Tomcat, complete the following tasks.

  • Download and Install Repository Database JDBC Drivers
  • Modify JDBC Connection Information

If you have JBoss, complete the following tasks.

  • Define JNDI Connection Information
  • Remove JNDI Resource References
  • Update JNDI Data Source References to Conform with JBoss Standards

Perform Tomcat-Specific Connection Tasks

If you plan to run the BA 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. View the contents of the pentaho.war file with a zip utility, such as 7-Zip, WinZip, or Archive. Do not unzip the file.
  3. Use a text editor of your choice to open the context.xml file that is in Tomcat's META-INF/ directory.
  4. Do one of these things.
    1. If you are using PostgreSQL as your BA Repository database, comment out the resource references that refer to other databases, such as MySQL and Oracle. In the PostgreSQL sections make sure that the code looks like the following, but adjust the port numbers and passwords to reflect your environment, if necessary.
      <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"/>
      
    2. If you are using MySQL as your BA Repository database, comment out the resource references that refer to other databases, such as PostgreSQL and Oracle. Then, add the following code to the file if it does not already exist. Adjust the port numbers and passwords to reflect your environment, if necessary.
      <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"/>
      
    3. If you are using Oracle as your BA Repository database, comment out the resource references that refer to other databases such as PostgreSQL and MySQL. Then, add the following code to the file if it does not exist. Adjust the port numbers and passwords to reflect your environment, if necessary.
      <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"/>
      
  5. Modify the username, password, driver class information, IP address (or domain name), and port numbers so they reflect the correct values for your environment.
  6. Make sure that the validationQuery variable for your database is set to one of these.
    • PostgreSQLvalidationQuery="select 1"
    • MySQL:validationQuery="/* ping */ select 1"/
    • Oracle:validationQuery="select 1 from dual"
  7. Save the context.xmlfile, then close it.
  8. To verify that the changes have been made and saved, do this.
    1. View the context.xml file. Make sure that the changes that you made to the file in these instructions have been saved.
    2. Close the file.
  9. To make sure that the changes that you made in the context.xml file take effect when Tomcat is started, navigate to the <your tomcat installation directory>\conf\Catalina\localhost 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.

Perform JBoss-Specific Connection Tasks

To define JDBC and JNDI connections, several JBoss-specific tasks are required.

Install JDBC Driver as a Module in JBoss

In JBoss, JDBC driver information is stored in a module, which is an XML file that you create. You must download the JDBC driver software component to the correct directory, then create module.xml files for each database. You need to create a file for the database that hosts the BA Repository (either PostgreSQL, MySQL, or Oracle), as well as for HSQLDB.
  1. Navigate to the pentaho/server/biserver-ee/<your jboss installation directory>/modules/system/layers/base/org folder and create one of the following paths for the database on which you are hosting the BA Repository.
    • PostgreSQL: postgresql/main
    • MySQL: mysql/main
    • Oracle: oracle/main
  2. Download the supported JDBC driver for your BA Repository database to the postgresql/main, mysql/main, or oracle/main directories (which ever one you created). See the JDBC Drivers Reference for a list of supported drivers.
  3. Navigate to the pentaho/server/biserver-ee/<your jboss installation directory>/modules/system/layers/base/com/h2database directory and create the following path: hsqldb/main.
  4. Download the supported JDBC driver for HSQLDB. Place HQSLDB's driver in the hsqldb/main directory . See the JDBC Drivers Reference for a list of supported drivers.
  5. In the postgresql/main, mysql/main, or oracle/main (which ever one you created), do the following things.
    1. Use an editor to create a text file named module.xml.
    2. Copy the following code to the module.xml file, then modify it so that the name of the JDBC driver you just downloaded appears.
      • PostgreSQL: If you are using PostgreSQL, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
        <?xml version="1.0" encoding="UTF-8"?>
            <module xmlns="urn:jboss:module:1.0" name="org.postgresql">
                <resources>
                    <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
                </resources>
                <dependencies><module name="javax.api"/></dependencies>
            </module>
        
      • MySQL: If you are using MySQL, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
        <?xml version="1.0" encoding="UTF-8"?>
            <module xmlns="urn:jboss:module:1.0" name="org.mysql">
                <resources>
                    <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
                </resources>
                <dependencies><module name="javax.api"/></dependencies>
            </module>
        
      • Oracle: If you are using Oracle, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
            <?xml version="1.0" encoding="UTF-8"?>
            <module xmlns="urn:jboss:module:1.0" name="org.oracle">
                <resources>
                    <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
                </resources>
                <dependencies><module name="javax.api"/></dependencies>
            </module>  
        
    3. Save and close the module.xml file.
  6. In the hsqldb/main directory, do these things.
    1. Use an editor to create a text file named module.xml.
    2. Copy the following code to the module.xml file, then modify it so that the name of the JDBC driver you just downloaded appears in the resource-root path.
      <?xml version="1.0" encoding="UTF-8"?>
      <module xmlns="urn:jboss:module:1.0" name="com.h2database.hsqldb">
      <resources>
      <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
      </resources>
      <dependencies><module name="javax.api"/></dependencies>
      </module>
      
    3. Save and close the module.xml file.

Define JNDI Database Connection Information in JBoss

JNDI is used to specify port, driver, user name, and password information for the Audit and Quartz databases that are housed on your BA Repository database. JNDI provides a common interface for different naming services, such as DNS, LDAP, and Microsoft Active Directory. Instead of having to remember the details for how to connect or interact with the data for many different naming services, you need to only use an XML to specify the information you want to pass to the naming service.

  1. Use a text editor to open the pentaho/server/biserver-ee/<your jboss installation directory>/standalone/configuration/standalone.xml file.
  2. Insert these lines after the definition of ExampleDS data source.
                    <datasource jndi-name="java:jboss/datasources/Hibernate" pool-name="hibpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/hibernate
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    hibuser
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
                        <datasource jndi-name="java:jboss/datasources/Quartz" pool-name="quartzpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/quartz
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    pentaho_user
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
                        <datasource jndi-name="java:jboss/datasources/Audit" pool-name="auditpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/hibernate
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    pentaho_user
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
     <datasource jndi-name="java:jboss/datasources/Pentaho_Operations_Mart" pool-name="Pentaho_Operations_Mart" enabled="true" jta="true" use-java-context="true" use-ccm="true"> 
         <connection-url> 
             jdbc:postgresql://localhost:5432/hibernate 
         </connection-url> 
         <driver-class> 
             org.postgresql.Driver 
         </driver-class> 
         <driver> 
             org.postgresql 
         </driver> 
         <pool> 
             <prefill> 
                 false 
             </prefill> 
             <use-strict-min> 
                 false 
             </use-strict-min> 
             <flush-strategy> 
                 FailingConnectionOnly 
             </flush-strategy> 
         </pool> 
         <security> 
             <user-name> 
                 hiuser 
             </user-name> 
             <password> 
                password 
             </password> 
         </security> 
    </datasource>
    
  3. If your environment (e.g. port numbers, IP address), solution repository, or database password and username information differs from the code you added in the previous step, modify it to match your specifications.
  4. Add the driver definition in the driver section of the file. Here is an example of the PostgreSQL driver definition. If you are using MySQL or Oracle, modify the driver name, module, and data source class accordingly.
     <driver name="org.postgresql" module="org.postgresql">
      <xa-datasource-class>
          org.postgresql.xa.PGXADataSource
          </xa-datasource-class>
      </driver>
    
  5. Close and save the standalone.xmlfile.
  6. Open the pentaho/server/biserver-ee/pentaho-solutions/system/applicationContext-spring-security-jdbc.xml file. Change the port number, driver class name, user name, and password to reflect your environment's settings, if necessary. When complete, save and close the file.

Add JBoss Deployment Structure File to pentaho.war

The jboss-deployment-structure.xml file controls class loading. It prevents automatic dependencies from being added, adds dependencies, defines additional modules, changes isolated class loading behavior, and adds additional resource roots to a module. You will need to create, then add a JBoss deployment structure file (jboss-deployment-structure.xml) to the pentaho.war.

  1. Use a text editor to create a new file named jboss-deployment-structure.xml.
  2. Copy the following code snippet to the jboss-deployment-structure.xml file.
<jboss-deployment-structure>
<deployment>
<exclude-subsystems>
<subsystem name="resteasy" />
<subsystem name="jaxrs" />
<subsystem name="webservices" />
</exclude-subsystems>
<dependencies>
<module name="com.h2database.h2" />
<module name="org.postgresql" />
<module name="com.h2database.hsqldb" />
</dependencies>
</deployment>
</jboss-deployment-structure>
  1. Save and close the file.
  2. Use a zip extraction utility (such as 7-Zip, Winzip, or Archive) to view the contents of the pentaho.war file. Do not unzip or extract the contents of the file.
  3. Navigate to the WEB-INF directory and add the jboss-deployment-structure.xml file that you just created to it.
  4. Close the pentaho.war file. The zip extraction utility that you used might show a prompt that asks whether you would like to update the file in the pentaho.war archive. If this happens, confirm that you would like to do this.

Remove JNDI Resource References in JBoss

Because JBoss has its own mechanism for referencing JNDI data sources, the resource-references in the web.xml file located in the pentaho.war are not needed. You must remove these resource-references for the BA Server to operate properly.
  1. Navigate to the pentaho/server/biserver-ee/<your jboss installation directory>/standalone/deployments directory.
  2. Use a zip extraction utility (such as 7-Zip, Winzip, or Archive) to view the contents of the pentaho.war file. Do not unzip or extract the contents of the file.
  3. Navigate to the WEB-INF directory and open the web.xml file in a text editor.
  4. Delete all <resource-ref> tagged entries including everything between the <resource-ref> and </resource ref> tags.
  5. Save and close the file.
  6. The zip extraction utility that you used might show a prompt that asks whether you would like to update the file in the pentaho.war archive. If this happens, confirm that you would like to do this.

Update JNDI Data Source Reference to Conform to JBoss Standards

Update these files so that referenced JNDI datasources conform to JBoss standards.

  1. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/quartz/quartz.properties file.
  2. Change the org.quartz.dataSource.myDS.jndiURL value to jboss/datasources/Quartz, then save and close the file.
  3. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/audit_sql.xml file.
  4. Change the JNDI value to jboss/datasources/Hibernate, then save and close the file.
  5. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/data-access/settings.xml file.
  6. Change the data-access-staging-jndi value to jboss/datasources/Hibernate, then save and close the file.
  7. Open the pentaho/server/biserver-ee/pentaho-solutions/system/audit/dialects/h2 folder. Use the text editor to open each file and make the following changes:
    • Change <database>Audit</database> to <database>jboss/datasources/Audit</database>.
    • Change <database>Hibernate</database> to <database>jboss/datasources/Hibernate</database>.