Re: SQL0443 when inserting a row with an XML column

What was attempted for resolution prior to calling a service provider seems thorough. I do not see anything wrong with the SELECT, and would expect that a with-isolation clause [other than NC] should resolve the issue being diagnosed. Perhaps now is the time to ask the service provider.

FWiW: The noted sqlstate is the sqlcode -7034 which is the message identifier SQL7034 and symptom msgSQL7034. Knowing that can be helpful in searches; e.g. for any known\reported defects. A quick web search [not a search of the IBM defects and KB] turned up several hits with the symptom keyword.

FWiW: The CPF503E is somewhat generic, and whatever is the database file being opened to implement the request is diagnosed as file on which the error occurred; i.e. the reference to the SYSDUMMY1 is really not germane. To eliminate the explicit file name reference to the one-row table, the SELECT can be rewritten using a values-clause to produce a values-row [albeit the implementation could still be with the one-row table QSQPTABL; and thus the error reporting that name, or I suppose a temporary file name like *QUERY#####... well, until the issue is resolved]:

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

Regards, Chuck

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

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).

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?