I agree it would be nice if there was a purge method on the stats service.
It would also be nice if some additional configuration options were
available that applied to retention of data (all, 1 week, 1 day, 1 hour -
those kind of options).
We did some testing with a simple service. We injected the JetspeedDS data
source into our service and configured the Spring init-method. In the init
method we deleted all the rows from the PAGE_STATISTICS, PORTLET_STATISTICS,
and USER_STATISTICS tables using SQL. Doing the deletes didn't result in
the physical file size shrinking since Derby doesn't reclaim used space. We
added the necessary SP Derby calls to SYSCS_UTIL.SYSCS_COMPRESS_TABLE for
each table. This is a Derby-only solution but that's OK with us for now.
This worked fine with smaller stats tables. However, the larger the tables
the less reliable the init-method became. As the table grew we'd get
different exceptions doing the delete in the init-method, probably due to
different timing issues and colliding with things Jetspeed was trying to do
as well. We saw concurrent update exceptions and Container was opened in
read-only mode exceptions.
If someone has a suggestion on how to get our service called in a more
reliable, predictable manner after or even before Jetspeed has done its
thing that'd be great. For now we've solved the problem by not calling a
service but shrinking the database using the Derby ij tool from the command
line. I've shared the script below that we are using.
connect 'jdbc:derby:c:/temp/db/productiondb';
delete from APP.PAGE_STATISTICS;
delete from APP.PORTLET_STATISTICS;
delete from APP.USER_STATISTICS;
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'PAGE_STATISTICS', 0);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'PORTLET_STATISTICS', 0);
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'USER_STATISTICS', 0);
commit;
exit;
On Tue, Mar 8, 2011 at 2:03 PM, David Taylor <[email protected]>wrote:
> On Tue, Mar 8, 2011 at 8:29 AM, Jeff Pierce <[email protected]> wrote:
> > Thanks for the quick response everyone.
> >
> > I think we'll look to implement a JS service that cleans up the data on a
> > regular basis. I'm assuming even a user-defined service can get access
> to
> > the JS database or possible the stats service has methods to call that we
> > can utilize.
> >
> Yes you can get access to the data source from a service. Take a look
> at the Statistics impl, since it uses JDBC batch updates, it needs
> access to the JDBC data source. In fact it would be a nice addition to
> the API to have a method to purge the stats db
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
