RE: Perfromance of SQL DELETE vs clrpfm

Richard,

This is one of those "it depends" issues. In more recent releases
(V5R3+), IBM added a "fast delete" method that mimics the CLRPFM
approach to deleting all rows in a file. I know there are some QAQQINI
settings that define how fast delete works, but not certain what
documentation might explain this in more detail.

9.3.3 Fast Delete support

As developers move from native I/O to embedded SQL, they often wonder
why a Clear
Physical File Member (CLRPFM) command is faster than the SQL equivalent
of DELETE
FROM table. The reason is that the SQL DELETE statement deletes a single
row at a time. In
i5/OS V5R3, DB2 Universal Database for iSeries has been enhanced with
new techniques to
speed up processing when every row in the table is deleted.
If the DELETE statement is not run under commitment control and no WHERE
clause is
specified, then DB2 Universal Database for iSeries uses the CLRPFM
operation underneath
the covers.

If the Delete is performed with commitment control and no WHERE clause
is specified, then
DB2 Universal Database for iSeries can use a new method that is faster
than the old delete
one row at a time approach. The deletions can still be committed or
rolled back, but individual
journal entries for each row are not recorded in the journal. This
technique is only used if all
of the following statements are true:

 The target table is not a view.
 A significant number of rows is being deleted.
 The job issuing the DELETE statement does not have an open cursor on
the file (not
including pseudo-closed SQL cursors).
 No other job has a lock on the table.
 The table does not have an active delete trigger.
 The table is not the parent in a referential constraint with a
CASCADE, SET NULL, or SET
DEFAULT delete rule.
 The user issuing the DELETE statement has *OBJMGT or *OBJALTER system
authority
on the table in addition to the DELETE privilege.

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Reeve
Sent: Thursday, October 14, 2010 10:10 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Perfromance of SQL DELETE vs clrpfm

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