Skip to main content

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Set up JNDI connections for the Pentaho Server

Parent article

Instructions for adding JNDI connections vary depending on which web application server you are using.

  • If you installed the Pentaho Server using the Pentaho Installation Wizard, you are using a Tomcat web application server.
  • If you installed the Pentaho Server using the manual installation method, you are using either a Tomcat or JBoss web application server.

If you installed the Pentaho Server using the manual installation method, then you specified data connections to your Tomcat or JBoss web application server. If you want to modify existing connections or define more JNDI connections, perform the appropriate set of instructions below.

This material is only for IT administrators who know where data is stored, how to connect to it, details about the computing environment, and how to use the command line to issue commands for Microsoft Windows or Linux. You should also know how to install a database and a web application server.

Defining JNDI connections for PDI clients

If you are publishing to the Pentaho Server from a PDI client, Pentaho supplies a method for you to configure your PDI client to have the same JNDI connection information as the Pentaho Server. By using this method, your application server will not be continuously running during the development and testing of transformations.

  • To configure a JNDI connection for your PDI client, edit the jdbc.properties file to mirror the JNDI connection information of your application server data sources. The jdbc.properties file is located here: /pentaho/design-tools/data-integration/simple-jndi.

Add drivers

The Pentaho Server needs the appropriate driver to connect to the database that stores your data. Your database administrator, Chief Intelligence Officer, or IT manager should be able to provide the appropriate driver. If not, you can download drivers from your database vendor's website. The Components Reference contains a list of drivers.

Once you have the correct driver, copy it to the following directories:

  • Pentaho Server: /pentaho/server/pentaho-server/tomcat/lib/
  • PDI client: data-integration/lib

You must restart PDI client for the driver to take effect.

There should be only one driver for your database in the directory. Ensure that there are no other versions of the same vendor's driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts. This is a concern when you are adding a driver for the same database type as your Pentaho Repository. If you have any concerns about how to proceed, contact Pentaho Support.

Driver for Microsoft SQL Server

If you are using a Microsoft SQL Server (MSSQL), you might need to use an alternative, non-vendor-supported driver called JTDS. Contact Pentaho Support to ensure that you are adding the correct driver.

syntax xml:For Microsoft Windows, most JDBC drivers support Type 2 integrated authentication through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all machines and directories to which you copied the JDBC driver. You can find this file in this location.<installation directory>\sqljdbc_<version>\<language>\auth\

If running:Use the sqljdbc_auth.dll file here:
64-bit JVM on a x64 processorx64 folder
64-bit JVM on an Itanium processorIA64 folder

Tomcat JNDI connections

If you installed the Pentaho Server using the manual installation method, then you specified data connections to your Tomcat web application server. If you want to modify existing connections or add more connections, perform the following tasks.
NoteDatabase connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho 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 chosen to use a Pentaho Repository database other than PostgreSQL.

Procedure

  1. Stop the Tomcat and Pentaho Servers.

  2. Consult your database documentation to determine the class name and connection string for your database.

  3. Edit the /tomcat/webapps/pentaho/WEB-INF/web.xml file.

  4. At the end of the <web-app> element, in the same part of the file where you see <!-- insert additional resource-refs -->, add the following XML snippet:

    <resource-ref>
        <description>myDataSource</description>
        <res-ref-name>jdbc/myDataSource</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

    Change the description and res-ref-name nodes, as well as any others that apply to your situation and fit your database. You may need to consult the JNDI Resources article in the Apache Tomcat documentation to see if there are other components to consider.

  5. Save and close the web.xml file.

  6. Edit the /tomcat/conf/context.xml with a text editor. Alternatively, you can modify the /tomcat/webapps/pentaho/META-INF/context.xml file if you want this data connection to be available only to the Pentaho Server. Adding JNDI connections to the context.xml makes them available to all of the webapps deployed to this Tomcat instance.

  7. Anywhere inside of the Context element, add the following XML snippet:

    <Resource name="jdbc/myDataSource"
        auth="Container" type="javax.sql.DataSource"
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        maxActive="20"
        maxIdle="5"
        maxWait="10000"
        username="dbuser"
        password="password"
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://127.0.0.1:5432/myDataSource"
    />
  8. The example above shows a simple PostgreSQL configuration. Replace the Resource name, username, password, driverClassName, and url parameters, or any relevant connection settings, to match your database connection information and the details you supplied in the web.xml file earlier.

  9. Save and close the context.xml file.

  10. Delete the pentaho.xml filed located in the /tomcat/conf/catalina/ directory. The pentaho.xml is a cached copy of the context.xml file you modified. Since the cache is not usually configured to update frequently, you have to delete the pentaho.xml file and let Tomcat recreate it when it starts up.

  11. Start the Tomcat and Pentaho servers.

