Manual installation process

Manual installation deploys Pentaho Server to your own supported web application server (for example, Tomcat).

If you prefer an installation with a bundled web application server, see Archive installation.

Review Before you begin before you start.

Process overview

1. Prepare your OS environment.

2. Prepare your Pentaho Repository database.

3. Configure and start Pentaho Server. Install licenses.

Prepare your Windows environment for a manual installation

Prepare your Windows environment for a manual installation. Manual installation deploys Pentaho Server to your own web application server.

If you want Pentaho to provide the web application server, see Archive installation.

Process overview

Complete these tasks to prepare a Windows environment for a manual installation of Pentaho Server:

1. Create Windows directory structure.

2. Install Java.

3. Install your web application server.

4. Install the database that will host the Pentaho Repository.

5. Download and unpack the installation files.

6. Set environment variables.

7. Prepare your Pentaho Repository.

If you already installed a supported web application server, skip “Install your web application server.”

Create Windows directory structure

Choose an installation path for Pentaho on Windows. Pentaho recommends this directory path:

pentaho\\server\\pentaho-server

1. Sign in to the machine where you will run Pentaho Server.

2. Create the directory path: pentaho\server\pentaho-server.

3. Verify you have permission to read, write, and run commands in these directories.

4. Verify users can write to the directory where you install Pentaho.

Install Java

Pentaho requires a supported Java version.

1. Check Components Reference for supported Java versions.

2. Download a supported JRE or JDK from the Oracle website and install it.

3. Set JAVA_HOME to your Java install directory.

Example:

set JAVA_HOME="C:\Program Files\Java\jdk11.x.x.x"

Install your web application server

If you want to deploy Pentaho Server to your own Tomcat web application server, install Tomcat first.

1. Check Components Reference for supported Tomcat versions.

2. Install the web application server by following the vendor’s documentation.

Pentaho recommends installing Tomcat in pentaho\server\pentaho-server.

1. Start the web application server and verify the default page loads.

2. Stop the web application server.

Install the Pentaho Repository host database

The Pentaho Repository stores data used for scheduling and security. It also stores metadata and models for reports.

1. Choose and install a database to host the Pentaho Repository.

Pentaho supports:

• PostgreSQL

• MySQL

• MariaDB

• Oracle

• MS SQL Server

1. Check Components Reference for supported database versions.

2. Install the database.

3. Verify the database starts and you can connect to it.

Download and unpack installation files

Download the compressed installation files. Then unpack them into the correct directories.

Download files

1. Sign in to the Support Portal using the credentials from your Pentaho Welcome Packet.

2. In the Pentaho card, select Download.

3. On the 11.x page, select Pentaho 11.0 GA Release.

You might need to select See all <number> articles to see the full list of 11.x downloads.

1. Scroll to the bottom of the Pentaho 11.0 GA Release page.

2. In the file component section, go to Pentaho Server\Manual Build (No Web Application Server).

3. Download pentaho-server-manual-ee-11.0.0.0-<build number>.zip.

4. In the file component section, go back to 11.0.0.0-GA.

5. Open the Operations Mart folder.

6. Download pentaho-operations-mart-11.0.0.0-<build number>.zip.

Unpack installation files

1. Locate the two files you downloaded:

   1. pentaho-server-manual-ee-11.0.0.0-<build number>.zip

   2. pentaho-operations-mart-11.0.0.0-<build number>.zip

2. Unpack pentaho-server-manual-ee-11.0.0.0-<build number>.zip. Move the contents to \pentaho\server\pentaho-server.

3. Unpack pentaho-operations-mart-11.0.0.0-<build number>.zip. Move the contents to \pentaho\server\pentaho-server.

4. In \pentaho-server, open \pentaho-server-manual-ee.

5. Unpack these ZIP files and move the contents to the correct directories:

File to unpack	Directory where you move the file contents
license-installer.zip	pentaho\server
jdbc-distribution-utility.zip	pentaho\server
pentaho-data.zip	pentaho\server\pentaho-server
pentaho-solutions.zip	pentaho\server\pentaho-server

1. Copy the .war and .html files to these directories:

File	Directories that you copy the file into
pentaho.war	pentaho\server\pentaho-server\<tomcat installation directory>\webapps
pentaho-style.war	pentaho\server\pentaho-server\<tomcat installation directory>\webapps
PentahoServer_OSS_Licenses.html	pentaho\server\pentaho-server

Verify directory structure

1. Verify the files are placed correctly. Your pentaho directory should include:

   • pentaho\server\pentaho-server\

   • pentaho\server\pentaho-server\data\<database name>

2. If your web application server is installed outside pentaho\server\pentaho-server, verify:

   • pentaho.war is in your web application server’s webapps directory.

   • pentaho-style.war is in your web application server’s webapps directory.

Set environment variables

If you do not set the required environment variables, Pentaho might not start.

1. Set PENTAHO_JAVA_HOME to your Java installation directory:

