Better research this carefully before making the change...
If I recall correctly, when inserting records into the file performance can
be affected by REUSEDLT(*YES). DB2/400 has to find the records that are
deleted and use them when adding new records to the file. This takes more
time than just adding records to the end of the current file data. Once all
the deleted records have been used, it reverts to adding records to the end
of the file. However, there is no guarantee that all deleted records will
be reused (see excerpt from IBM manual below).
Depending on how many records are in the file, and if the file often has
more deleted records than non-deleted records, it could significantly affect
throughput and program run times. I would suggest running some benchmarks
to get some empirical data in regard to this.
Also, there are also some restrictions on specifying REUSEDLT(*YES) on a
physical file. Here is a excerpt from the AS/400 Database Programming Guide
(SC41-5701), found in the AS/400 Information Center at
http://as400bks.rochester.ibm.com :
REUSEDLT Parameter.
This parameter specifies whether deleted record space should be reused on
subsequent write operations (CRTPF and CHGPF commands). When you specify
*YES for the REUSEDLT parameter, all insert requests on that file try to
reuse deleted record space. Reusing deleted record space allows you to
reclaim space used by deleted records without having to issue a RGZPFM
command. When the CHGPF command is used to change a file to reuse deleted
records, the command could take a long time to run, especially if the file
is large and there are already a lot of deleted records in it. It is
important to note the following:
a.. The term arrival order loses its meaning for a file that reuses
deleted record space. Records are no longer always inserted at the end of
the file when deleted record space is reused.
b.. If a new physical file is created with the reuse deleted record space
attribute and the file is keyed, the FIFO or LIFO access path attribute
cannot be specified for the physical file, nor can any keyed logical file
with the FIFO or LIFO access path attribute be built over the physical file.
c.. You cannot change an existing physical file to reuse deleted record
space if there are any logical files over the physical file that specify
FIFO or LIFO ordering for duplicate keys, or if the physical file has a FIFO
or LIFO duplicate key ordering.
d.. Reusing deleted record space should not be specified for a file that
is processed as a direct file or if the file is processed using relative
record numbers.
REUSEDLT Parameter. When you specify REUSEDLT(*YES) on the Create Physical
File (CRTPF) or Change Physical File (CHGPF) command, the following
operations may work differently:
a.. Arrival order becomes meaningless for a file that reuses deleted
record space. Records might not be added at the end of the file.
b.. End-of-file delay does not work for files that reuse deleted record
space.
c.. Applications that use DDM from a previous release system to a current
release system may get different results when accessing files where deleted
record space is reused.
d.. One hundred percent reuse of deleted record space is not guaranteed. A
file full condition may be reached or the file may be extended even though
deleted record space still exists in the file.
Because of the way the system reuses deleted record space, consider the
following points before creating or changing a file to reuse deleted record
space:
a.. 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 should not reuse deleted record space.
b.. Files used as queues should not reuse deleted record space.
c.. Any files used by applications that assume new record inserts are at
the end of the file should not reuse deleted record space.
If you decide to change an existing physical file to reuse deleted record
space, and there are logical files with access paths with LIFO or FIFO
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 doing the following:
1.. Rename the existing logical file that has the FIFO or LIFO attribute.
2.. 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.
3.. Delete the renamed file.
----- Original Message -----
From: "Rich_Buttenhoff" <
[email protected]>
To: <
[email protected]>
Sent: Wednesday, October 09, 2002 2:33 PM
Subject: F43199 Deleted Records
Hello List,
Has anyone out there set the "Reuse deleted records" parameter on the
F43199 file to *YES?
Our Reorganize Physical File Member process takes way too much downtime.
Are there any other ideas?
Thanks in advance for your assistance.
Rich
===========================================================
JDE World Software A7.3 Cum 12 GL,AR,AP,PO,TA,MN...
AS/400 730 V5R1
Jwalk 3.1C8
===========================================================
Rich Buttenhoff Phone
208)799-4181
Potlatch Corporation Fax
208)799-1687
805 Mill Road Mailto:
[email protected]
Lewiston, ID 83501-1016
http://www.potlatchcorp.com
===========================================================
Walking on water and programming from
specifications are easy, if both are frozen.
===========================================================
--------------------------
Steve Landess
V4R4 A7.3 cume9
EDI
(512) 423-0935
