Get started Bring yourself up to speed with our introductory content.

Is it possible to re-use deleted records?

If I use 're-use deleted records' on a database file, what impact on performance should I expect? If the performance is not going to suffer too much, do I still have to run reorg on the file on a regular basis or is it 'cleaner' automatically by using spaces created by previously deleted records?

I have looked for articles on the Web regarding a possible loss of performance as a result of re-using records. I have not found any such articles so far. In my company, we found the contrary. We wrote records to three files and read the records sequentially. Once processed, the records would be deleted. The longer the file (because of deleted records), the longer it would take for the reader program to find the next record to process. When we replaced the file with one re-using deleted records and indexed on a newly created timestamp, performances improved.

Generally though, even if there was a loss of performance due to re-using deleted records, it would be more than offset by the new needs of iSeries sites, having the smallest down time possible. If you end up with many files in need of regular reorganize, the time lost while stopping the system and doing the reorganize may be very long, and unacceptable if your system powers Web sites.

Finally, it is difficult to answer your last question. If you have a file that already has larger number of deleted records, it may be an idea to reorganize it before you change it to re-use delete. Also, if you have files on which many records are created, and only then deleted, re-using deleted records will save you from having to do regular re-organize but between each cycle, the file will still be fairly large.


Search400's targeted search engine: Get relevant information on DB2/400.

The Best Web Links: tips, tutorials and more.

Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.

Dig Deeper on iSeries SQL commands and statements

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.