Ctrlk
Try Pentaho! - Start your 30 day evaluation today
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Step 2: Set up the Pentaho Repository

The Pentaho Repository is hosted on the database you installed to prepare your environment. The repository includes the following components:

  • Jackrabbit Contains the solution repository, examples, security data, and report content.

  • Quartz Holds data related to scheduling reports and jobs.

  • Hibernate Holds data related to audit logging.

  • (Optional) Pentaho Operations Mart Reports on system usage and performance.

Set up the Pentaho Repository by completing the tasks for the database you are using:

PostgreSQL

Install PostgreSQL as the host database for the Pentaho Repository in an archive installation by completing the following tasks, in order:

Important: Before you prepare your Pentaho Repository, you must complete Step 1: Prepare your environment.

1

Initialize the PostgreSQL Pentaho Repository database

To initialize PostgreSQL so it serves as the Pentaho Repository, run SQL scripts to create to create the Hibernate, Quartz, Jackrabbit (JCR), and (Optional) Pentaho Operations Mart components.

Important:

  • Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations might cause the scripts to fail.

  • If you use a different password or user, update the examples to match.

  • Review Quartz database information:

    • Pentaho 10.2.0.0 and earlier use Quartz 1.x. Quartz 1.x uses a QRTZ5_ prefix in the database.

    • Pentaho 10.2.0.1 and later use Quartz 2.x. Quartz 2.x uses a QRTZ6_ prefix in the database.

    • When upgrading from an earlier version to Pentaho 10.2.0.1 and later, create the QRTZ6_ tables by repeating the Quartz database creation procedure. You can optionally migrate existing Quartz schedules using the migration script. During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data. However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run, and data might be deleted.

    • Always back up your data before proceeding.

    • If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will also see this exception message in catalina.log:

Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

Note: If you are evaluating Pentaho, you might want to skip this step.

  1. Browse to the <your pentaho directory>/pentaho-server/data/postgresql folder.

  2. Use a text editor to modify these create scripts as needed for your user, password, database, and other values:

    • create_jcr_postgresql.sql

    • create_quartz_postgresql.sql

    • create_repository_postgresql.sql

    • pentaho_mart_postgresql.sql

  3. Save and close the files.

Run SQL scripts

To use PostgreSQL as the database for your Pentaho Repository, you must initialize the Jackrabbit, Quartz, Hibernate, and Operations Mart components. You can run SQL scripts developed by Pentaho to create and initialize these components.

When upgrading from earlier Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database. This creates a new Quartz library in the repository database. Back up your data before proceeding.

If you want to keep your existing Quartz library data, you can migrate the current tables to the new tables.

Note: You may need administrator permissions to run these scripts on the host OS server.

  1. Back up your data. See step 3, Back up your existing Pentaho products and install Pentaho 11.0, in the Upgrade an archive installation section.

  2. If Pentaho Server is running, stop it. See Stop and start the Pentaho Server and repository.

  3. Ensure your PostgreSQL instance is running.

  4. Open a PSQL console window and run these SQL creation scripts in this order:

    1. <your pentaho directory>/pentaho-server/data/postgresql/create_quartz_postgresql.sql

    2. <your pentaho directory>/pentaho-server/data/postgresql/create_jcr_postgresql.sql

    3. <your pentaho directory>/pentaho-server/data/postgresql/create_repository_postgresql.sql (initializes Hibernate)

    4. <your pentaho directory>/pentaho-server/data/postgresql/pentaho_mart_postgresql.sql

    The Quartz database, Jackrabbit and PostgreSQL repositories, and Pentaho Operations Mart are created. The new Quartz scheduler tables prefixed with QRTZ6_ are created.

    Any existing QRTZ5_ scheduler database is retained. Starting the Pentaho Server at this point results in an empty schedule.

  5. If you want to retain your existing scheduler database, open migrate_old_quartz_data_postgresql.sql in a text editor.

  6. Update the migration script as needed for your user, password, database, and other values.

  7. Run the migration script:

    <your pentaho directory>/pentaho-server/data/postgresql/migrate_old_quartz_data_postgresql.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Notes:

  • The original QRTZ5_ data is retained. You can delete it after you confirm the migration completed successfully.

  • You unpacked the Pentaho Operations Mart SQL file while preparing your environment for archive installation.

