Re: SQL0443 when inserting a row with an XML column

Joe,

Are you trying to archive a RAW XML file into the DB ?

Regards,
Richard Schoen
------------------------------

message: 7
date: Tue, 09 Apr 2013 11:29:25 -0500
from: Joe Pluta <lists@xxxxxxxxxxxxxxxxx>
subject: SQL0443 when inserting a row with an XML column

Actually, that is the initial problem, but as I tried more things, I
found more problems:

1. Can't insert a CLOB column from IFS
2. Can't select a CLOB column from IFS

Here's an example:

select cast ( get_clob_from_file('/myfiles/sample_001.xml')
as varchar(20000)) ifsfile from sysibm/sysdummy1

Whatever I do, all of the above actions eventually fail with this as the
low-level error:

Message ID . . . . . . : CPF503E
Date sent . . . . . . : 04/09/13 Time sent . . . . . . :
11:23:23

Message . . . . : User-defined function error on member SYSDUMMY1.

Cause . . . . . : An error occurred while invoking user-defined function
GET_CLOB_FROM_FILE in library QSYS2. The error occurred while
invoking the
associated external program or service program QDBSSUDF2 in library
QSYS,
program entry point or external name QSQGTCF, specific name
GET_CLOB_FROM_FILE_1. The error occurred on member SYSDUMMY1 file
SYSDUMMY1
in library SYSIBM. The error code is 1. The error codes and their
meanings
follow:
1 -- The external program or service program returned SQLSTATE
42926. The
text message returned from the program is: LOB and XML locators are not
allowed with COMMIT(*NONE). .
More...

Argh.

I have done the following, either individually or in combination: used
"with CS" or "with UR" on the updates and selects. Executed STRCMTCTL
before firing up STRSQL. Used COMMIT(*ALL) on STRSQL. I've even started
journaling on SYSDUMMY1. All to no avail.

Is anyone else running into this?

Joe