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
DECLARE CSR1 CURSOR WITH RETURN FOR
FROM HCMDEV.EMPLOYEE emp
WHERE EMP.EMPID IN
FROM XMLTABLE('$d/EMPLOYEE/EMPID' PASSING DOC AS "d" COLUMNS EMPID CHAR(9) PATH '.') AS X);
Kind regards,Paula DiTallo
This topic has been locked.
5 replies Latest Post - 2012-09-12T22:50:32Z by SystemAdmin
Pinned topic xmlparse/xmltable in db2 v6.1 on i?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-09-12T22:50:32Z at 2012-09-12T22:50:32Z by SystemAdmin
NickLawrence 060000949J52 PostsACCEPTED ANSWER
Re: xmlparse/xmltable in db2 v6.1 on i?2012-09-12T15:15:11Z in response to SystemAdminIf 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.
From what you describe, a temporary table might give you what you need.
Re: xmlparse/xmltable in db2 v6.1 on i?2012-09-12T16:46:09Z in response to NickLawrenceNick--
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.
Re: xmlparse/xmltable in db2 v6.1 on i?2012-09-12T17:18:35Z in response to SystemAdminI 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?
NickLawrence 060000949J52 PostsACCEPTED ANSWER
xmlparse/xmltable in db2 v6.1 on i?2012-09-12T19:19:56Z in response to SystemAdminThe 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.