Re: Reuse Deleted Records ?

On 01-Mar-2014 14:14 -0800, Jack Kingsley wrote:

If I have the keyword set to *YES, as the file grows with additional
records and then deletions take place should you not be setting this
value as well??:

Max % deleted records allowed (DLTPCT)

The Deleted Percentage setting is independent of the Reuse Deleted
Records (REUSEDLT) parameter setting. The DLTPCT was more desirable in
the past, when there was no ability to reuse deleted rows, because the
only way to recover the space was to reorganize. With REUSEDLT(*YES),
the value of DLTPCT is IMO, significantly debased; at least with the
conventional operating assumption, that a file will eventually refill
with what was deleted.

if so, to what % ??

The value of "Max % deleted records allowed (DLTPCT)" to set for the
file depends entirely on when one would like to be informed of that
percentage being exceeded; i.e. the /maximum/ is not something enforced
such that any particular action is prevented, merely the point at which
being informed of the condition is desirable.

If the file is setup to have NOMAX for the amount of records in it,
the file is going to continue to grow,

The SIZE(*NOMAX) just explicitly avoids inquiries asking to allow
growth. The file can continue to grow, given permission [by reply to
the inquiry messages], irrespective the value for the Size attribute.

but is this to allow faster I/O.

Not sure if the above meant to say "but this is" as a statement, or
"but is this" as a question... But, a file that allows reuse of deleted
records can enable some faster writes to the file for avoidance of
dataspace extents and use of concurrent insert, plus keep more data
contiguous allowing for quicker sequential read access; of course, with
the extra-overhead\added-expense for tracking deletions. The biggest
benefit from REUSEDLT(*YES) however, is the ability to possibly avoid
ever performing another Reorganize Physical File Member (RGZPFM); i.e.
the old-style reorg, now expressed as a request with Allow Cancel
(ALWCANCEL) set to *NO. The newer online reorg [ALWCANCEL(*YES)]
enables avoiding the classic reorganize that required the file and the
access paths were unavailable during the maintenance, irrespective the
REUSEDLT specification.

I have a situation where I had a file overflow an ASP and trying to
see what the values should be when using (reuse deleted records).

The DLTPCT merely asks the database to calculate the percentage of
deleted rows to the number of active rows, during close processing
[rather than during I/O], and merely to inform by a message to the
history log (QHST) when the percentage exceeds the specified value. As
a /maximum/ there is really no effect with regard to the ability of the
file to grow; in number of records, deleted or otherwise. Also, AFaIK
that percentage test is /dumb/, being absolute vs relative; because
obviously 50% of 10 rows with a small record-length is probably not
worthwhile knowing about, whereas a 50% of millions of rows with a
relatively large record-length is probably of worth having been
informed. And [verified still in v5r3] the CPF4653 "DLTPCT parameter
value for member &1 exceeded." is limited to only opens with all I/O
capabilities [e.g. OPNDBF OPTION(*ALL)], thus SQL DML statements like
DELETE, UPDATE, and INSERT which open only for the required capability,
will not exhibit the messaging upon file-close processing.

The DLTPCT is IMO, not really all that relevant to the described
situation. While arguably relevant in some way, mostly tangentially,
yet the overall size of the file is more pertinent. The general
assumption is that the deleted rows are eventually going to be used
again, so the actual number of rows as most representative of the size
is likely more pertinent. Concentrating on the SIZE() is probably a
better option.

I seem to recall file size was a topic of the DB2 for i "health
center"; for sure something about ASPs. Maybe something there will be
of interest.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.