Verify PostgreSQL initialization

Note: Unless you changed it in Step 1, the default password for each username is password.

After you run the scripts, verify the databases and user roles were created correctly:

  1. Open pgAdminIII or a similar client tool.

  2. Log in as hibuser.

  3. Verify that you can see the tables under hibernate.

  4. If you installed Pentaho Operations Mart, verify that you can see the tables under pentaho_operations_mart.

  5. Log in as jcr_user.

  6. Verify that you can see the tables under jackrabbit.

    The Jackrabbit tables might not appear until you start Pentaho for the first time.

  7. Log in as pentaho_user.

  8. Verify that you can see the tables under quartz.

  9. Exit from the tool.

You have initialized the PostgreSQL Pentaho Repository database.

2

Configure the PostgreSQL Pentaho Repository database

After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for PostgreSQL.

Note: PostgreSQL is configured by default.

If you kept the default passwords and port, you can skip to Perform Tomcat-specific connection tasks.

By default, the examples in this section use a PostgreSQL database running on port 5432 with the default password.

Important: If you use a different port or password, you must update the examples to match.

Set up Quartz on PostgreSQL

Event information, such as scheduled reports, is stored in the Quartz JobStore.

During installation, specify where the JobStore is located by modifying quartz.properties.

  1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties in a text editor.

  2. In the #_replace_jobstore_properties section, set org.quartz.jobStore.driverDelegateClass:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

  3. Save and close the file.

Set Hibernate settings for PostgreSQL

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file.

The Hibernate configuration file specifies driver and connection information, dialects, and connection close and timeout behavior.

Note: Pentaho Server also stores audit logs in the Hibernate database.

The files in this section are located in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

  1. Open hibernate-settings.xml in a text editor.

  2. Find the <config-file> tags and confirm this value is set for PostgreSQL:

    <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

  3. Save and close the file if you made changes.

Modify Jackrabbit repository information for PostgreSQL

Edit repository.xml to use PostgreSQL for the default Jackrabbit repository.

  1. Go to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.

  2. Open repository.xml in a text editor.

  3. Ensure the PostgreSQL lines are not commented out.

  4. Ensure the MySQL, Oracle, and MS SQL Server lines are commented out.

    Item
    Code Section

    Repository

    DataStore

    Workspaces

    PersistenceManager (1st part)

    Versioning

    PersistenceManager (2nd part)

    DatabaseJournal

3

Perform Tomcat-specific connection tasks

After you configure the repository, configure your web application servers to connect to it.

In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, Pentaho Server is configured to run on Tomcat.

Connections are already specified.

Modify Tomcat context.xml only if you changed the default ports or passwords.

Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you must download and copy a JDBC driver to the correct locations.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers.

You must download and install the file yourself.

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

    See JDBC drivers reference for supported drivers.

  2. Copy the JDBC driver JAR to pentaho/server/pentaho-server/tomcat/lib.

  3. If you want to keep the Pentaho sample, copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib.

Modify JDBC connection information in the Tomcat XML file

Database connection and network information is stored in context.xml.

Modify context.xml to match your environment.

Important: If you use a different port, password, user, driver class, or host name, update the examples to match.

  1. Consult your database documentation to determine the JDBC class name and connection string.

  2. Go to server/pentaho-server/tomcat/webapps/pentaho/META-INF.

  3. Open context.xml in a text editor.

  4. Add the following code if it does not already exist:

  5. Update the user name, password, driver class, host name, and port values for your environment.

  6. Comment out any resource references that refer to other databases.

  7. Set validationQuery to select 1.

  8. Save and close context.xml.

