Maintenance Route For BIE's Internal Hypersonic Database.

This is a copy of an e-mail from the BIE mailling list. It describes how to setup a route that cleans old data from BIE's internal Hypersonic database. Hypersonic has issues with very large record sets and long transactions so keeping this database small really helps BIE's stability and performance.

NOTE: This e-mail doesn't mention that the username for connecting to BIE's internal Hypersonic database is "sa", and the password is "BIE".

The E-Mail

I recently ran into some trouble with the HypersonicSQL database that
BIE 6 uses internally for various things. While Hypersonic seems pretty
solid, it looks like it start falling apart when the database grows
large, like over 500m or so.

We've been running with BIE6 on production for about 5 months now and
we've processed somewhere around 4 million transactions with it so far.
This is fine, but the hypersonic DB had grown to around 2 gb. At this
size, h-sql seems to break down and takes a LONG time to start up and
shut down. At 2gb, my production server was taking about 5-10 minutes
to start.

It looks like the h-sql size problem comes from 4 tables

1) Transaction
2) TransactionDetail
3) ActivityInstanceMeta
4) ActivityInstanceStates

Transaction and TransactionDetail contain the records that get logged
when the TransactionLogger action is used. I'm not 100% sure about
ActivityInstanceMeta and ActivityInstanceStates but they seem to hold
data about individual process runs(?).

I have no reason to keep any of the data in the above tables for more
than about 7 days, even on our production servers. If the data is
important enough to keep a history of, we have other methods of logging
it.

To keep these tables to a reasonable size, I wrote a route that purges
anything over a certain age from these tables. I'm including everything
I needed to do it, including the HSQLDB jdbc driver and the BPML file
that actually does the purging.

To use this route, first install the attached hsqldb.jar file as a jdbc
driver. It's classname is 'org.hsqldb.jdbcDriver' and the URL it seems
to want is 'jdbc:hsqldb:'. Then, make a connection called '
BIE_Internal_Database'. Don't forget the underscores, BIE gets mad
about DB connections with spaces in them. The URL for this connection
should be ' jdbc:hsqldb:/services/hypersonic/BIE' where is the directory name of where you have BIE installed.

Then, if you have the latest patches, import the BPML file. If you
don't have the latest version, put the bpml file in the packages
directory.

The route will delete anything older than about a week from those 4
tables, then do a checkpoint on the database. I've scheduled the route
to run once a day on all my BIE6 servers.

IMPORTANT NOTE: IF YOU'RE DATABASE IS TOO LARGE NOW, THE ROUTE WILL
FAIL. THERE SEEMS TO BE A MAXIMUM NUMBER OF ROWS THAT HYPERSONIC CAN
DELETE AT ONE TIME. Because I had millions of rows in these tables, I
needed to get the DB fixed up by hand first. What I did was copy the
BIE data files to my desktop computer, then I used the hypersonic front
end to delete rows 50,000 at a time. When I finished, I copied the DB
back to the server.

I have a question about all this for the JBOSS gurus out there. It
seems to me that this is something that should be done automatically by
BIE from time to time. The number of days to keep logs should be
configurable in the front end somewhere and I believe BIE should do this
cleanup both on demand and on a fixed schedule. Is this possible with
JBOSS? I know NOTHING about either JBOSS or J2ME myself, so I'm not
exactly sure how this would work.

The above post was by John Fruetel of FoodOrigins. He has contributed many useful suggestions and patches to the BIE user community,

Note: After running maintenance the first time you may want to reogranize the Hypersonic database if your BIE.data file is still of significant size. Instructions for starting the Hypersonic client and reorganizing the database are here. The BIE.data file is located in the $BIE_HOME/services/hypersonic directory where $BIE_HOME is the installation root of your BIE instance.