Not real sure how Truncate is different from Clear, but 'DELETE FROM table' will result in a clear if possible.
From the SQL reference...
DELETE Performance: An SQL DELETE statement that does not contain a WHERE clause will delete all rows of a table. In this case, the rows may be deleted using either a clear operation (if not running under commitment control) or a change file operation (if running under commitment control). If running under commitment control, the deletes can still be committed or rolled back. This implementation will be much faster than individually deleting each row, but individual journal entries for each row will not be recorded in the journal. This technique will only be used if all the following are true:
The target table is not a view.
A significant number of rows are 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.
If this technique is successful, the number of increments (see the SIZE keyword on the CHGPF CL command) is set to zero.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----John Yeung <gallium.arsenide@xxxxxxxxx> wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: John Yeung <gallium.arsenide@xxxxxxxxx>
Date: 03/10/2016 10:16AM
Subject: Re: Possible ODBC weirdness when deleting records
On Thu, Mar 10, 2016 at 8:49 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
Personally, I wouldn't worry about rather or not the file ended up deleted
records or not.
I agree with you, in most cases. Basically, if a shop has IBM i
hardware with an appropriate level of storage for their business and
is using SQL, then most likely deleted records are not a big deal. And
the more they use SQL, the less arrival sequence is likely to matter,
and the more they can switch to reusing deleted records, if space is
an issue.
I'd also stick with the SQL delete vs. the CLRPFM. As mentioned in my
other post, the SQL delete will clear the file even with Pseudo-closed
cursors open to it. CLRPFM on the other hand won't.
Well, I'm at a shop that is still only dabbling in SQL. (And I still
don't know what "pseudo-closed" means.) We don't generally reuse
deleted records here, so a file that is very active, but which stays
below the clearing threshold, will grow uncontrollably if all we have
is SQL deletion.
Yes, it's possible to set up periodic deleted record clean-up as a
separate process. But I still think it's nice to be able to really
clear something immediately if that is indeed your intention.
If SQL isn't going to provide a truncate facility, then I feel there
ought to be more sophisticated determination of when to implicitly
clear a file. Like maybe if the total (active plus deleted) records is
over a certain threshold, then attempt to clear. (If this is already
the case and my testing just didn't hit it, then the threshold is too
high.)
John Y.
midrange.com