4

Start the Pentaho Server

After you finish setting up the Pentaho Repository, go to Step 3: Start Pentaho Server and install licenses and client tools.

MySQL or MariaDB

Install MySQL or MariaDB as the host database for the Pentaho Server in an archive installation by completing the following tasks, in order:

Important:

  • Before you prepare your Pentaho Repository, you must complete Step 1: Prepare your environment.

  • MySQL Connector/J 8.0 cannot be used when configuring Tomcat with the JDBC driver JAR.

1

Initialize the MySQL or MariaDB repository database

To initialize MySQL or MariaDB so that it serves as the Pentaho Repository, you must run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

Important:

  • Use the ASCII character set when you run the scripts to initialize the repository database. Do not use UTF-8. UTF-8 can trigger string-length limits that can make scripts fail.

  • If your user, password, host, or port differs, you must update the examples to match your environment.

  • Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with the QRTZ5_ prefix.

  • Pentaho 10.2.0.1 and later use Quartz 2.x tables with the QRTZ6_ prefix. When upgrading to 10.2.0.1 or later, you must create the QRTZ6_ tables by re-running the Quartz database creation step. You can then optionally migrate schedules using the provided migration script.

  • Back up your data before you run any SQL scripts.

  • Failure to complete the Quartz upgrade results in a Pentaho Server start-up error. In addition to the server error, the following exception message is generated in the catalina.log file:

Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

If you are evaluating Pentaho, you can skip this step.

  1. Browse to the <your pentaho directory>/pentaho-server/data/mysql folder.

  2. Use a text editor to update these scripts as needed for your existing user, password, database, and other values:

    • create_jcr_mysql.sql

    • create_quartz_mysql.sql

    • create_repository_mysql.sql

    • pentaho_mart_mysql.sql

  3. Save and close the files.

Run SQL scripts

When upgrading from previous Pentaho versions (including 10.2.0.0 GA to 10.2.0.1 and later), you must manually initialize a new Quartz database. This creates a new Quartz library in the repository database.

If you want to keep existing Quartz schedules, migrate the old tables to the new tables.

You may need administrator permissions to run these scripts on the host OS.

  1. Back up your data. See step 3, Back up your existing Pentaho products and install Pentaho 11.0, in the Upgrade an archive installation section.

  2. If the Pentaho Server is running, stop it. See Stop and start the Pentaho Server and repository.

  3. Make sure your MySQL or MariaDB instance is running.

  4. Open MySQL Workbench or a MySQL/MariaDB command prompt.

  5. Run the SQL creation scripts in this order:

    1. <your pentaho directory>/pentaho-server/data/mysql/create_jcr_mysql.sql

    2. <your pentaho directory>/pentaho-server/data/mysql/create_quartz_mysql.sql

    3. <your pentaho directory>/pentaho-server/data/mysql/create_repository_mysql.sql

    4. <your pentaho directory>/pentaho-server/data/mysql/pentaho_mart_mysql.sql

    This creates the Quartz database, the Jackrabbit and MySQL/MariaDB repositories, and Pentaho Operations Mart.

    The scripts create the new Quartz scheduler tables prefixed with QRTZ6_.

    Any existing QRTZ5_ tables are retained. Starting the Pentaho Server at this point results in an empty schedule.

  6. If you want to retain your existing scheduler data, open migrate_old_quartz_data_mysql.sql in a text editor.

  7. Update the migration script for your user, password, database, and other values. Save the file.

  8. Run the migration script:

    <your pentaho directory>/pentaho-server/data/mysql/migrate_old_quartz_data_mysql.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Notes:

  • The original QRTZ5_ data is retained. You can delete it after you confirm migration success.

  • You unpacked the Pentaho Operations Mart SQL file while preparing your environment for the archive installation.

Verify MySQL or MariaDB initialization

Unless you changed it in Step 1, the default password for each username below is password.

