I'm in the process of purging old data from our OE 10.2B DB and trying to determine how much space/blocks I gain or are made reusable by doing this. What information do I need to determine that and how do I do the calculation?
Thanks in advance for your help.
For a pretty wild guess you can estimate based on the records per block, number of rows to delete, average row size and a fudge factor of how well packed the data you plan to delete is concentrated per DB block.
To get the exact numbers try the delete against a copy of the database :-) (with a dbanalys before and after). This has the advantage of knowing the new free blocks, an approximate length of time for the deletes and a safe way to check to make sure you only delete what you wanted to.
Depending on the percentage of data you are removing per table you might be better off with copying the data you want to keep to an identical table (different name obv) and then renaming the tables afterwards.
Purging Data and then guestimating how much space will result, is a bit like firing buckshot blindfolded. It could result in a chunk of blocks being freed up, it could result in most of the blocks only having fragments freed up. You also need to take into account Type I or Type II Storage areas. With Type II Storage Areas, the 'space' available will then only be available for the same object. iow, if there are 2 tables in the area, and you purge the first table, the resulting space will only be available for that table's new/update records.
Purging Data is then really all about how many Blocks are added to the Free Chain as a result, there will also be blocks added to the RM Chain (not completely empty). To this end, the better reporting would be pre and post purge:
1. prostrct statistics dbname area 'area name'
2. proutil dbname -C chanalys area 'area name'
When I tried to run that command against the entire DB, rather than a specific area, I received the error -cs parameter requires a numeric argument. I used the -csoutput option to specify the file name. What am I missing that's causing that error?
-csoutput was only added around OE 11.4, so any earlier version you'll get this error (or if you mistype the parameter)
proutil dbname -C chanalys area Cust_Data -csoutput
or , for the entire DB:
proutil popo -C chanalys -csoutput
then creates two output files for you:
db-name .ch.txt — For chain analysis output
db-name .block.txt — For block analysis output