SET PENTAHO_JAVA_HOME=C:\Program Files\Java\jdk11.x.x.x

1. (Optional) If you are using a JRE, also set JRE_HOME.

2. Sign out and sign in again. Then verify the variables are set:

ECHO %PENTAHO_JAVA_HOME%

Prepare your Pentaho Repository

Continue in one of these sections:

• Use PostgreSQL as your repository database (manual installation)

• Use MySQL or MariaDB as your repository database (manual installation)

• Use Oracle as your repository database (manual installation)

• Use MS SQL Server as your repository database (manual installation)

Prepare your Linux environment for a manual installation

This guide helps you prepare your Linux environment for a manual installation. Manual installation deploys the Pentaho Server to your own supported web application server (for example, Tomcat).

If you prefer an installation that includes a bundled web application server, see Archive installation.

Process overview

If you already installed a supported web application server, skip Install your web application server.

1. Create the Pentaho user

2. Create Linux directory structure

3. Install Java

4. Install your web application server

5. Install the Pentaho Repository host database

6. Download and unpack installation files

7. Set environment variables

8. Advanced Linux considerations

9. Prepare your Pentaho Repository

Create the Pentaho user

Create a user account with administrative privileges. You will use it for the remaining steps.

1. Create an administrative user on the server and name it pentaho.

2. Verify the pentaho user can read, write, and execute in its home directory.

3. Verify users can write to the directory where you install Pentaho.

Create Linux directory structure

Choose an installation path. Pentaho recommends this directory structure.

1. Sign in to the machine where you will run the Pentaho Server.

2. Make sure you are signed in as the pentaho user.

3. Create these directories under the pentaho user’s home directory:

<your home directory>/pentaho/server/pentaho-server
<your home directory>/.pentaho

1. Verify you have read, write, and execute permissions for the directories you created.

Install Java

Pentaho requires a supported Java version.

1. Check Components Reference for the supported Java version.

2. Download and install the supported JRE or JDK.

3. Set JAVA_HOME to your Java installation directory.

Example:

export JAVA_HOME=/usr/lib/jvm/jdk11.x.x.x

Install your web application server

If you plan to deploy Pentaho on your own Tomcat server, install Tomcat first.

If you already have a supported Tomcat installed, skip this section.

1. Check Components Reference for the supported Tomcat version.

2. Install your web application server, following its vendor documentation.

3. Start the web application server and verify you can reach the default page.

4. Stop the web application server.

Install the Pentaho Repository host database

The Pentaho Repository stores data used for scheduling and security. It also stores metadata and models for reports.

1. Choose a supported database type:

   • PostgreSQL

   • MySQL

   • MariaDB

   • Oracle

   • MS SQL Server

2. Check Components Reference for supported database versions.

3. Install the database you selected.

4. Verify the database is installed and running.

Download and unpack installation files

Pentaho Server and Operations Mart are delivered as compressed files. Download them, unpack them, then copy the contents to the correct directories.

Download files

1. Sign in to the Support Portal using the credentials in your Pentaho Welcome Packet.

2. In the Pentaho card, select Download.

3. In the 11.x list, select Pentaho 11.0 GA Release.

You might need to select See all <number> articles to see all downloads.

1. In Pentaho Server/Manual Build (No Web Application Server), download:

   • pentaho-server-manual-ee-11.0.0.0-<build number>.zip

2. In Operations Mart, download:

   • pentaho-operations-mart-11.0.0.0-<build number>.zip

Unpack installation files

1. Unpack pentaho-server-manual-ee-11.0.0.0-<build number>.zip.

2. Move its contents to:

   • /pentaho/server/pentaho-server

3. Unpack pentaho-operations-mart-11.0.0.0-<build number>.zip.

4. Move its contents to:

   • /pentaho/server/pentaho-server

5. In /pentaho-server, go to /pentaho-server-manual-ee.

6. Unpack these ZIP files and move their contents as shown:

   • license-installer.zip → pentaho/server

   • jdbc-distribution-utility.zip → pentaho/server

   • pentaho-data.zip → pentaho/server/pentaho-server

   • pentaho-solutions.zip → pentaho/server/pentaho-server

7. Copy these files to your web application server:

   • pentaho.war → pentaho/server/pentaho-server/<tomcat installation directory>/webapps

   • pentaho-style.war → pentaho/server/pentaho-server/<tomcat installation directory>/webapps

   • PentahoServer_OSS_Licenses.html → pentaho/server/pentaho-server

Verify directory structure

1. Verify these directories exist:

   • pentaho/server/pentaho-server/

   • pentaho/server/pentaho-server/data/<database name>

2. If your web application server is not under pentaho/server/pentaho-server, verify you copied the WAR files to your chosen <tomcat installation directory>/webapps directory.

Set environment variables

If you do not set the required *_HOME environment variables, Pentaho may not start correctly.

1. Set PENTAHO_JAVA_HOME to your Java installation path.

Example:

export PENTAHO_JAVA_HOME=/usr/lib/jvm/java-11-oracle