After you run the scripts, verify the databases and user roles were created properly:

  1. Open MySQL Workbench.

  2. Log in as hibuser.

  3. Under Schemas, expand hibernate > Tables.

  4. If you installed Pentaho Operations Mart, expand pentaho_operations_mart > Tables.

  5. Log in as jcr_user.

  6. Under Schemas, expand jackrabbit > Tables.

    The Jackrabbit tables might not appear until after you start Pentaho for the first time.

  7. Log in as pentaho_user.

  8. Under Schemas, expand quartz > Tables.

  9. Exit MySQL Workbench.

You have initialized the MySQL or MariaDB Pentaho Repository database.

2

Configure the MySQL or MariaDB repository database

Now that you have initialized your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for MySQL or MariaDB.

The examples assume MySQL on port 3306 with default passwords.

If your port or password differs, update the examples to match your environment.

Set up Quartz on MySQL or MariaDB

Quartz stores scheduler data, such as scheduled reports.

You point Pentaho at the correct JobStore by updating quartz.properties.

  1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties.

  2. In #_replace_jobstore_properties, set org.quartz.jobStore.driverDelegateClass:

  3. Save the file.

Set Hibernate settings for MySQL or MariaDB

Hibernate settings point Pentaho to the correct Hibernate configuration file.

Note: Hibernate also stores the audit logs used by Operations Mart.

Files are in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

  1. Open hibernate-settings.xml.

  2. Find the <config-file> entry. Replace postgresql.hibernate.cfg.xml with mysql5.hibernate.cfg.xml:

    From:

    To:

  3. Save and close the file.

  4. (MariaDB only) Open mysql5.hibernate.cfg.xml.

  5. Find the driver class and change it from MySQL to MariaDB:

    From:

    To:

Replace the audit log file with the MySQL version

Replace audit_sql.xml with the MySQL version.

Use the same file for MariaDB.

  1. Locate pentaho-solutions/system/dialects/mysql5/audit_sql.xml.

  2. Copy it into pentaho-solutions/system.

Modify Jackrabbit repository information for MySQL or MariaDB

Update Jackrabbit to use the MySQL (or MariaDB) repository.

  1. Open pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml.

  2. Uncomment the MySQL lines.

  3. Comment out the MS SQL Server, Oracle, and PostgreSQL lines.

  4. Verify these sections use the MySQL values.

    Item
    Code Section

    Repository

    DataStore

    Workspaces

    PersistenceManager (1st part)

    Versioning

    PersistenceManager (2nd part)

    DatabaseJournal

3

Perform Tomcat-specific connection tasks

After your repository is configured, configure the web application server to connect to the Pentaho Repository.

In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, the Pentaho Server software is configured to deploy and run on Tomcat. Connections are already specified. Only update the Tomcat context.xml file if you changed default ports or passwords.1

Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, download and copy a JDBC driver to the right locations for the Pentaho Server and the web application server.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install these files yourself.

  1. Download a JDBC driver JAR from your database vendor or a third-party driver developer. The JDBC drivers reference lists supported drivers.

  2. Copy the JDBC driver JAR to the pentaho/server/pentaho-server/tomcat/lib folder.

  3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to keep the Pentaho sample.

Modify JDBC connection information in the Tomcat XML file

Database connection and network settings (username, password, driver class, IP address or domain name, and port numbers) for your Pentaho Repository database are stored in the context.xml file.

Update this file to match your environment.

Important: If you have a different port, password, user, driver class, or IP address, update the examples to match your environment.

  1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.

  2. Go to server/pentaho-server/tomcat/webapps/pentaho/META-INF and open context.xml in a text editor.

  3. Add the following code if it does not already exist.

    For MySQL:

    For MariaDB:

  4. Update usernames, passwords, driver classes, IP addresses (or domain names), and port numbers to match your environment.

  5. Comment out resource references for databases you are not using.

  6. Verify validationQuery is set to select 1 (for example: validationQuery="select 1").

  7. Save context.xml, then close it.

4

Start the Pentaho Server

