Re: Possible ODBC weirdness when deleting records

Glad you figured it out...that's an interesting bit of triva..

Personally, I wouldn't worry about rather or not the file ended up deleted
records or not.

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.

Charles


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

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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.