Re: Possible ODBC weirdness when deleting records

On Wed, Mar 9, 2016 at 6:03 PM, John Yeung <gallium.arsenide@xxxxxxxxx> wrote:

Hm... have to check at least one more thing out - Charles mentioned this:

A significant number of rows are being deleted.

That was one of the required conditions for the clear to be attempted.
But these are small files that I'm looking at. The clearable one has
always had a bit more records than the others, so maybe I'm near the
boundary of what "a significant number" is. Trying to test that
next....

That appears to be it!

I don't know all the factors that might go into determining what
constitutes "significant", but for these files I've been testing, on
this system, it's 1000 records. If the file already has at least that
many *undeleted* records, then the file gets cleared if the required
lock can be secured. This is true of any of the files.

I did notice that the deleted records which are still hanging around
don't seem to figure into it. I haven't tested this exhaustively, but
it looks as though I could keep indefinitely adding and deleting
records, and as long as the number of *active* records stays below
1000, the number of deleted records will continue to grow.

So, I guess a silly, convoluted trick for trying to coerce SQL into
clearing a file is to first add 1000 records, then do the delete. (The
only reason this even entered my mind is that I had originally wanted
to use QCMDEXC to issue a proper CLRPFM, but I got the dreaded
"SQL0901 - SQL system error. (-901)". On another LPAR, QCMDEXC works
normally. It would be handy if IBM added a truncate or clear SQL
statement to DB2 for i.)

Thank you to all who engaged in this brainstorming exercise!

John Y.