After you finish setting up the Pentaho Repository, go to Step 3: Start Pentaho Server and install licenses and client tools.

Oracle

Install Oracle as the host database for the Pentaho Server in an archive installation by completing the following tasks, in order:

Important: Before you prepare your Pentaho Repository, you must complete Step 1: Prepare your environment.

1

Initialize Oracle Pentaho Repository database

To initialize Oracle so that it serves as the Pentaho Repository, you need to run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

Important:

  • Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations might cause the scripts to fail.

  • If you use a different password or user, update the examples to match.

  • Review Quartz database information:

    • Pentaho 10.2.0.0 and earlier use Quartz 1.x. Quartz 1.x uses a QRTZ5_ prefix in the database.

    • Pentaho 10.2.0.1 and later use Quartz 2.x. Quartz 2.x uses a QRTZ6_ prefix in the database.

    • When upgrading from an earlier version to Pentaho 10.2.0.1 and later, create the QRTZ6_ tables by repeating the Quartz database creation procedure. You can optionally migrate existing Quartz schedules using the migration script. During this upgrade procedure, executing the SQL create script resets the corresponding Quartz database without loss of the original data. However, the other associated repository databases in 10.2.0.1 will be reset by SQL scripts if run, and data might be deleted.

    • Always back up your data before proceeding.

    • If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will also see this exception message in catalina.log:

Change default passwords

For production systems, follow best practices and change the default passwords in the SQL scripts.

Note: If you are evaluating Pentaho, you can skip this step.

  1. Browse to the <your pentaho directory>/pentaho-server/data/oracle folder.

  2. Use a text editor to update the scripts as needed for your user, password, database, and other settings:

    • create_jcr_ora.sql

    • create_quartz_ora.sql

    • create_repository_ora.sql

    • pentaho_mart_oracle.sql

  3. Save and close the files.

Run SQL scripts

When upgrading from previous Pentaho versions, including 10.2.0.0 GA to 10.2.0.1 and later, you must manually initialize a new Quartz database. A new Quartz library is created in the repository database as a result.

Always follow best practices and back up your data before you proceed. If you want to keep existing Quartz schedules, you can migrate the existing tables to the new tables.

Note: You may need administrator permissions to run these scripts on the host OS server.

  1. Back up your data. See step 3, Back up your existing Pentaho products and install Pentaho 11.0, in the Upgrade an archive installation section.

  2. If the Pentaho Server is running, stop it. For instructions, see Stop and start the Pentaho Server and repository.

  3. Make sure that your Oracle instance is running.

  4. Open a Command Prompt or terminal window that runs SQL*Plus, then run the following scripts in this order:

    1. <your pentaho directory>/pentaho-server/data/oracle/create_jcr_ora.sql

    2. <your pentaho directory>/pentaho-server/data/oracle/create_quartz_ora.sql

    3. <your pentaho directory>/pentaho-server/data/oracle/create_repository_ora.sql

    4. <your pentaho directory>/pentaho-server/data/pentaho_mart_oracle.sql

    This creates the Quartz database, Jackrabbit repository, Hibernate repository, and Pentaho Operations Mart.

    The QRTZ6_ tables are created. Any existing QRTZ5_ tables remain. If you start the Pentaho Server now, the schedule is empty.

  5. If you want to retain your existing schedule data, open migrate_old_quartz_data_oracle.sql in a text editor.

  6. Update the script for your user, password, database, and other settings, then save it.

  7. Run the migration script:

    <your pentaho directory>/pentaho-server/data/oracle/migrate_old_quartz_data_oracle.sql

Notes:

  • The original QRTZ5_ data remains available. Delete it only after you verify a successful migration.

  • You unpacked the Pentaho Operations Mart SQL file while preparing your environment for the archive installation.

Verify Oracle initialization

Note: Unless you changed it in Step 1, the default password for each username below is password.

