The two procedures SYSCS_COMPRESS_TABLE and SYSCS_INPLACE_COMPRESS_TABLE) for
compacting are going to hold a lock on the table during the total time of
compression which for 20 million rows is going to be a long time. Not the
answer that you want to hear but that is what it is.
Have you tried querying the SYSCS_DIAG.SPACE_TABLE to make sure that you have
space to be reclaimed. You might find that compressing the table is not going
to return much space in any case.
SELECT *
FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE'))
AS T2
Replace 'MYSCHEMA' and 'MYTABLE' with your particular ones.
A couple of thoughts, it might be that you could copy the rows from one table
to a new table and then drop the original table and rename new table back. Of
course, this does require you to have about twice the disk usage while being
done and you would need to be smart about making sure that any new rows after
you start the copy are also moved to the new table, but it might allow you to
have the old table be used while you are building a new compressed table.
Hope this helps.
Brett
From: Stefan R. [mailto:elstefan...@gmail.com]
Sent: Tuesday, March 13, 2012 12:59 PM
To: derby-user@db.apache.org
Subject: Compress large table
Hi,
In one of our current projects we are using derby and have some large db tables
(over 20 Million rows and some indexes).
We decided to clean this table with a scheduled task. Now to be able to free
some disk space we want to compress the files using SYSCS_COMPRESS_TABLE. What
would be the best option to use this function and keep the table lock as short
as possible? Our app needs to keep running while the compression is taking
place.
Do you have any suggestions?
Thank you,
Stefan
