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.
FROM TABLE (SYSCS_DIAG.SPACE_TABLE('MYSCHEMA', 'MYTABLE'))
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.
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?