I want to make the unused space availabe to derby for the insertion of new rows. It should be optinal to give unused space back to the OS. In order to do so, I have to use some system utility.
I'm not totally sure whether the 5th word in the above line is "optional" or "optimal". To return unused space to the OS, you have to make a special call; simply deleting your rows from the table with DELETE FROM is not enough, nor is updating your rows to set every column to the shortest-possible value.
The only utility I found is SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE. But is this correct?
It is correct that the SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE procedure will return space to the operating system if there is space available. There is also the SYSCS_UTIL.SYSCS_COMPRESS_TABLE procedure. And there is the DROP TABLE statement. And the TRUNCATE TABLE statement.
Since SYSCS_UTIL.SYSCS_COMPRESS_TABLE will only give unused space back to the OS, this is not helpfull either. Or is it?
I don't understand your question: I thought that you **wanted** to "give unused space back to the OS".
Where is my misunderstanding, i.e. what do I have to do, in order do reclame the space? Or does derby somehow optimize disk usage in the described case automatically?
Derby does many such optimizations. You might want to start by reading the archives of derby-user from May-June, 2005, specifically starting here: http://mail-archives.apache.org/mod_mbox/db-derby-user/200505.mbox/%3cloom.20050531t105713-...@post.gmane.org%3E Maybe you could run some experiments, to try different approaches, and report back to the list about what experiments you have run, and what you have found, and the list could help you understand the behaviors you are seeing. Note that if you actually have "a huge number of rows", where the data you store in each row is "large, say, on average of about 9000 characters" and then you "replace the data and they are on average only 10 characters long", you have created a somewhat mysterious program, to me. Will you ever make those values longer again? Or will they permanently be only 10 characters long? Why did you need the 9000 byte values in the first place? Does this happen with every row in your table, or is it more of a mixture of some rows growing and some rows shrinking? The wonderful thing about a database like Derby is that you have many choices in how you build your application, and Derby will work very hard to try to accommodate whatever choices you make. The challenging thing about a database like Derby is that you have many choices in how you build your application, and Derby will work very hard to try to accommodate whatever choices you make. thanks, bryan
