Topic
5 replies Latest Post - ‏2012-09-12T22:50:32Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic xmlparse/xmltable in db2 v6.1 on i?

‏2012-09-12T13:08:30Z |
Techies--
I'm getting sql0204 XML in *LIBL type *SQLUDT not found on an i db2 6.1 install when I try to deploy a stored proc I know works on Linux v9.7. The reason I am attempting to get this to work is because I really need to pass a table (or array) variable. I couldn't find a way to send a multi-dim array to a sproc on the 6.1 v of i, so I thought I'd try getting around that with an xml doc. But that failed too... Does anyone have any advice for me on how to solve this issue?

Here's the sproc that works on v9.7,Linux:



CREATE PROCEDURE HCMDEV.EMP_MULTIPLE_XML (IN DOC XML)
DYNAMIC RESULT SETS 1
READS SQL DATA
LANGUAGE SQL SPECIFIC EMP_MULTIPLE_XML

P1: BEGIN

DECLARE CSR1 CURSOR WITH RETURN FOR
SELECT emp.EMPID,
emp.FIRSTNAME,
emp.LASTNAME,
emp.DIVISION,
emp.DISTRICT,
emp.LOCATION,
emp.OPERATIONALAREA,
emp.TERMDATE,
emp.REHIREDATE,
emp.HIREDATE,
emp.ADDRESSLINE1,
emp.ADDRESSLINE2,
emp.CITY,
emp.STATE,
emp.ZIPCODE,
emp.TELEPHONE1,
emp.POSITIONCODE,
emp.POSITIONTITLE,
emp.HIRECODE
FROM HCMDEV.EMPLOYEE emp
WHERE EMP.EMPID IN
(SELECT X.EMPID
FROM XMLTABLE('$d/EMPLOYEE/EMPID' PASSING DOC AS "d" COLUMNS EMPID CHAR(9) PATH '.') AS X);
OPEN CSR1;
END P1



Kind regards,Paula DiTallo
plditallo@ieee.org
Updated on 2012-09-12T22:50:32Z at 2012-09-12T22:50:32Z by SystemAdmin
  • NickLawrence
    NickLawrence
    47 Posts
    ACCEPTED ANSWER

    Re: xmlparse/xmltable in db2 v6.1 on i?

    ‏2012-09-12T15:15:11Z  in response to SystemAdmin
    If you need XMLTABLE, you'll need DB2 for i 7.1. The XML support was a new feature in 7.1.

    Arrays are supported for SQL procedures in 7.1 as well.

    http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzintro.htm

    From what you describe, a temporary table might give you what you need.
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: xmlparse/xmltable in db2 v6.1 on i?

      ‏2012-09-12T16:46:09Z  in response to NickLawrence
      Nick--
      Thanks for replying.

      Here's my compound problem to solve. DB2 is the source data that I'm accessing via a SQL Server linked object. I'm trying to compensate for the poor performance on distributed queries by shipping key data over to filter through on the DB2 backend.

      In the case of the EMPLOYEE view, the EMPID will work to do this. If I send 30 EMPIDs back to DB2 for selecting in an xml doc, I'd only be getting back 30 out of 10s of thousands of possible EMPLOYEES, thus making the subquery select locally on the SQL Server side much faster.

      Is there any feature at all available on i v6.1 to do this? Unfortunately, upgrading to v7.1 isn't on the list due to a vendor-related issue.

      In this context, I don't see how a temp table would work out.

      Kind regards,
      Paula DiTalloplditallo@ieee.org
      • SystemAdmin
        SystemAdmin
        3129 Posts
        ACCEPTED ANSWER

        Re: xmlparse/xmltable in db2 v6.1 on i?

        ‏2012-09-12T17:18:35Z  in response to SystemAdmin
        I just downloaded a general db2 v6r2 pdf file -- I see this version of db2 i has been out for several years now. There seems to be some xml search capability, but very limited by comparison to the v9.7 I have available on the Linux version.

        Does anyone know if I can pass a clob datatype as a parameter? If so, I suppose there isn't anything keeping me from using it as an xml document. The next question, can I rely on the trim, ltrim,osstr, etc. functions to be able to parse out the clob within the body of the sproc?

        Kind regards,
        Paula DiTallo

        plditallo@ieee.org
  • NickLawrence
    NickLawrence
    47 Posts
    ACCEPTED ANSWER

    xmlparse/xmltable in db2 v6.1 on i?

    ‏2012-09-12T19:19:56Z  in response to SystemAdmin
    The native XML type does not exist in V6R1.

    At 6.1, you would have to work with an XML document as serialized character data (CLOB or BLOB is most common, VARCHAR or VARBINARY might be OK if the serialized data is known to be not too big)

    A binary type offers the advantage of avoiding issues where the character set of the character data is unintentionally converted to a character set that no longer matches the encoding declaration in the XML document.

    If you represent XML data in DB2 as character (or binary), any character (or binary) operation is legal - DB2 has no knowledge that this value represents an XML document and will not stop you from doing non-XML (usually bad) operations on it. But I would not recommend this approach as general solution. If you do this, you would be better off with something like a comma separated list.

    Character operations on serialized XML documents are not a good way to extract data. These operations cannot leverage the relationships defined by the XML document. In addition, they will not convert XML values into SQL values, and this will cause problems even when all of the data is type xs:string in the XML document; when an XML document is serialized to a text data type, certain characters will be escaped. (For example '<'s a whole different topic.

    There is an XML Extender option for the DB2 Extenders (5761-DE1) product that contains table functions for extracting data from an XML document. However this is an additional charge product, and the functionality is not as standardized or well integrated as XMLTABLE. Given the new XML function available in DB2 for i 7.1, the XML Extender option is not going to be the recommended solution for XML on DB2 for i 7.1.

    The simplest solution might be to write a user defined table function in an external language to extract the XML data and return it to DB2. Most host languages (Java, RPG, C++) either have XML parsing capabilities built into the language - or have toolkits available to do this.

    Passing a CLOB or BLOB to a function or procedure should not be an issue.

    A few Google searches will probably provide you with some articles on the subject with examples. I see IT jungle has an article that sounds like what you are looking for, although I have not read them and I cannot make claims to their accuracy.
    http://www.itjungle.com/fhg/fhg100709-story02.html
    http://www.itjungle.com/fhg/fhg102109-printer02.html
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: xmlparse/xmltable in db2 v6.1 on i?

      ‏2012-09-12T22:50:32Z  in response to NickLawrence
      Nick,
      Thanks for all of your guidance. I'll see what I can do with comma separated values and the clob datatype.

      Kind regards,
      Paula DiTallo
      plditallo@ieee.org