On 10 Sep 2013 10:33, Needles,Stephen J wrote:
We are having an internal debate to reuse deleted records or not.
The most recent info I can find on the subject dates back to V5R4,
we are on V7R1.
Here are a few of the issues that I am aware of REUSEDLT(*YES)
will...
- Cause arrival sequence processing to require attention.
- SQL cursors without an ORDER BY will give data in seemingly random
order according to the SQE's implementation of the request (don't
ask how we were bit by this :))
That is a potential issue irrespective of the choice for the Reuse
Deleted Records option. All SQL SELECT without an ORDER BY must be
presumed to be unordered, because any assumption that there will be any
particular collation may prove false at any time; even from one run to
the next.
- May cause problems concerning access path maintenance, thus
potentially impacting performance (as of V5R4, this did not appear
to be an issue any longer?).
Deleted rows are not part of a keyed access path. I am unsure what
is alluded. Rows being deleted are removed from the keyed access path
the same way, the same maintenance, irrespective of reuse. The reuse
tracking is specific to the DataSpace, nothing to do with the DataSpace
Index [aka AccPth].
- Modernizing the DB to DDL (from DDS) will result in tables that
are REUSEDLT(*YES) by default as the CREATE TABLE SQL does not have
provision for restricting deleted row reuse.
A transition from DDS to DDL is not /modernizing/ except when new
columns or features available only to the SQL are being added
intentionally. Without a full understanding of the effects and the
intention to achieve what the change would offer, the transition would
just be a change for the sake of change; little to no value, with
potentially great costs, arising due to failing to understand the
possibly nuanced effects of having made such a change. Instead, any
/modernizing/ should be effected with a change from RLA to SQL for I\O,
for which the DDS created physical files are completely compatible for
now and into the future.
Any application /modernized/ to use SQL vs RLA has no concept of the
Reuse feature, and will be unaffected [except perhaps for statements
assuming the SQL will effect Arrival Sequence ordering when no ORDER BY
was specified]. A result-set is a set of active rows; any deleted rows
are outside the realm of the SQL, except those that are pending under
isolation and thus the only concern is that they can be rolled back or
committed.
- Does not completely exclude the need for periodic RGZPFM to be run
on the tables.
Yet much more than REUSEDLT(*NO) could, for the same I\O.
REUSEDLT(*YES) can conceivably preclude any requirement ever to
reorganize the data to reclaim the space. That is very improbable for
REUSEDLT(*NO), except in some very specific implementations; e.g. Allow
Delete capability turned off with ALWDLT(*NO).
Typically the number of rows in a file will eventually reach the same
maximum that had been achieved in the past, so reclaiming that storage
will cause the database to have to reallocate that same storage again
eventually, possibly during run-time. Thus a reorganize to reclaim the
deleted row space often is just exercising the CPU for little gain
overall, and possibly causing more work during run-time than would have
transpired had the reorganize request had not been done. Note that
there is the /online/ reorganize feature as part of the RGZPFM, which
can limit both the offline-time for the data and greater CPU
requirements; i.e. using the Allow Cancel (ALWCANCEL) enabled.
The question is... with enhancements to the SQE in V7R1 and such...
will REUSEDLT(*YES) provide a significant lessening of the need for
RGZPFM (perhaps annually or monthly versus weekly on select tables)
without a performance problem over time?
Except in bulk deletes and a need [not just some pedantic desire] to
recover the storage, the gains from periodic reorganize requests is
unlikely to be that great. I am not aware of any specific enhancements
that makes any difference since the capability to reuse deleted rows
became available. The capability should have enabled reduction of
RGZPFM usage to nearly never, if the sole purpose was to reclaim space
for the deleted rows. If the reorg requests were to order the data,
then the Arrival Sequence was almost surely not an issue with such
files, so changing to reuse deleted should not matter.
The only value in keeping REUSEDLT(*NO) is to maintain compatibility
for those /legacy applications/ that have a built-in dependence on the
Arrival Sequence Access Path being truly representative of what that
naming applies; i.e. such that when Sequential Access methods are
requested against the file data, along with the assumption that Arrival
Order is guaranteed. The CRTPF would have changed the default to enable
reuse, but that would have broken any applications that had been created
in the past, whereby its assumptions made included physical ordering;
definitely not something that could have been changed with just a
notation in the Memo to Users... but most systems would do well to
customize their systems with CHGCMDDFT CRTPF 'REUSEDLT(*YES)' after
ensuring any applications issuing CRTPF and depending on physical order
have been updated to explicitly request REUSEDLT(*NO).
midrange.com