After you run the scripts, verify that databases and user roles were created correctly:

  1. Open a Terminal or Command Prompt window running SQL*Plus (or a similar client) and connect to the Oracle database.

  2. Log in as hibuser.

  3. Verify that you can see the tables under hibernate.

  4. If you installed Pentaho Operations Mart, verify that you can see the tables under pentaho_operations_mart.

  5. Log in as jcr_user.

  6. Verify that you can see the tables under jackrabbit.

    The Jackrabbit tables may not appear until you start Pentaho for the first time.

  7. Log in as pentaho_user.

  8. Verify that you can see the tables under quartz.

  9. Exit the tool.

You have initialized the Oracle Pentaho Repository database.

2

Configure Oracle Pentaho Repository database

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an Oracle database.

By default, the examples in this section are for an Oracle database that runs on port 1521. The default password is also in these examples.

Important: If you use a different port or password, you must update the examples to match your environment.

Set up Quartz on Oracle

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located by modifying the quartz.properties file.

  1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties file in any text editor.

  2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown:

  3. Save the file and close the text editor.

Set Hibernate settings for Oracle

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file. The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

Note: The Hibernate database is also where the Pentaho Server stores the audit logs that act as source data for the Pentaho Operations Mart.

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

  1. Open the hibernate-settings.xml file in a text editor. Find the tags and change postgresql.hibernate.cfg.xml to oracle10g.hibernate.cfg.xml as shown.

    From:

    To:

  2. Save and close the file.

Replace default version of audit log file with Oracle version

Since you are using Oracle to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for Oracle.

  1. Locate the pentaho-solutions/system/dialects/oracle10g/audit_sql.xml file.

  2. Copy it into the pentaho-solutions/system directory.

Modify Jackrabbit repository information for Oracle

Edit the following code to change the default Jackrabbit repository to Oracle.

  1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.

  2. As shown in the table below, locate and verify or change the code so that the Oracle lines are not commented out, but the MySQL, PostgreSQL, and MS SQL Server lines are commented out.

    Item
    Code Section

    Repository

    DataStore

    Workspaces

    PersistenceManager (1st part)

    Versioning

    PersistenceManager (2nd part)

    DatabaseJournal

3

Perform Tomcat-specific connection tasks

After your repository has been configured, you must configure the web application servers to connect to the Pentaho Repository. In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and the Tomcat context.xml file must be modified ONLY if you have changed the default ports or passwords.

Use these steps to set up the JDBC driver and update the Tomcat connection settings.

Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, you must download and copy a JDBC driver to the appropriate places for the Pentaho Server and the web application server.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.

  1. Download a JDBC Driver JAR from your database vendor or a third-party driver developer. The JDBC drivers reference has a list of supported drivers.

  2. Copy the JDBC driver JAR you downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.

  3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.

Modify JDBC connection information in the Tomcat 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 Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information for your operating environment.

Important: If you have a different port, password, user, driver class information, or IP address, you must change the values in these examples to match your environment.

  1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.

  2. Navigate to the server/pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any text editor.

  3. Add the following code to the file if it does not already exist. Replace XE in the URL setting with your schema name.

  4. Modify the username, password, driver class information, IP address (or domain name), and port numbers to match your environment.

  5. Comment out any resource references that refer to other databases.

  6. Verify that the validationQuery variable for your database is set to validationQuery=\"select 1 from dual\".

  7. Save the context.xml file, then close it.

4

Start the Pentaho Server

After you finish setting up the Pentaho Repository, go to Step 3: Start Pentaho Server and install licenses and client tools.

MS SQL Server

Install MS SQL Server as the host database for the Pentaho Server in an archive installation by completing the following tasks, in order:

Important: Before you prepare your Pentaho Repository, you must complete Step 1: Prepare your environment.

1

Initialize MS SQL Server Pentaho Repository database

To initialize MS SQL Server so it serves as the Pentaho Repository, run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

