/
Configuring 2.X Server for a Non-Default Repository DB

Configuring 2.X Server for a Non-Default Repository DB

Dec 10, 2008

Part of the reason for the refactoring that happened between 1.7 and 2.0 was to make it easier to change the configuration of the server to use a different DB as the repository (hibernate & quartz).  This simplifies the build process and allows us to make a single build that can be easily reconfigured.  Here's how you do it:

  1. Edit pentaho-solutions/system/hibernate/hibernate-settings.xml

    • Comments in the file tell you which file to point to for the specific DB you want to use as the repository

    • Choices of repository DB for which we supply configuration files are:

      • HSQLDB

      • MySQL

      • Postgres

      • Oracle

  2. Look at pentaho-solutions/system/applicationContext-acegi-security-hibernate.properties

    • Change the config appropriately

  3. Look at administration-console/resource/config/console.xml

    • Make sure hibernate-config-path is pointing to the correct file in administration-console/resource/hibernate

  4. Look at tomcat\webapps\pentaho\META-INF\context.xml

    • Point the data sources for Hibernate and Quartz to your DB of choice

    • Change the "validationQuery" to your DB specific query(For postgreSQL use "select 1" as the query. For oracle, use "SELECT 1 FROM DUAL" as the query).

    • Modify the connection information for your DB.

  5. Edit pentaho-solutions\system\quartz\quartz.properties

    • Change "DriverDelegateClass" for different databases.
      org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.<DriverDelegateClass>
      Where DriverDelegateClass is one of:- StdJDBCDelegate (for many JDBC-compliant drivers)

      • - MSSQLDelegate (for Microsoft SQL Server drivers)

      • - PostgreSQLDelegate (for PostgreSQL drivers)

      • - WebLogicDelegate (for WebLogic drivers)

      • - oracle.OracleDelegate (for Oracle drivers)

  6. For Oracle and Postgres, look at pentaho-solutions\system\pentaho.xml. Replace the insert statement in the <auditConnection> element with the following insert statement:

INSERT INTO PRO_AUDIT (AUDIT_ID, JOB_ID, INST_ID, OBJ_ID, OBJ_TYPE, ACTOR, MESSAGE_TYPE, MESSAGE_NAME, MESSAGE_TEXT_VALUE, MESSAGE_NUM_VALUE, DURATION, AUDIT_TIME) values (NEXTVAL('hibernate_sequence'),?,?,?,?,?,?,?,?,?,?,?)

That should be all you need to do to set up a non-default repository DB.  Good luck!