Hitachi Vantara Pentaho Community Wiki
Child pages
  • 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!

  • No labels

5 Comments

  1. Anonymous

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

     For the Quartz problem check out this thread

  2. 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. Anonymous

    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. 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. 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