Re: SQL0443 when inserting a row with an XML column

On 09 Apr 2013 10:38, Joe Pluta wrote:

On 4/9/2013 12:27 PM, CRPence wrote:

On 09 Apr 2013 09:29, Joe Pluta wrote:

... Used COMMIT(*ALL) on STRSQL. ...

With regard specifically to the above comment, in case this was
unknown or overlooked, the COMMIT() specification for a STRSQL
invocation is ignored if the STRSQL request opens an existing
session instead of creating a new session. The
isolation\commitment-control level must be set within an existing
session using the F13=Services, 1="Change session attributes";
that, or [but IMO not preferable because the request is not
reflected in the session attributes] the "SET TRANSACTION ISOLATION
LEVEL" must be used.

However as I had noted in my prior reply, I would have expected
that a with-isolation clause on the statement should have effected
the same results as either of the above.

Well, that's a kick in the head. Never knew that, and nothing in
the help text reflects that. And guess what? Changing the
commitment control using F13 works at least with the SELECT. This
indicates a bug in the WITH clause - it doesn't override the
session setting. And I seem to recall running across a PTF for that
particular issue but I didn't chase it further since I thought it
didn't apply directly (because I thought I was fixing it with the
COMMIT(*ALL) on the STRSQL command).

Argh. I had long just /assumed/ the help text must have a /note/ about that issue, since long ago... because it was a common issue, often discussed in various fora.

FWiW the condition *is* diagnosed, albeit subtly, via the message SQL6102 issued upon entry into an existing\saved session: the v5r3 text suggests that the "Interactive SQL has found a saved session, and the session started again. This means that all values in effect now are the values that were in effect when the session was saved, along with any previous statements and messages that were entered or sent." IMO the first occurrence of /values/ should say /settings/ to be more clear. And it is unfortunate that there is no "Note:" or even just a sentence in the overall help text, suggesting that all [¿or some?] of the parameters specified will be ignored when a saved session is chosen; possibly even noting the message to look for, to be aware that a saved session was selected.

Of course anyone can submit a reader comment to the InfoCenter documentation, and the update there is the same as that which would be made to the help text; i.e. they are the same source. There is a /Send feedback/ button, which is supposed to be on all pages of the IBM i InfoCenter, to allow making such a request easy. I will not be submitting that one.