1. (Optional) If you are using a JRE, also set JRE_HOME.

2. Sign out and sign back in, then verify the variables are set.

Advanced Linux considerations

Some headless server scenarios need extra configuration:

• A system with no video card

• A system with a video card but no X server installed

In some environments (especially without a video card), you might need both procedures to generate reports.

Systems without video cards

Add -Djava.awt.headless=true to CATALINA_OPTS in your application server startup scripts.

Startup can fail if any environment variable value is invalid. Double-check settings after edits.

Example:

export CATALINA_OPTS="-Djava.awt.headless=true -Xms4096m -Xmx6144m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000"

If you create a service control script, add the same parameter to its CATALINA_OPTS.

Systems without X11

Chart generation requires X11 functions.

If you cannot install an X server, install Xvfb instead. Xvfb emulates X11 in memory.

Install Xvfb using your operating system’s package manager.

Prepare your Pentaho Repository

Continue in one of these sections:

• Use PostgreSQL as your repository database (manual installation)

• Use MySQL or MariaDB as your repository database (manual installation)

• Use Oracle as your repository database (manual installation)

• Use MS SQL Server as your repository database (manual installation)

Prepare your Pentaho Repository

The Pentaho Repository resides on the database you installed during environment setup.

It includes these components:

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

• Quartz Stores scheduler data for reports and jobs.

• Hibernate Stores audit logging data.

• (Optional) Pentaho Operations Mart Stores system usage and performance data.

If you use examples or scripts, update usernames, passwords, and ports to match your environment.

Use PostgreSQL as your repository database (manual installation)

Install PostgreSQL as the host database for the Pentaho Server repository.

Initialize the PostgreSQL Pentaho Repository database

Run PostgreSQL initialization scripts to create the Pentaho Repository schemas.

You create schemas for Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart.

Use an ASCII database encoding when you run these scripts. Do not use UTF-8. UTF-8 can cause script failures due to text length limits.

Quartz 1.x vs Quartz 2.x upgrade note (Pentaho 10.2.0.1+)

Pentaho 10.2.0.0 and earlier versions use Quartz 1.x tables with a QRTZ5_ prefix.

Pentaho 10.2.0.1 and later versions use Quartz 2.x tables with a QRTZ6_ prefix.

If you upgrade to Pentaho 10.2.0.1 or later, create the QRTZ6_ tables.

Optionally migrate your existing schedules using the provided migration script.

If you do not complete the Quartz upgrade, Pentaho Server can fail at startup.

You may see Missing Quartz library database error in catalina.log.

1

Step 1: 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 <your-pentaho-dir>/pentaho-server/data/postgresql.

2. Edit these scripts for your user, password, database, and any other required values:

   • create_jcr_postgresql.sql

   • create_quartz_postgresql.sql

   • create_repository_postgresql.sql

   • pentaho_mart_postgresql.sql

3. Save the files.

2

Step 2: Run SQL scripts

Run the PostgreSQL initialization scripts from the Pentaho software distribution.

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

If you are upgrading from earlier Pentaho versions (including 10.2.0.0 GA → 10.2.0.1+), you must create the new Quartz 2.x tables.

If you want to keep existing scheduler data, migrate QRTZ5_ data to the new QRTZ6_ tables.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

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

3. Make sure PostgreSQL is running.

4. Open a psql console and run these scripts in order:

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

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

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

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

This creates the Quartz database, Jackrabbit repository, PostgreSQL repository, and Operations Mart schema.

It also creates the new Quartz scheduler tables prefixed with QRTZ6_.

Any existing QRTZ5_ scheduler tables are retained.

1. If you want to retain existing scheduler data, open migrate_old_quartz_data_postgresql.sql.

2. Update the migration script for your current user, password, database, and any other required values.

3. Run the migration script:

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

The original QRTZ5_ data is retained.

Delete it after you confirm the migration is successful.

If you do not have pentaho_mart_postgresql.sql, make sure you unpacked the Pentaho Operations Mart SQL file during installation.

3

Step 3: Verify PostgreSQL initialization

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

1. Open pgAdmin (or a similar client).

2. Log in as hibuser, then verify you can see the tables under hibernate.

3. If you installed Operations Mart, verify you can see the tables under pentaho_operations_mart.

4. Log in as jcr_user, then verify you can see the tables under jackrabbit.

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

1. Log in as pentaho_user, then verify you can see the tables under quartz.

You have initialized the PostgreSQL Pentaho Repository database.

Configure the PostgreSQL Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart for PostgreSQL.

PostgreSQL is configured by default. If you kept the default password and port, skip this section and continue to Perform Tomcat-specific connection tasks.

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

Set up Quartz on PostgreSQL

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:

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

1. Save the file.

Set Hibernate settings for PostgreSQL

Hibernate settings point Pentaho to the correct Hibernate configuration file.

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, then confirm it uses the PostgreSQL config:

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

1. Save the file if you made changes.

Modify Jackrabbit repository information for PostgreSQL

