Reusing deleted records

Sometimes you might want to reuse deleted records for your database files. In this case, you can use the REUSEDLT parameter.

When you specify REUSEDLT(*YES) on the Create Physical File (CRTPF) or Change Physical File (CHGPF) command, the following operations might work differently:

Arrival order becomes meaningless for a file that reuses deleted record space. Records might not be added at the end of the file.
End-of-file delay does not work for the files that reuse deleted record space.
One hundred percent reuse of deleted record space is not guaranteed. A file full condition might be reached or the file might be extended even though deleted record space still exists in the file.

Note: Because of the way the system reuses deleted record space, the following types of files should not be created or changed to reuse deleted record space:

Files processed using relative record numbers, and files used by an application to determine a relative record number that is used as a key into another file
Files used as queues
Any files used by applications that assume new record insertions are at the end of the file
When DB2® Symmetric Multiprocessing is installed, files on which you expect to have parallel index maintenance performed when rows are updated, inserted, or deleted

If you decide to change an existing physical file to reuse deleted record space, and there are logical files with access paths with first-in-first-out (FIFO) or last-in-first-out (LIFO) duplicate key ordering over the physical file, you can re-create the logical files without the FIFO or LIFO attribute and avoid rebuilding the existing access path by following these steps:

Rename the existing logical file that has the FIFO or LIFO attribute.
Create a second logical file identical to the renamed file except that duplicate key ordering should not be specified for the file. Give the new file the original file name. The new file shares the access path of the renamed file.
Delete the renamed file.