Important:

  • Use the ASCII character set when you run these scripts. Do not use UTF-8. Text string length limitations can cause the scripts to fail.

  • If you have a different password or user, update the examples to match your configuration.

  • Pentaho 10.2.0.0 and earlier use the Quartz 1.x library, which uses a QRTZ5_ prefix in the database. Pentaho 10.2.0.1 and later use Quartz 2.x, which uses a QRTZ6_ prefix. When you upgrade from an earlier version, create the QRTZ6_ tables by repeating the Quartz database creation step. You can optionally migrate schedules with the migration script. Always back up your data before you proceed.

  • If you do not complete the Quartz upgrade, Pentaho Server fails at startup. You will see a “Missing Quartz library database error” exception in catalina.log.

Adjust MS SQL Server configuration settings

Configure these MS SQL Server settings in Microsoft SQL Server Management Studio (or a similar tool).

  1. Select SQL Server and Windows Authentication Mode to use mixed authentication.

  2. Enable TCP/IP for MS SQL Server.

  3. Make sure MS SQL Server listens on an external IP, not localhost.

Change default passwords

For production, follow best practices to change the default passwords in the SQL scripts.

Note: If you are evaluating Pentaho, you may want to skip this step.

  1. Browse to the <your pentaho directory>/pentaho-server/data/sqlserver folder.

  2. Modify these create scripts as needed for your existing setup (user, password, database, and other required information):

    • create_jcr_sqlServer.sql

    • create_quartz_sqlServer.sql

    • create_repository_sqlServer.sql

    • pentaho_mart_sqlserver.sql

  3. Save and close the files.

Run SQL scripts

When upgrading from previous Pentaho versions (including 10.2.0.0 GA to 10.2.0.1 and later), manually initialize a new Quartz database. This creates a new Quartz library in the repository database. Back up your data before you proceed.

Run the SQL creation scripts.

Note: You may need administrator permissions to run these scripts on the host OS server.

  1. Back up your data. See step 3, Back up your existing Pentaho products and install Pentaho 11.0, in the Upgrade an archive installation section.

  2. If the Pentaho Server is running, stop it. For instructions, see Stop and start the Pentaho Server and repository.

  3. Make sure your MS SQL Server instance is running.

  4. Open a sqlcmd utility window, or run these scripts from Microsoft SQL Server Management Studio, in this order:

    1. <your pentaho directory>/pentaho-server/data/sqlserver/create_jcr_sqlServer.sql

    2. <your pentaho directory>/pentaho-server/data/sqlserver/create_quartz_sqlServer.sql

    3. <your pentaho directory>/pentaho-server/data/sqlserver/create_repository_sqlServer.sql

    4. <your pentaho directory>/pentaho-server/data/sqlserver/pentaho_mart_sqlserver.sql

    The Quartz database, Jackrabbit and MS SQL Server repositories, and Pentaho Operations Mart are created. The new Quartz scheduler table prefixed with QRTZ6_ is created. Any existing QRTZ5_ scheduler database is retained. Starting the Pentaho Server at this point results in an empty schedule.

  5. If you want to retain your existing scheduler database, open the migrate_old_quartz_data_sqlserver.sql file.

  6. Modify the migration script as needed for your existing setup (user, password, database, and other required information), then save it.

  7. Run the migration script: <your pentaho directory>/pentaho-server/data/sqlserver/migrate_old_quartz_data_sqlserver.sql

The original Quartz scheduler database is migrated to the new Quartz database.

Notes:

  • The original QRTZ5_ data is retained. You can delete it after you confirm migration is successful.

  • You unpacked the Pentaho Operations Mart SQL file while preparing your environment.

Verify MS SQL Server initialization

Note: Unless you changed it in Step 1, the default password for each username below is password.