Update Jackrabbit to use the PostgreSQL repository.

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

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

3. Verify these sections use the PostgreSQL values.

Repository

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>

DataStore

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="databaseType" value="postgresql"/>
  <param name="minRecordLength" value="1024"/>
  <param name="maxConnections" value="3"/>
  <param name="copyWhenReading" value="true"/>
  <param name="tablePrefix" value=""/>
  <param name="schemaObjectPrefix" value="ds_repos_"/>
</DataStore>

Workspaces

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>

PersistenceManager (workspaces)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

Versioning

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>

PersistenceManager (versioning)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.PostgreSQLPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>

DatabaseJournal

<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
  <param name="revision" value="${rep.home}/revision.log" />
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="postgresql"/>
  <param name="schemaObjectPrefix" value="cl_j_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>

Perform Tomcat-specific connection tasks

After you configure the Pentaho Repository, configure Tomcat to connect to it.

This step sets up JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Only edit Tomcat context.xml if you changed default ports or passwords.

1

Download the JDBC driver and apply it to Pentaho Server

To connect to a database, you must install a JDBC driver JAR.

Install it for Pentaho Server and Tomcat.

Pentaho cannot redistribute some third-party database drivers. Download the driver from your database vendor.

1. Download a JDBC driver JAR from your database vendor.

See JDBC drivers reference.

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

2. Optional: Copy hsqldb-2.3.2.jar to pentaho/server/pentaho-server/tomcat/lib.

Do this if you want to keep the Pentaho sample data.

2

Modify JDBC connection information in the Tomcat context.xml file

Database connection settings are stored in Tomcat context.xml.

Update the file to match your environment.

If your port, password, username, driver class, or host differs from the defaults, update the examples.

1. Consult your database documentation for:

   • JDBC driver class name

   • JDBC connection string

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml in a text editor.

3. Add the following XML if it does not already exist:

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
        validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="pentaho_user" password="password" testOnBorrow="true"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/quartz"
        validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="hibuser" password="password" driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://localhost:5432/hibernate?searchpath=pentaho_dilogs"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_user" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_admin" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="jcr_user" password="password"
        driverClassName="org.postgresql.Driver" url="jdbc:postgresql://localhost:5432/jackrabbit"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

1. Update the username, password, driver class, host, and port to match your environment.

2. Comment out any resources for databases you are not using.

3. Verify validationQuery="select 1".

4. Save the file.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.

Use MySQL or MariaDB as your repository database (manual installation)

Install MySQL or MariaDB as the host database for the Pentaho Server repository.

Initialize the MySQL or MariaDB Pentaho Repository database

Run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Operations Mart components.

Use the ASCII character set when you run these scripts. Do not use UTF-8. Script failures can occur due to string-length limits.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables (QRTZ5_). Pentaho 10.2.0.1 and later use Quartz 2.x tables (QRTZ6_).

When you upgrade to 10.2.0.1 or later, you must create the QRTZ6_ tables. You can optionally migrate existing schedules using the migration script.

If Quartz is not upgraded, the server can fail at startup. You may see Missing Quartz library database error in catalina.log.

Step 1: Change default passwords

For production, change the default passwords in the SQL scripts.

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

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

2. Update these scripts for your user, password, database, and other required values:

   • create_jcr_mysql.sql

   • create_quartz_mysql.sql

   • create_repository_mysql.sql

   • pentaho_mart_mysql.sql

3. Save and close the files.

Step 2: Run SQL scripts

When upgrading from older versions (including 10.2.0.0 GA), initialize a new Quartz database. This creates the Quartz 2.x tables.

You might need administrator permissions on the host OS.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

2. If the Pentaho Server is running, stop it.

See Stop and start the Pentaho Server and repository.

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

2. Open a MySQL/MariaDB prompt or MySQL Workbench. Then run the 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:

• Quartz scheduler tables prefixed with QRTZ6_

• Jackrabbit tables

• Repository tables

• (Optional) Operations Mart tables

Any existing QRTZ5_ tables are retained.

If you start the server now, the schedule is empty.

1. If you want to retain existing schedules, open migrate_old_quartz_data_mysql.sql.

2. Update the migration script as needed. Then save it.

3. Run:

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

The original QRTZ5_ tables are retained. Delete them only after you confirm migration success.

Step 3: Verify MySQL or MariaDB initialization

Unless you changed it in Step 1, the default password is password.

1. Open MySQL Workbench.

2. Log in as hibuser.

3. Under Schemas, expand hibernate > Tables. Verify you see tables.

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

5. Log in as jcr_user.

6. Under Schemas, expand jackrabbit > Tables. Verify tables.

Jackrabbit tables might not appear until the first server start.

1. Log in as pentaho_user.

2. Under Schemas, expand quartz > Tables. Verify tables.

3. Exit MySQL Workbench.

Configure the MySQL or MariaDB Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart.

The examples assume MySQL on port 3306 with default passwords.

