Re: TRUNCATE TABLE on V7R2 sometimes fails

On Fri, Jul 16, 2021 at 4:15 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Patrik, did you ever find out anything more about this? I am fascinated too, because it happens on our 7.3 system.

Nope. I was resolving to add a "if there was an error, try DELETE FROM" after all truncates.

What's kind of interesting is something I learned relatively recently:
if you are deleting a sufficiently large number of records using
DELETE FROM (with no WHERE clause), then the system actually tries to
clear the file (equivalent to, or perhaps even identical to, CLRPFM).
For you, with only a few hundred records per file, you're definitely
below the threshold for clearing. (In my experiments, I never saw the
clearing behavior for less than 1000 records.)

Most annoyingly, truncating an empty table is also considered as an error condition. Note that I'm using ODBC from Linux to access the databases, and the (to me) only reliable way to tell if there was an error is to not look at affected row count, or return value, but see if the Error-method from Perl-DBI returns some text.

I was so happy when TRUNCATE was added, but in practice, it virtually
never works for me. Obviously it doesn't (and shouldn't) work if the
file is locked. But even apart from that, it feels like every time I
try to truncate, it silently fails. I am also using ODBC (from either
Windows or PASE). I can't remember the last time TRUNCATE worked for
me, if indeed it ever did.

For me, the only *truly* reliable way to know if a truncate worked is
to query the file to see if it is indeed empty.

John Y.