Re: SQL0443 when inserting a row with an XML column

[CRPence <CRPbottle@xxxxxxxxx>]

  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?