Reuse Deleted Records? *YES!

How does the iSeries find a space to insert a new record over a deleted record when REUSEDLT(*YES) is specified for a file? An associate says that the file is scanned sequentially for a deleted record when writing a new record to the file and as a result there is a significant performance penalty.

–Millard

I sent your question to Kent Milligan of IBM. According to him, “A table scan is not performed. A bitmap-like structure is used to identify available deleted rows within the table.”

So, Millard, there are no performance-related reasons not to reuse deleted records. This topic reminds me of email I got in response to another article, “Alternatives to Clear Physical File Member.”

Hey, Ted:
Is there any reason you didn’t suggest having the file reuse deleted records instead of having to clear it nightly?
–Jim

Hey, Ted:
I suggest the reader set the file to reuse deleted records.

CHGPF REUSEDLT(*YES)

Then the file won’t have to be cleared or reorganized at all, and thus won’t need to be cleared or reorganized, unless one day there are a huge number of transactions and disk space is at a premium. That’s not a likely problem to have, since disk is cheap. Just add and delete, and the system, as usual, takes care of itself.
Regards, Hawk

To answer Jim’s question, I know there’s a lot of old code out that that precedes REUSELDT(*YES), so I just didn’t think of it when I was writing it up.

Devoted reader Vern also had a good suggestion. He wrote:

SQL’s DELETE FROM LIB/FILE gets rid of everything. I’m not sure whether it requires the exclusive lock of CLRPFM.
–Vern

I gave it a quick test. While one job was displaying a file through the RUNQRY command, I tried CLRPFM from a second job. No good. Then I cranked up SQL in the second job and ran the DELETE command. That worked. However, the two functions are not identical. After CLRPFM completes, the file is empty and there are no deleted records. After DELETE, the file is empty, but there are deleted records.

As usual, multiple methods exist for removing the outer covering of a feline animal. And it appears that the only time it’s not appropriate to reuse deleted records is when arrival sequence has some meaning.

Thanks to Kent Milligan for answering Millard’s question.

–Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot