Database Configuration Prior To Version 3.2

[resplin]

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com

ConfigurationDatabaseInstallation

Table of Contents

• 1 Database Configuration
  
  
  
  • 1.1 Introduction
  
  
  • 1.2 Overriding the Database Connection Properties
  
  
  • 1.3 Connection Pool Configuration
  
  
  • 1.4 Oracle Example
  
  
  • 1.5 PostgreSQL Example
  
  
  • 1.6 Hypersonic SQL Databases
  
  
  • 1.7 DB2
  
  
  • 1.8 MS-SQL Databases
  
  
  • 1.9 Other Databases
  
  
  • 1.10 Appendix
  
  

Database Configuration

Introduction

This page assumes knowledge of how to extend the repository configuration and override properties: Repository Configuration

It relates to versions of Alfresco prior to 3.2 3.2 Database Configuration

Overriding the Database Connection Properties

Remove the .sample extension from the following files:

<extensionRoot>/alfresco/extension/custom-repository-context.xml.sample
<extensionRoot>/alfresco/extension/custom-repository.properties.sample
<extensionRoot>/alfresco/extension/custom-hibernate-dialect.properties.sample

Modify the properties files to enable the configuration appropriate to the database installation that the server will be run on.

Connection Pool Configuration

To enable the advanced connection pool override, remove the .sample extension from the following files:

<extensionRoot>/alfresco/extension/custom-connection-pool-context.xml.sample

Modify the properties as required, paying particular attention to the validationQuery property.
It is also possible to use a completely different connection pool implementation, e.g. C3P0.

Oracle Example

To use Oracle please follow the steps below:

• Create a new 'alfresco' user and schema in oracle.
  
• Ensure that the alfresco user has the required privileges to create and modify tables.  This can be removed once the server has started, but may be required during upgrades.
  
• Override following repository properties in your custom-repository.properties:
  

db.driver=oracle.jdbc.OracleDriver
db.name=alfresco
db.url=jdbc:oracle:thin:@<machinename>:1521:<database sid>
db.username=alfresco
db.password=alfresco

• Override the following Hibernate property in your custom-hibernate-dialect.properties:
  

hibernate.dialect=org.hibernate.dialect.Oracle9Dialect

• If you have multiple Alfresco instances installed on an Oracle server, you will need to force the database metadata queries to target the schema that each database user is using.  Put the following in custom-hibernate-dialect.properties:
  

hibernate.default_schema=ALFRESCO

• Copy the Oracle JDBC driver JAR into \tomcat\common\lib (Tomcat) or \tomcat\lib (on Tomcat 6) or \jboss\server\default\lib (JBoss).
  

• You can now startup the Tomcat or Jboss server
  

• Note: if you get JDBC errors ensure the location for the Oracle JDBC drivers are on the system path, or add them to the relevant lib directory of the app server - the Oracle JDBC drivers can be found in the <orainst>/ora<ver>/jdbc/lib directory (e.g. c:\oracle\ora92\jdbc\lib)
  

• Note: for performance reasons it is recommended that you use the 10g JDBC drivers, even if your Oracle server is 9i.
  

PostgreSQL Example

To use PostgreSQL please follow the steps below.

• Copy the appropriate PostgreSQL driver JAR to /tomcat/common/lib (TomCat) or /jboss/server/default/lib (JBoss).
  

• Create a new 'alfresco' user and database. Set this user's password to 'alfresco'.
  
• Ensure that the alfresco user has the required privileges to create and modify tables.
  

• Verify/modify the Alfresco data location in the custom-repository.properties file.
  

• Override following repository properties in custom-repository.properties:
  

db.driver=org.postgresql.Driver
db.name=alfresco
db.url=jdbc:postgresql://localhost/<database name>
db.username=alfresco
db.password=alfresco

• Override the following Hibernate property in custom-hibernate-dialect.properties:
  

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.query.substitutions=true TRUE, false FALSE

• Ensure that the postgresql.conf file (pg_hba.conf for postgres 8.1.3) (refer to PostgreSQL documentation for more info on this file) contains:
  

host    all    all    127.0.0.1/32    password

... to allow password-authenticated connections via TCP/IP.

• Do not forget to change the ownership of the directory tree specified in custom-data-location.properties to be owned by the user running the alfresco server.
  

• You can now startup the Tomcat or Jboss server
  

Hypersonic SQL Databases

To use Hypersonic SQL please follow the steps below

• Copy the appropriate HSQL driver JAR to /tomcat/common/lib (TomCat) or /jboss/server/default/lib (JBoss).
  

• Edit custom-repository.properties file add/update the following entries:
  

dir.root=/data/alfresco/data/
db.username=sa
db.password=
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:file:/data/alfresco/hsql_data/;ifexists=true;shutdown=true;

• In custom-hibernate-dialect.properties the following entries are added/enabled:
  

hibernate.dialect=org.hibernate.dialect.HSQLDialect

• Be aware that the password is empty.
  

• Start the application server to verify your configuration changes.
  

Note: Hypersonic Database is not supported for Enterprise use

DB2

There is no official support for DB2.

• Default access to DB2 from Alfresco will use the cursor stability isolation level. This will produce concurrency issues arising from read locks. The following registry variables need to be set to work round this. They do not resolve all issues. See [http://publib.boulder.ibm.com/infocen.../r0005665.html]
  
  •   db2set DB2_EVALUNCOMMITTED=ON     (avaiable from DB2 v8.1.4)
    
  •   db2Set DB2_SKIPDELETED=ON         (avaiable from DB2 v8.1.4)
    
  •   db2set DB2_SKIPINSERTED=ON        (avaiable from DB2 v8.2.2)
    

• Create a DB2 database with a larger page size of 32 K and using UTF8. If you do not do this you will see error SQL0286N (sqlCode -286, sqlstate 42727) as the schema is created for tables that do not fit the page size.
  

• Set up the alfresco user and associated schema. Note DB2 only integrates with the OS security. You can not add a DB user with a password as you can say in oracle.
  

• The default hibernate DB2 driver is fine
  
  • hibernate.dialect=org.hibernate.dialect.DB2Dialect
    

• Known issues
  
  • DB2 is case sensitive (and a pain to do otherwise).
    
  • AVM support requires a reverse index for AVM_HISTORY_LINKS (descendent, ancestor)
    
  • Statistics must be updated after this index is added (after bootstrap is fine)
    
  • Requires DB2 v8.2.2 or later
    

• DB2 Configuration issues
  
• If you see DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH203 0X5359534C564C3031, DRIVER=3.50.152
  
  • On Windows
    
  • start the db2 command line window
    
  • cd ../bnd
    
  • db2
    
  • connect to dbname
    
  • bind @db2cli.lst blocking all grant public clipkg 10
    

• • It is possible you could get away with 4 above (the default is 3)
    
  • check the package is there
    
  • select pkgname from syscat.packages where pkgname like 'SYSLH20%'
    

MS-SQL Databases

This is my configuration for the MS-SQL 2005 database.

• I decided to use the jtds-drivers.  Download the jtds-distribution (http://jtds.sourceforge.net/), and copy the jtds-1.2.2.jar to your /tomcat/common/lib (Tomcat) or /jboss/server/default/lib (JBoss).
  

• create an alfresco database and corresponding user, with sufficient rights on the database (to create tables etc).
  

• Edit the ALFRESCO_HOME/tomcat/shared/classes/alfresco/extension/custom-repository.properties file and add/update the following entries:
  

dir.root=/alfresco_prd_data/content
dir.indexes=/alfresco_prd_data/indexes
db.username=alfresco
db.password=alfresco
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://DBSERVERNAME:PORT/alfresco

• In custom-hibernate-dialect.properties the following entries are added/enabled:
  

hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect

• Start the application server to verify your configuration changes.
  

Other Databases

Hibernate supports a range of database dialects: Hibernate Dialects

Alfresco supports Microsoft SQL Server and Sybase but requires custom dialects to be used. The configuration is the same. Make sure the database connector is on the classpath, set the hibernate dialect in the config, set the properties for the database connection in the config, make sure the appropriate user is defined on the database and that they can login, that the holder db/tablespace/... where this user can create tables exists, and that they have rights to create tables etc.

Examples for setting the hibernate dialect are provided here

Appendix

• Sample Diagram of Core ADM Tables
  
• The JDBC-Driver for Oracle is in the jar ojdbc14.jar.
  

---

Back to Server Configuration