Re: Drop Case API

CRPence wrote:

As I recall REXX SQL runs by default with isolation [there was no SET ISOLATION LEVEL statement in the source]. I wondered if maybe the process was functional, but for lack of a coded COMMIT, then a COMMIT or ROLLBACK must have been issued from the command line in testing it? If it was not under isolation, then one would expect that the coded ROLLBACK is flawed.? For having a ROLLBACK on error but no COMMIT coded for success, that is what led to my posting this inquiry. But additionally I wondered, can a SELECT done FOR UPDATE and WITH NC really allow for the UPDATE WHERE CURRENT OF under isolation? I have never tried nor done that. I suppose as dynamically scrollable, the data is selected or omitted dynamically to avoid any concern for changing data; and of course the specific row from the FETCH is locked to prevent any change by another request.

Hey, Chuck...

Yes, it was indeed late Saturday when I put that together. The "Drop Case" stuff was wrapped in fairly generic SQL rather than SQL that was explicit for the task; and it started as SQL copied from a different procedure, then (significantly) modified for the new usage. The ROLLBACK probably should have been cut out, but I made the mistake of stopping when the proc worked rather than going all the way to 'production quality'.

The biggest flaw for me in that sense was that I didn't enable journaling on that table when I created it here. It was, after all, just a test example; so, I skimped.

However, I did indeed run it multiple times and it did work, updating the columns as intended. At that point, I was more interested in going to bed than ironing out the dumb parts.

As it was, I probably would have finished it faster if I _had_ enabled journaling. If nothing else, I could have coded a COMMIT while relying on the ROLLBACK to put things back when things went wrong.

But once it worked, I talked myself into sleep.

Here is a PTF for a ROLLBACK for a WITH NC cursor combined with other work under isolation:
http://www-01.ibm.com/support/docview.wss?uid=nas3b38d4626399ff2a886257528005d7a4c

Handy... Thanks!

FWiW: If isolation is expected, the setup does not include any noted requirement for establishing logging [journaling] for the TABLE which is required to enable isolation [commitment control].

Ummm... What? Is that in relation to the PTF or is it more general?

That is, can you say that once more with different phrasing? I'm probably reading this too late at night once again, but it feels like I'm missing something with that.

Also FWiW: I believe the use of RC to reflect the SQL activity is not going to be generally functional as coded & commented. That is, I believe a SavedRC=RC would need to be used after each SQL and then exit using the SavedRC value, since the RC as utilized could reflect the Return Code for activity which was post-SQL.?

Yeah, that's a good point that's probably true in most cases. RC should be captured immediately at the point of error and held apart from subsequent operations before being used for any kind of exit value (or _any_ later purpose). Likelihood of contamination is too high.

Tom Liotta

Tom Liotta wrote:

<<SNIP>>

It's the SQL that created a small table for testing plus a REXX proc that loops through the table (using SQL) setting a VARCHAR column to Drop Case.

It's a bit more complex than just the small part that does the Drop Case by itself. But you already have your RPG solution, so I might as well go the extra distance.