If your ports or passwords differ, update the examples. Match your environment values.

Step 1: Set up Quartz on MySQL or MariaDB

Quartz stores scheduler event 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:

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

1. Save the file.

Step 2: Set Hibernate settings for MySQL or MariaDB

Hibernate settings point Pentaho to the correct Hibernate configuration file.

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:

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

To:

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

1. Save and close the file.

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

3. Change the driver class:

From:

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

To:

<property name="connection.driver_class">org.mariadb.jdbc.Driver</property>

Step 3: 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.

Step 4: 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.

Repository

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>

DataStore

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="databaseType" value="mysql"/>
  <param name="minRecordLength" value="1024"/>
  <param name="maxConnections" value="3"/>
  <param name="copyWhenReading" value="true"/>
  <param name="tablePrefix" value=""/>
  <param name="schemaObjectPrefix" value="ds_repos_"/>
</DataStore>

Workspaces

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>

PersistenceManager (workspaces)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

Versioning

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>

PersistenceManager (versioning)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>

DatabaseJournal

<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
  <param name="revision" value="${rep.home}/revision.log"/>
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mysql"/>
  <param name="schemaObjectPrefix" value="J_C_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>

Perform Tomcat-specific connection tasks

Configure Tomcat to connect to Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Modify context.xml only if you changed ports or passwords.

Step 1: Download the driver and apply it to the Pentaho Server

You need a JDBC driver for your repository database.

Due to licensing restrictions, you must download some drivers yourself.

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

See JDBC drivers reference.

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

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

Step 2: Modify JDBC connection information in the Tomcat context XML file

Database connection information is stored in context.xml.

If your port, password, user, driver class, or host differs, update the examples. Match your environment values.

1. Use your database documentation to confirm:

   • JDBC driver class name

   • JDBC connection string format

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml.

3. Add resource entries if they do not exist already.

MySQL

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="pentaho_user" password="password" testOnBorrow="true"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="hibuser" password="password" driverClassName="com.mysql.cj.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_user" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_admin" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="jcr_user" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/jackrabbit"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

MariaDB

1. Update usernames, passwords, hostnames, and ports to match your environment.

2. Comment out resource entries for databases you are not using.

3. Verify validationQuery="select 1" is correct for your database.

4. Save context.xml.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.

Use Oracle as your repository database (manual installation)

Use these steps to set up Oracle as the Pentaho Repository host database.

Use the ASCII character set when you run repository scripts. Do not use UTF-8. Script failures can occur due to string length limits.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with a QRTZ5_ prefix. Pentaho 10.2.0.1 and later use Quartz 2.x tables with a QRTZ6_ prefix.

When upgrading to 10.2.0.1+, you must create the QRTZ6_ tables. You can optionally migrate existing schedules using the migration script.

If you do not complete the Quartz upgrade, Pentaho Server can fail at startup. You may see Missing Quartz library database error in catalina.log.

Initialize the Oracle Pentaho Repository database

To initialize Oracle as a Pentaho Repository, run several SQL scripts to create Hibernate, Quartz, Jackrabbit (JCR), and Operations Mart components.

Step 1: Change default passwords

For production systems, change the default passwords in the SQL scripts.

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

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

2. Edit these scripts for your user, password, database, and required settings:

   • create_jcr_ora.sql

   • create_quartz_ora.sql

   • create_repository_ora.sql

   • pentaho_mart_oracle.sql

3. Save and close the files.

Step 2: Run SQL scripts

If you are upgrading from earlier Pentaho versions, initialize a new Quartz database for Quartz 2.x.

Back up your data before proceeding.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

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

3. Make sure your Oracle instance is running.

4. Open SQL*Plus (or a similar client) and run the 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/oracle/pentaho_mart_oracle.sql

5. (Optional) Migrate schedules from Quartz 1.x:

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

   2. Update the script for your environment, then save it.

   3. Run the migration script.

The original QRTZ5_ tables remain. Delete them only after you confirm the migration.

Step 3: Verify Oracle initialization

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

1. Open SQL*Plus (or a similar client) and connect to the Oracle database.

2. Log in as hibuser. Verify you can see the tables under hibernate.

3. If you installed Operations Mart, verify you can see the tables under pentaho_operations_mart.

4. Log in as jcr_user. Verify you can see the tables under jackrabbit.

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

1. Log in as pentaho_user. Verify you can see the tables under quartz.

Configure the Oracle Pentaho Repository database

By default, these examples assume Oracle runs on port 1521.

If your ports or passwords differ, update the examples.

Step 1: Set up Quartz on Oracle

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

2. In #_replace_jobstore_properties, set:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate

1. Save the file.

Step 2: Set Hibernate settings for Oracle

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

1. Open hibernate-settings.xml.

2. Update the <config-file> value:

From:

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

To:

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

1. Save and close the file.

Step 3: Replace the audit log file with the Oracle version

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

2. Copy it into pentaho-solutions/system.

Step 4: Modify Jackrabbit repository information for Oracle

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