After you run the scripts, verify that the databases and user roles were created properly:

  1. Open MS SQL Server Management Studio.

  2. Log in as hibuser.

  3. In Object Explorer, navigate to hibernate and verify that you can see the tables.

  4. If you installed Pentaho Operations Mart, log in as pentaho_operations_mart.

  5. In Object Explorer, navigate to pentaho_operations_mart and verify that you can see the tables.

  6. Log in as jcr_user.

  7. In Object Explorer, navigate to jackrabbit and verify that you can see the tables. The Jackrabbit tables may not appear until you start Pentaho for the first time.

  8. Log in as pentaho_user.

  9. In Object Explorer, navigate to quartz and verify that you can see the tables.

  10. Exit MS SQL Server Management Studio.

2

Configure MS SQL Server Pentaho Repository database

After you initialize your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for an MS SQL Server database.

By default, the examples in this section assume MS SQL Server running on port 1433. They also use the default password.

Important: If you use a different port or password, update the examples to match your configuration.

Set up Quartz on MS SQL Server

Event information, such as scheduled reports, is stored in the Quartz JobStore. During installation, indicate where the JobStore is located by modifying quartz.properties.

  1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties.

  2. In the #_replace_jobstore_properties section, set org.quartz.jobStore.driverDelegateClass:

  3. Save the file.

Set Hibernate settings for MS SQL Server

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository Hibernate configuration file.

Note: The Hibernate database is also where Pentaho Server stores the audit logs used as source data for Pentaho Operations Mart.

The files in this section are located in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

  1. Open hibernate-settings.xml.

  2. Find the <config-file> tags. Change postgresql.hibernate.cfg.xml to sqlserver.hibernate.cfg.xml.

    From:

    To:

  3. Save and close the file.

Replace default version of audit log file with MS SQL Server version

Since you are using MS SQL Server to host the Pentaho Repository, replace audit_sql.xml with the MS SQL Server version.

  1. Locate pentaho-solutions/system/dialects/sqlserver/audit_sql.xml.

  2. Copy it into the pentaho-solutions/system directory.

Modify Jackrabbit repository information for MS SQL Server

Edit repository.xml to change the default Jackrabbit repository to MS SQL Server.

  1. Navigate to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.

  2. Open repository.xml.

  3. Locate and verify or change the configuration so that the MS SQL Server lines are uncommented, and the MySQL, Oracle, and PostgreSQL lines are commented out.

    Item
    Code Section

    Repository

3

Perform Tomcat-specific connection tasks

After your repository is configured, configure your web application server to connect to the Pentaho Repository.

In this step, you make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Note: By default, Pentaho Server is configured to be deployed and run on Tomcat. Connections are already specified. Modify Tomcat context.xml only if you changed the default ports or passwords.

Download driver and apply to the Pentaho Server

To connect to a database (including the Pentaho Repository database), download and copy a JDBC driver to the Pentaho Server and the web application server.

Note: Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install the file yourself.

  1. Download a JDBC driver .jar from your database vendor or a third-party driver developer. See JDBC drivers reference.

  2. Copy the JDBC driver .jar to pentaho/server/pentaho-server/tomcat/lib.

  3. Copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib if you want to retain the Pentaho sample data.

Modify JDBC connection information in the Tomcat XML file

Database connection and network information (such as user name, password, driver class, host, and port) is stored in context.xml. Modify it to match your environment.

Important: If you use a different user name, password, or port, update the examples to match your environment.

  1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.

  2. Navigate to server/pentaho-server/tomcat/webapps/pentaho/META-INF.

  3. Open context.xml.

  4. Add the following resources if they do not already exist:

    Note: Depending on your MS SQL Server configuration, you may be able to set trustServerCertificate=true. See Connecting with encryption.

  5. Modify user names, passwords, driver class information, host, and port to match your environment.

  6. Comment out any resource references that refer to other databases.

  7. Verify your database validation query is set to validationQuery=\"select 1\".

  8. Save and close context.xml.

4

Start the Pentaho Server

After you finish setting up the Pentaho Repository, go to Step 3: Start Pentaho Server and install licenses and client tools.

PreviousStep 1: Prepare your environment
NextStep 3: Start Pentaho Server and install licenses and client tools

Last updated

Was this helpful?