I may be mistaken, but it would appear that if you rewrite a foc-file (ON TABLE HOLD AS ... FORMAT FOCUS), that WebFOCUS won't reduce the size of the file if the data would fit in a smaller file, but keeps it at the original size instead. Of course it does grow if the file needs to store more data, or there would be trouble. I can see how this would be a performance optimisation once a report is in production.

However, sometimes we change our minds about how to distribute our data among foc-files, which does at times result in an expected file size reduction. Due to the (perceived) above optimisation, this of course does not happen.

Is there a way to trim down existing foc-files without having to re-create them from scratch? Something akin to vacuuming database tables?

This message has been edited. Last edited by: Kerry,

There are at least two ways of trimming down the file size of focus files, but they all involve re-creating the file. First one is to do a rebuild dump, then delete the file, then do a rebuild load.
The other one is to table to a hold file, delete the focus file, followed by a reload with the use of modify or maintain.
There is no utility that I know of that will do a truncate of the file without at some point physically removing the 'old' data file.

Something I have discovered recently too is, each one of these held files consumes space on the disk. That is, if you hold one file, sort it and hold it as a second name, there will be 2 equal sized files on the disk (assuming you didn't add WHERE criteria on the second file). So, it may make your processing more efficient or logical, but from a disk usage perspective, it is a bit worse.

IT = Tradeoffs

- ABT

I think GamP's suggestion of REBUILD is the safest was to "compress" a FOCUS file.

You could even create an include fex that will handle the job for you, so its generic, and can be reused.

I may have done this at a customer site once.

If you are using ON TABLE HOLD FORMAT FOCUS, the file is considered temporary (and by design, should be temporary!). REBUILD would not be a performance enhancer for an 'on-the-fly' HOLD file... Rather just delete the foc file before the HOLD because it is emptied anyway.

If you are using MODIFY or MAINTAIN, then the REBUILD is your best bet.

We load quite a few FOCUS DB in Batch jobs. (controlled by an AutoSys Scheduler). One approach is the to the Create and Load process in a work folder. We use one per batch job to keep the jobs isolated.

When the load is finished and cbalanced, the new file is moved to the "production" folder and made available for use.

The batch jobs do several TABLEs. The size of the FOCSORT increases. We have included a delete of FOCSORT between the job steps to keep it from growing to large. We only became aware of this problem when we moved to 7.6.10 from 7.6.4. But, it may have become noticeable because our available disk shrunk by 20 gig during the install process. The server build team has since corrected their error.

In older releases focsort never gave a problem because it was always rewritten with every new sort. In fact, this still should be the case. The positive side is that focsort now is 'unlimited' in size. So also for very large sorts and aggregations focsort can accomodate that amount of data.
Focsort will however, very much like a focus datafile - which it was in the old days, never be reduced in size automatically.