MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

Re: Reuse Deleted Records ?



fixed

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.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact