By default, GRIA uses HSQLDB (Hypersonic) as its database management system for storing the services' state information in. For most users Hypersonic will be a good choice as it requires no configuration or management. Some users may want to use a more heavy-weight RDBMS such as MySQL, Oracle etc. GRIA uses the Hibernate object/relational persistence system, so switching from one RDBMS to another is made a lot easier.

Currently we have tested GRIA with MySQL and PostgreSQL. GRIA has not been tested on other RDBMS such as Oracle, DB2 or MS SQL Server but these are available through Hibernate and may work.

To use a GRIA package with an alternative database, the RDBMS must be installed, the database created and the hibernate.properties file for the GRIA package (found in the service's configuration directory) must be edited accordingly.

To improve database performance, you may also wish to enable database connection pooling. The steps required to do this are detailed in the connection pooling page.

MySQL

To use a MySQL database called "hibernate" on a server "dbhost" with username "uname" and password "pword" follow the instructions below

1. Log in to MySQL as the root user: type mysql -u root -p at the shell prompt.
2. Enter the mysql root user's password when prompted.
3. Enter the following commands at the MySQL prompt:

   create database hibernate;
   grant all privileges on hibernate to uname@'localhost' identified by 'pword';
   grant all privileges on hibernate to uname@'%' identified by 'pword';
   grant all privileges on hibernate.* to uname@'localhost' identified by 'pword';
   grant all privileges on hibernate.* to uname@'%' identified by 'pword';
   flush privileges;
   

To use this database you need to edit the hibernate.propeties file located in the service's configuration directory. First you must comment out any lines that refer to HypersonicSQL and uncomment the lines that refer to MySQL.

Now edit the file as shown below (make sure to replace "dbhost" with the actual name of the host where the database is located and "uname" and "pword" with the real username and password).

hibernate.dialect org.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql://dbhost/hibernate
hibernate.connection.username uname
hibernate.connection.password pword

For further information consult the Hibernate and MySQL documentation.

Database connection pooling improves the performance of database operations by eliminating most of the overhead of acquiring database connections.

To configure connection pooling, you need to edit the hibernate.propeties file located in the services configuration directory and edit certain lines in the file, as described below.

First disable hibernate's basic connection pool by ensuring that there is a # at the start of the following line, as below.

#hibernate.connection.pool_size 1

Next, enable the c3pO connection pool by uncommenting the following lines by removing the # at the start of each line.

#hibernate.c3p0.min_size 2
#hibernate.c3p0.max_size 7

You should also set appropriate values for the "min_size" and "max_size" properties. These are the minimum and maximum number of database connections that will be managed by the connection pool. The uncommented lines with example values are shown below.

hibernate.c3p0.min_size 10
hibernate.c3p0.max_size 40

It is important to ensure that the maximum number of pooled connections (accross all GRIA services that use the same database) are within the limits of your database configuration. Refer to your database documentation and your database configuration files for details of the maximum number of available connections.