Re: Perfromance of SQL DELETE vs clrpfm

["Mark S. Waterbury" <mark.s.waterbury@xxxxxxxxxxxxx>]

 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