Results

Tomcat can now properly connect to your data.

Install JDBC driver as a module in JBoss

If you installed the Pentaho Server using the manual installation method, then you specified data connections to your JBoss web application server. If you want to modify existing connections or add more connections, perform the following tasks.

  1. Create module file for Pentaho Repository database
  2. Create module file for HSQL database
  3. Create module file for H2 database
  4. Define JNDI database connection information in JBoss
  5. Remove JNDI resource references in JBoss
  6. Update JNDI data source references to conform to JBoss standards

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.

The JDBC drivers reference has a list of supported drivers.

Create module file for Pentaho Repository database

You need to create a file for the database that hosts the Pentaho Repository (either PostgreSQL, MySQL, or Oracle), as well as for HSQLDB.

Procedure

  1. Locate the pentaho/server/pentaho-server/<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 Pentaho Repository.

    • PostgreSQL: postgresql/main
    • MySQL: mysql/main
    • Oracle: oracle/main
    • MS SQL Server: sqlserver/main
  2. Create these two paths in the same directory.

    • HSQLDB: hsqldb/main
    • H2: h2/main
  3. Download the supported JDBC driver for your Pentaho Repository database to the directory that you just created. The JDBC Drivers Reference has a list of supported drivers.

  4. Within that directory, do the following things:

    1. Use an editor to create a text file named module.xml.

    2. Copy the appropriate code into the module.xml file, then modify it so that the name of the JDBC driver you just downloaded appears.

      Repository TypeModule Code
      PostgreSQL
      <?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>7
          </module>
      MySQL
      <?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
      <?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>
      MS SQL Server
      <?xml version="1.0" encoding="UTF-8"?>
          <module xmlns="urn:jboss:module:1.0"name="org.sqlserver">
              <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.

Create module file for HSQL database

You will need to create a module file for the HSQL database.
CautionThe version of HSQLDB used should be 2.3.2.

Procedure

  1. Download the supported JDBC driver for HSQLDB and place it in the hsqldb/main directory.

  2. In the hsqldb/main directory, create a text file named module.xml.

  3. Copy this code into 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="org.hsqldb">
            <resources>
                <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
            </resources>
            <dependencies><module name="javax.api"/></dependencies>
        </module>
  4. Save and close the module.xml file.

Create module file for H2 database

You need to create a module file for the H2 database.

Procedure

  1. In the h2/main directory, create a text file named module.xml.

  2. Copy this code into 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="org.h2">
            <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 Pentaho Repository database. This section shows you how to define your JNDI database connection information.
NoteIf you have a different database than PostgreSQL, or if you are using a different port, password, user, driver class information, or IP address, make sure that you adjust the examples in this section to match the ones in your environment.

Procedure

  1. Copy the pentaho-style.war and pentaho.war files into the pentaho/server/pentaho-server/your jboss installation directory/standalone/deployment directory, or verify that the files are already there, as in PostgreSQL.

  2. Locate the pentaho/server/pentaho-server/<your jboss installation directory>/standalone/configuration/standalone.xml file and open it with a text editor.

  3. Insert these lines after the definition for 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="pentahooperationsmartpool" 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/PDI_Operations_Mart"pool-name="PDI_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>hibuser</user-name>
                                 <password>password</password>
                             </security>
                    </datasource>
  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 another database, 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>
    <driver name="org.hsqldb" module="org.hsqldb">
    <driver-class>org.hsqldb.jdbcDriver</driver-class>
    </driver>
  5. Save and close the standalone.xml file.

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 Pentaho Server to operate properly.

Procedure

  1. Navigate to the pentaho/server/pentao-server/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 prompt appears, confirm that you would like to update the file.

Update JNDI data source references to conform to JBoss standards

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

Procedure

  1. Use a text editor to open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file. Change the org.quartz.dataSource.myDS.jndiURL value to jboss/datasources/Quartz, then save and close the file.

  2. Use a text editor to open the pentaho/server/pentaho-server/pentaho-solutions/system/audit_sql.xml file. Change the JNDI value to jboss/datasources/Hibernate, then save and close the file.

  3. Use a text editor to open the pentaho/server/pentaho-server/pentaho-solutions/system/data-access/settings.xml file. Change the data-access-staging-jndi value to jboss/datasources/Hibernate, then save and close the file.

  4. Open the pentaho/server/pentaho-server/pentaho-solutions/system/audit/dialects/h2 directory.

    Make sure you open the file from the correct folder.
  5. Use the text editor to open each file in the H2 directory and make the following changes:

    1. Change <database>Audit</database> to <database>jboss/datasources/Audit</database>.

    2. Change <database>Hibernate</database> to <database>jboss/datasources/Hibernate</database>.