The Wiki will be offline Monday, November 20, for upgrade between 10:00am ET and 5:00pm ET.
Hitachi Vantara Pentaho Community Wiki

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

Skip to end of metadata
Go to start of metadata

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!

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. Dec 06, 2008

    Anonymous says:

    http://forums.pentaho.org/showthread.php?t=66604  For the Quartz problem c...

    http://forums.pentaho.org/showthread.php?t=66604

     For the Quartz problem check out this thread

  2. Dec 10, 2008

    Kevin Haas says:

    Re: #6 above, note that for Oracle you should NOT change: INSERT INTO PRO_AUDIT...

    Re: #6 above, note that for Oracle you should NOT change:

    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 (HIBERNATE_SEQUENCE.nextval,?,?,?,?,?,?,?,?,?,?,?)

    This should use the Oracle nextval sequence syntax

  3. Dec 18, 2008

    Anonymous says:

    It looks like in 2.0.1, step 6 is not required.  The audit ID may not be us...

    It looks like in 2.0.1, step 6 is not required.  The audit ID may not be using the sequences any more.  Either way, I left it alone and it worked for me.

  4. Jan 15, 2009

    Darrin Blocker says:

    Following the above document we were able to use hibernate against SQL Server 20...

    Following the above document we were able to use hibernate against SQL Server 2005. The only thing we did was omit the mapping file and of course use the MSServer dialect. We are still testing but have not come accross any issues.

  5. Apr 06, 2009

    Balázs Bárány says:

    For PostgreSQL, see http://forums.pentaho.org/showthread.php?t=59202 :  To...

    For PostgreSQL, see http://forums.pentaho.org/showthread.php?t=59202 :

     To allow Quartz to properly utilize a PostgreSQL database, try replacing the following in the quartz.properties in the system\quartz folder of the solutions.
    (i.e.- solutions\system\quartz\quartz.properties)

    Replace:

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

    with

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