2. Uncomment the Oracle sections.

3. Comment out the MySQL, PostgreSQL, and MS SQL Server sections.

Use these Oracle sections as your reference:

Repository

<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schemaObjectPrefix" value="fs_repos_"/>
  <param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>

DataStore

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="databaseType" value="oracle"/>
  <param name="minRecordLength" value="1024"/>
  <param name="maxConnections" value="3"/>
  <param name="copyWhenReading" value="true"/>
  <param name="tablePrefix" value=""/>
  <param name="schemaObjectPrefix" value="ds_repos_"/>
</DataStore>

Workspaces

<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schemaObjectPrefix" value="fs_ws_"/>
  <param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>

PersistenceManager (workspaces)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="oracle"/>
  <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
  <param name="tablespace" value="pentaho_tablespace"/>
</PersistenceManager>

Versioning

<FileSystem class="org.apache.jackrabbit.core.fs.db.OracleFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schemaObjectPrefix" value="fs_ver_"/>
  <param name="tablespace" value="pentaho_tablespace"/>
</FileSystem>

PersistenceManager (versioning)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="oracle"/>
  <param name="schemaObjectPrefix" value="pm_ver_"/>
  <param name="tablespace" value="pentaho_tablespace"/>
</PersistenceManager>

DatabaseJournal

<Journal class="org.apache.jackrabbit.core.journal.OracleDatabaseJournal">
  <param name="revision" value="${rep.home}/revision.log" />
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="oracle"/>
  <param name="schemaObjectPrefix" value="J_C_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>

Perform Tomcat-specific connection tasks

After repository configuration, configure your web application server to connect to the repository. This includes JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Update context.xml only if you changed default ports, hosts, or passwords.

Step 1: Download the JDBC driver and apply it to Pentaho Server

1. Download a JDBC driver .jar from Oracle or a third-party provider.

2. If needed, confirm the driver is supported. See JDBC drivers reference.

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

4. (Optional) Copy hsqldb-2.3.2.jar to pentaho/server/pentaho-server/tomcat/lib to keep the sample data.

Step 2: Modify JDBC connection information in the Tomcat context.xml file

Update host, port, username, password, and database identifiers to match your environment. Also comment out resources for databases you do not use.

1. Find the JDBC driver class name and connection string in your database documentation.

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml.

3. Add or update resources similar to the following examples.

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@<host>:1521:<SID_or_service>"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="pentaho_user" password="password"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@<host>:1521:<SID_or_service>"
        validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="jcr_user" password="password"
        driverClassName="oracle.jdbc.OracleDriver"
        url="jdbc:oracle:thin:@<host>:1521:<SID_or_service>"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

1. Save the file.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.

Use MS SQL Server as your repository database (manual installation)

Use these steps to set up MS SQL Server as the Pentaho Repository host database.

Initialize the MS SQL Server Pentaho Repository database

You will run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart schemas.

Use the ASCII character set when you run these scripts. Do not use UTF-8. Some scripts can fail due to string length limits.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with a QRTZ5_ prefix. Pentaho 10.2.0.1 and later use Quartz 2.x tables with a QRTZ6_ prefix.

When upgrading to 10.2.0.1+ you must create the QRTZ6_ tables. You can optionally migrate existing schedules with migrate_old_quartz_data_sqlserver.sql.

Failure to complete this Quartz upgrade can prevent the server from starting. You may see Missing Quartz library database error in catalina.log.

1

Step 1: Adjust MS SQL Server configuration settings

Configure these settings in Microsoft SQL Server Management Studio (or similar):

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.

2

Step 2: Change default passwords

For production systems, change the default passwords in the SQL scripts.

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

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

2. Edit these scripts for your user, password, database, and required settings:

   • create_jcr_sqlServer.sql

   • create_quartz_sqlServer.sql

   • create_repository_sqlServer.sql

   • pentaho_mart_sqlserver.sql

3. Save and close the files.

3

Step 3: Run SQL scripts

If you are upgrading from earlier Pentaho versions, initialize a new Quartz database for Quartz 2.x.

Back up your data before proceeding.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

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

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

4. Run these scripts in 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

This creates:

• Jackrabbit schema and repository tables

• Quartz tables (QRTZ6_ for Quartz 2.x)

• Hibernate repository schema

• Pentaho Operations Mart schema

1. (Optional) Migrate schedules from Quartz 1.x:

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

   2. Update the script for your environment, then save it.

   3. Run the migration script.

The original QRTZ5_ tables remain. Delete them only after you confirm the migration.

4

Step 4: Verify MS SQL Server initialization

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

1. Open MS SQL Server Management Studio.

2. Log in as hibuser.

3. In Object Explorer, select hibernate. Verify tables exist.

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

5. In Object Explorer, select pentaho_operations_mart. Verify tables exist.

6. Log in as jcr_user.

7. In Object Explorer, select jackrabbit. Verify tables exist.

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

1. Log in as pentaho_user.

