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

Owned by Former user (Deleted)
Last updated: Dec 10, 2008 by Former user (Deleted)Version comment
1 min read

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!