Re: SQL0443 when inserting a row with an XML column

On 4/9/2013 12:38 PM, 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).

So now I can start moving back up the chain.

As always, Chuck, your insight is unparalleled. Thank you.

And as the denouement, magically EVERYTHING starts working. The inserts work perfectly both into XML and CLOB type fields and the WITH clause is unnecessary (because I have COMMIT(*ALL) set in the session attribute using F13).

I only hope this thread saves someone else the couple of days I lost. :)

Joe