y employer has just spun off our product testing facility into an independent organization, able to seek other clients. They will have their own, independent data systems but background data must be sent and test results must be retrieved to our core system with minimal effort.
XML is a logical choice for the exchange and is supported by the group developing for the new company.
Now I need to export data and accept it back in XML; I don’t have full control over the data formats, and I have precious little time.
XML Extender to the rescue!
I need to exchange data between an existing DB2 database and another party, using XML, with minimal development.
Use DB2’s XML Extender to translate between XML and relational data in DB2.
IBM delivers DB2’s XML support in the form of an “Extender”?a kind of privileged add-on that integrates closely with the core database. The Extender includes indexing capabilities and adds new data types and functions.
XML Extender for DB2 v7.2 is available for download. DB2 v8.1 includes the XML Extender in the regular installation kits, and it installs simply as an optional component. (See the “Getting Started” sidebar for more information on installing the XML Extender.)
|What You Need|
|DB2 v8.1, or v7.2 with XML Extender. Based on Window 2000 install with DB2 v8.1.|
There are tutorials for using the Extender in online documentation and on Web sites such as IBM’s Developer Domain. I won’t attempt to reproduce or better them, but I can give you a quick rundown on what to expect from DB2 with XML.
Before you begin working on any DB2/XML solution, you need to figure out what your real requirements are.
Table 1: XML vs. Relational Options in DB2
Translate relational data to XML
“Shred” (translate) XML to relational data
Compose Yourself (Translating to XML)
select rec2xml(1.0, 'COLATTVAL','MyRow',DEPTNO, DEPTNAME, MGRNO) AS MYROWSFROM DEPARTMENTMYROWS--------------------------------------------------------------------
Similarly, there are functions to return single elements (XMLELEMENT), group elements as XML attributes (XMLATTRIBUTES), and aggregate elements in a hierarchy (XMLAGG). Listing 1 is an example of aggregated data from DB2 functions, copied from the online documentation.
Whole Documents, with Help from DAD
DB2 documentation describes how to write a DAD, but getting the content right is tedious. Thankfully, there are some graphical tools for the task (see the “Tools for the Job” sidebar).
Reviewing our options from Table 1, we will consider the two XML “Collection” types:
SQL Collection Mapping is the simpler choice. It only works when you can collect all relevant data with a single SELECT statement, which is placed in the DAD. If multiple tables are involved you should use primary-foreign key relationships.
RDB_Node Collection Mapping is more flexible. It allows node definitions for multiple tables, optional columns, and optional conditions. All involved tables must have primary keys. This approach does not require SQL in the DAD. It does allow imports as well as exports, and you can create a field on each table to preserve the order of data when it appears in XML.
Listing 2 shows a sample DAD.
Once you have defined a DAD, creating XML is as easy as calling a single stored procedure, dxxGenXML:
dxxGenXML(CLOB(100K) DAD, /* input */ char(resultTabName) resultTabName, /* input */ integer overrideType /* input */ varchar(1024) override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */
dxxGenXML generates an XML document according to the given DAD and places the result in a table you specify when calling the routine.dxxGenXMLClob is identical except that it returns the XML directly as a Character Large Object.
In DB2 v8.1, you can now call a stored procedure directly from a DB2 command prompt. For added convenience, the extender provides an external command-line program to call these procedures. (See dxxAdm in the “Tools for the Job” sidebar.)
If you will be composing or decomposing XML documents frequently, it is worth your time to create a named collection using the dxxEnableCollection stored procedure. This procedure stores the DAD within the database, and you can then refer to it by name using dxxRetrieveXML and dxxRetrieveXMLClob, which is otherwise identical to dxxGenXML.
dxxRetrieveXML(char(collectionName) collectionName, /* input */ char(resultTabName) resultTabName, /* input */ integer overrideType, /* input */ varchar_value override, /* input */ integer maxRows, /* input */ integer numRows, /* output */ long returnCode, /* output */ varchar(1024) returnMsg) /* output */
You can call these procedures from your own code or by using the dxxAdm tool. Also see some of the “glue” utilities in “Filling in the Gaps.”
Taking It In (XML into DB2)