HSQL database maintenance

Adeptia uses the HSQL database for it's embedded back-end database. Within this database, all of the objects and activities you've created are stored. Since the HSQL database is java based, it is easily integrated with Adeptia and offers good performance at lower data volumes.

You can view the size of this database as the indigo.data file, located here: AdeptiaServer\ServerKernel\embeddedDb\db

In certain situations, your HSQL DB may grow fairly large. We have a couple recommendations for this situation:

Recommendation # 1:

The following property values can be changed in database property file to support large data file (for backend HSQL database, this change needs to be done in “ServerKernel/embeddedDb/db/indigo.properties”):

# initial value

hsqldb.cache_file_scale=2

# new value

hsqldb.cache_file_scale=8

Above change will allow storage of 8 GB in .data file. To apply the change to an existing database, database shall be shutdown first, then the property = value line above should be added to the .properties file before reopening the database.

Recommendation # 2:

 Also you can DEFRAG the HSQL database periodically to reclaim any unused spaces in .data file, usually when the .data file has reached 250MB.  For this, you need to use SQL statement SET CHECKPOINT DEFRAG <numeric value> in database script file (for backend HSQL database, this change needs to be done in “ServerKernel/embeddedDb/db/indigo.script”). The numeric value is the number of megabytes of recorded empty spaces in the .data file that would force a DEFRAG operation. Low values result in more frequent DEFRAG operations. A value of 0 indicates no automatic DEFRAG is performed. The default is 200 megabytes of lost space. For log size = 50 (set from property “hsqldb.log_size” in database property file, in our case this value is set to 50 in property file), the numeric value for checkpoint defrag shall be set to 10. So, here is the SQL statement that can be used:

SET CHECKPOINT DEFRAG 10

To apply the change to an existing database, database shall be shutdown first, then above SQL should be added to the .script file before reopening the database.

Steps to shrink any running HSQL database (to reclaim any unused spaces in .data file)::

1)  Connect to the HSQL database through following url:

 URL:  jdbc:hsqldb:hsql://<<machine Name or machine IP>>:<<port number>>

 User-id: sa

Password will be blank

Note:

- the default port for the HSQL backend database is 2476

- "Machine name or machine IP" is the name/IP where Adeptia Suite is running

 2) Run the following command:

CHECKPOINT DEFRAG