Re: Splitting a big file into smaller files

On 17-Apr-2015 16:26 -0500, Glenn Gundermann wrote:

Using the DELETE command doesn't necessarily mean the system will do
a FAST DELETE, a.k.a. RGZPFM. It might just delete all the records.
Resetting the RRN will then depend on whether you have REUSE DELETED
RECORDS. If you DSPFD on the table, what does it show for REUSE
DELETED RECORDS and TOTAL DELETED RECORDS?

Just to clarify [I realize Glenn knows, but for any readers of the archive]:

The original fast-delete feature is essentially the effect matching Clear Physical File Member (CLRPFM) and is limited to DELETE FROM the_file statement on which there is [either a WHERE clause that is known to delete all rows or] no WHERE clause; so more nearly "aka CLRPFM", but far from "aka RGZPFM" :-)

And IIRC, there was added a new\different variant, a newer "fast-delete" feature that is an effective ALTER TABLE [implemented-as], but for which only a subset of the data would be included in the /altered/ table; i.e. for a request to DELETE FROM the_file WHERE X=Y the alter variant would perform an ALTER with no effected changes but the data-copy would effect INSERT INTO the_file SELECT * FROM renamed_original_the_file WHERE NOT (X=Y) if the predicates on the WHERE clause were deemed to be inclusive of both a large number of rows and a large percentage of the rows. Necessarily, the /copy/ would have no deleted rows in this fast-delete path, because SELECT can only include active rows.

FWiW: The Reorganize Physical File Member (RGZPFM) presumably could be utilized after the alluded [lacking an actual script, difficult to know for sure] DELETE that precedes the effective /copy/ via (CREATE TABLE ... AS ... SELECT * FROM ... AS A WHERE RRN(A) BETWEEN ...), to ensure that any inactive rows were purged; i.e. if the original fast-delete was not the effect for the scripted DELETE. But conspicuously, replacing the DELETE with the CL CLRPFM would be ideal if the script need not run on another database... but that is an unlikely requirement, because any other database will likely have the same potential issue for inactive vs active rows after the DELETE [despite possibly just not yet encountered].