Re: Perfromance of SQL DELETE vs clrpfm

Richard:

CLRPFM deletes the data space for a physical file member and replaces it with a new empty one, and invalidates/clears any access paths (indexes) over this PF. CLRPFM is not allowed if any "delete" triggers or referential constraints are defined for the file.

Running an SQL delete will actually delete each row, row by row, and that will incur additional overhead to update each unique access path (or index) for any keys maintained in the PF or any LFs over that PF, for each record deleted. If there are any "constraints" defined, that will also incur extra overhead during the delete (e.g. "cascading deletes"). Also, if any delete triggers are defined for this PF, those triggers will "fire" for each and every row deleted. Also, if the file is defined with REUSEDLT(*NO), you will have a bunch of deleted records taking up disk space, until you run a RGZPFM.

Does that help?

All the best,

Mark S. Waterbury

> On 10/14/2010 11:09 AM, Richard Reeve wrote:

I just came across a program(CL) that uses the delete function within SQL to
clear a file and that made me wonder if it is more efficient to do a CLRPFM or
if the SQL delete would be as efficient.

Any thoughts/experiences?

Warmest Regards,

Richard Reeve