2. In Object Explorer, select quartz. Verify tables exist.

3. Exit MS SQL Server Management Studio.

Configure the MS SQL Server Pentaho Repository database

After initialization, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart to use MS SQL Server.

These examples assume MS SQL Server on port 1433 and the default passwords. If your ports or passwords differ, update the examples.

1

Step 1: Set up Quartz on MS SQL Server

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

2. In #_replace_jobstore_properties, set this value:

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

1. Save the file.

2

Step 2: Set Hibernate settings for MS SQL Server

All files below are in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

1. Open hibernate-settings.xml.

2. Update the <config-file> value:

From:

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

To:

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

1. Save the file.

3

Step 3: Replace the default audit log file with the MS SQL Server version

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

2. Copy it to pentaho-solutions/system.

4

Step 4: Modify Jackrabbit repository information for MS SQL Server

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

2. Uncomment the MS SQL Server sections.

3. Comment out the MySQL, Oracle, and PostgreSQL sections.

Use these MS SQL Server sections as your reference:

Repository

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>

DataStore

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="databaseType" value="mssql"/>
  <param name="minRecordLength" value="1024"/>
  <param name="maxConnections" value="3"/>
  <param name="copyWhenReading" value="true"/>
  <param name="tablePrefix" value=""/>
  <param name="schemaObjectPrefix" value="ds_repos_"/>
</DataStore>

Workspaces

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_ws_"/>
</FileSystem>

PersistenceManager (workspace)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

Versioning

<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="fs_ver_"/>
</FileSystem>

PersistenceManager (versioning)

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager">
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="pm_ver_"/>
</PersistenceManager>

DatabaseJournal

<Journal class="org.apache.jackrabbit.core.journal.MSSqlDatabaseJournal">
  <param name="revision" value="${rep.home}/revision.log" />
  <param name="url" value="java:comp/env/jdbc/jackrabbit"/>
  <param name="driver" value="javax.naming.InitialContext"/>
  <param name="schema" value="mssql"/>
  <param name="schemaObjectPrefix" value="cl_j_"/>
  <param name="janitorEnabled" value="true"/>
  <param name="janitorSleep" value="86400"/>
  <param name="janitorFirstRunHourOfDay" value="3"/>
</Journal>

Perform Tomcat-specific connection tasks

After repository configuration, configure your web application server to connect to the repository. This includes JDBC and JNDI connections for Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Update context.xml only if you changed default ports, hosts, or passwords.

1

Step 1: Download the JDBC driver and apply it to Pentaho Server

You must provide a JDBC driver for MS SQL Server. Pentaho cannot redistribute all third-party drivers.

1. Download a JDBC driver .jar from Microsoft or a third-party provider.

2. If needed, confirm the driver is supported. See JDBC drivers reference.

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

4. (Optional) Copy hsqldb-2.3.2.jar to pentaho/server/pentaho-server/tomcat/lib to keep the sample data.

2

Step 2: Modify JDBC connection information in the Tomcat context.xml file

If your host, port, username, password, or driver class differs, update the values below. Also comment out resources for databases you do not use.

1. Find the JDBC driver class name and connection string in your database documentation.

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml.

3. Add or update the resources if they do not already exist.

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
        validationQuery="select 1" />
<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="pentaho_user" password="password" testOnBorrow="true"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=quartz"
        validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="hibuser" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=pentaho_operations_mart"
        validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
        username="hibuser" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleData" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_user" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="pentaho_admin" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
        factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
        maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
        maxWait="10000" username="jcr_user" password="password"
        driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://localhost:1433;databaseName=jackrabbit"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

1. Verify validationQuery="select 1" is correct for your database.

2. Save the file.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.

Configure and start the Pentaho Server after manual installation

How you configure and start the Pentaho Server after a manual installation depends on your OS.

Configure the Pentaho Server

Windows

Complete these steps to configure the Pentaho Server on Windows.

The server will not start if any environment variable value is invalid.

1

Modify the Tomcat startup script

The Tomcat startup script must include:

• CATALINA_OPTS (Java options, including memory)

• DI_HOME (required for PDI components)

• The license file location

1. Copy all .jar files from:

<pentaho_install_dir>\pentaho-server\pentaho-solutions\native-lib\win64

To:

<tomcat_webapps_dir>\pentaho\WEB-INF\lib

1. Add the LIBPATH variable to the Tomcat startup.bat file:

set LIBPATH=<pentaho_install_dir>\pentaho-server\pentaho-solutions\native-lib\win64

1. Make sure Tomcat is not running.

   1. Open Task Manager.

   2. Look for Tomcat.

   3. If Tomcat is running, stop it.

2. Open the startup script in a text editor:

<pentaho_install_dir>\pentaho-server\tomcat\bin\startup.bat

1. Find the CATALINA_HOME section.

Add this line before it:

SET DI_HOME=%~dp0..\..\pentaho-solutions\system\kettle

1. Add the license option to CATALINA_OPTS.

Update the existing CATALINA_OPTS line by adding:

-Dpentaho.license.information.file=%PENTAHO_LICENSE_INFORMATION_PATH%

Example:

SET CATALINA_OPTS=-Xms4096m -Xmx6144m -Djava.library.path=%LIBPATH% -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 -Dpentaho.license.information.file=%PENTAHO_LICENSE_INFORMATION_PATH% -DDI_HOME=%DI_HOME%

1. Save and close the file.

2

Run the Pentaho Server as a Windows service (optional)

You can create a Tomcat task to run the Pentaho Server as a Windows service.

For details, see Tomcat Windows service how-to documentation.

When you run tomcat8w.exe, use these settings:

• Startup tab

  • Working Path: Add bin to the end of the path. Example: C:\pentaho\server\pentaho-server\tomcat\bin

• Java tab

  • Initial Memory Pool: 2048

  • Maximum Memory Pool: 6144

  • Java Options: add the following items:

    • -DDI_HOME

    • -Dfile.encoding

    • -Djava.library.path

    • -Dpentaho.license.information.file

Example values:

• -DDI_HOME=C:\pentaho\server\pentaho-server\pentaho-solutions\system\kettle

• -Dfile.encoding=utf8

• -Djava.library.path=<pentaho_install_dir>\pentaho-server\pentaho-solutions\native-lib\win64

• -Dpentaho.license.information.file=%PENTAHO_LICENSE_INFORMATION_PATH%

You must set PENTAHO_LICENSE_INFORMATION_PATH to point to the .elmLicInfo.plt file:

PENTAHO_LICENSE_INFORMATION_PATH=<complete_path>\.elmLicInfo.plt

3

Install licenses

Install your enterprise licenses before you start the server.

See Acquire and install enterprise licenses.

License activation overview (optional)

1. Contact Sales and acquire the necessary licenses. You will receive an activation ID or cloud license server information.

2. Deploy a local license server or a cloud license server to manage license activation.

   • Local license server: see Activate a license using a local license server in Administer Pentaho Data Integration and Analytics. Use offline activation if your license server cannot reach the back office URL.

   • Cloud license server: see Activate a license using a cloud license server in Administer Pentaho Data Integration and Analytics.

4

Start the Pentaho Server

Go to Start the Pentaho Server.

Linux

Complete these steps to configure the Pentaho Server on Linux.

The server will not start if any environment variable value is invalid.

1

Modify the Tomcat startup script

The Tomcat startup script must include:

• CATALINA_OPTS (Java options, including memory)

• DI_HOME (required for PDI components)

• The license file location

1. Copy all .jar files from:

<pentaho_install_dir>/pentaho-server/pentaho-solutions/native-lib/linux/x86_64

To:

<tomcat_webapps_dir>/pentaho/WEB-INF/lib

1. Add the LIBPATH variable to the Tomcat startup.sh file:

export LIBPATH=<pentaho_install_dir>/pentaho-server/pentaho-solutions/native-lib/linux/x86_64

1. Make sure Tomcat is not running.

   1. Open a terminal.

   2. Run ps -A.

   3. If Tomcat is running, stop it.

2. Open the startup script in a text editor:

<pentaho_install_dir>/pentaho-server/tomcat/bin/startup.sh

1. Find the #Check that target executable exists line.

Add this line above it:

DI_HOME=<pentaho_install_dir>/pentaho-server/pentaho-solutions/system/kettle

1. Add the license option to CATALINA_OPTS.

Update the existing CATALINA_OPTS line near the end of the file.

Example:

export CATALINA_OPTS="$CATALINA_OPTS -Xms4096m -Xmx6144m \
  -Djava.library.path=$LIBPATH \
  -Dsun.rmi.dgc.client.gcInterval=3600000 \
  -Dsun.rmi.dgc.server.gcInterval=3600000 \
  -Dpentaho.installed.licenses.file=$PENTAHO_INSTALLED_LICENSE_PATH \
  -DDI_HOME=$DI_HOME"

1. Save and close the file.

2

Install licenses

Install your enterprise licenses before you start the server.

See Acquire and install enterprise licenses.

3

Start the Pentaho Server

Go to Start the Pentaho Server.

Start the Pentaho Server

After you configure the Pentaho Server for your platform:

1. Define the DI_HOME environment variable.

The default path for Windows and Linux:

• Windows: PENTAHO_INSTALLATION_FOLDER\pentaho-server\pentaho-solutions\system\kettle

• Linux: PENTAHO_INSTALLATION_FOLDER/pentaho-server/pentaho-solutions/system/kettle

1. Run the startup script for your web application server.

   • Windows: startup.bat in the Tomcat bin folder

   • Linux: startup.sh in the Tomcat bin directory

2. Open http://localhost:8080/pentaho to access the Pentaho User Console (PUC).

If your server uses a different hostname or port, update the URL.

Troubleshoot startup issues

Troubleshooting is centralized in Troubleshooting possible installation and upgrade issues.