Re: TRUNCATE TABLE on V7R2 sometimes fails

Hello John,

Am 17.07.2021 um 03:38 schrieb John Yeung <gallium.arsenide@xxxxxxxxx>:

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).

I wonder which release introduced this clever behavior. It's not there with V4R5, deleting some 1000 rows takes some 10 seconds on my 150.

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.)

*Should* the OS not be clever enough to instead use the percentage but a fixed number for this decision?

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.

Honestly, I never did care too much how often it works or fails. :-)

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.

FYI: My comment from above does not only apply to truncates, but for any database operation, via ODBC, from Perl-DBI/Linux, with always the newest iACS ODBC drivers for Linux installed.

:wq! PoC