Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Location
    Zagreb, Croatia
    Posts
    11

    Unanswered: DB2 z/OS storede procedure - IN parameter XML

    Hello,
    we are developing stored procedures that should have IN parameter XML, but this doesn't work - we get SQLCODE = -20060, ERROR: UNSUPPORTED DATA TYPE XML ENCOUNTERED IN SQL PARAMETER. In book we found that VARCHAR could be used instead of XML, but VARCHAR has limit of 32000 bytes and our XMLs are larger than this (we want to store this XML from IN parameter into table with XML column).
    Does anyone have any experience with this, how to handle this, using CLOBs...?


    Thanks, Ivana

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I've used CLOBs to pass XML docs in and out of external z/OS stored procs coded in COBOL. You should be able to insert an XML doc passed to a stored proc as a CLOB input parm into an XML column type. Be aware that site specific ASCII/EBCDIC translations could introduce potentially damaging character translations when passing data from a different platform. At one site we had problems with square brackets, [ ]. You might not have any problems, but it's something to keep in mind when using CLOBs to pass XML docs.

  3. #3
    Join Date
    Nov 2012
    Location
    Zagreb, Croatia
    Posts
    11
    Thank you for your reply.
    We have now changed IN parameter into CLOB(2G) because we have to insert very large XML files.
    We create and test our stored procedures in IBM Data Studio.
    When we test our procedures some XML files reenter into our tables without any problem but for some we get this error (independently on its size):
    ERROR ENCOUNTERED DURING XML PARSING AT LOCATION 207194 An incorrect character was found in an attribute vRC=000C,RSN=3. SQLCODE=-20398, SQLSTATE=2200M, DRIVER=4.13.111
    Run of routine failed.
    - Roll back completed successfully.
    XML file is validate, there shouldn't be any errors in it. Do yyou know how to locate which character is incorrect, or any other way how to solve this problem?

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by IvanaB View Post
    ERROR ENCOUNTERED DURING XML PARSING AT LOCATION 207194 An incorrect character was found in an attribute vRC=000C,RSN=3. SQLCODE=-20398, SQLSTATE=2200M, DRIVER=4.13.111
    "LOCATION 207194" is the byte position of the incorrect character.
    So take substr(substr(the_data, 207194),1,1) to see the offending character.

    Make sure that the CCSID of the client is set correctly, also in DB2 Connect.
    Most likely, this is a codepage issue.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Nov 2012
    Location
    Zagreb, Croatia
    Posts
    11
    Thank you for your reply.
    I can't take SUBSTR(....) because this data hasn't entered my table. I suppose you meant to do this by SQL statement?
    CCSID of the client --> Data Studio Client? Can you tell me where to set this?

    Thanks,
    Ivana

  6. #6
    Join Date
    May 2011
    Location
    San Jose, CA, USA
    Posts
    4
    If you insert the XML through a stored procedure then you may want to code an SQL exception handler that inserts the XML into your own exception table with a CLOB column in case the insert into the XML column fails for some reason. Then you can analyze the document (e.g. with substr, etc. ) later on.

    Note that an XML document must only contain characters that correspond to any of the following Unicode code points: #x9 , #xA , #xD , #x20 to #xD7FF , #xE000 to #xFFFD , and #x10000 to #x10FFFF. Any other characters outside of these ranges must not appear in an XML document, or else parsing of the document will fail.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •