How to migrate from the XML Extender to DB2 pureXML
June 29, 2012
The XML Extender was introduced in DB2 7.1 (yes, that long ago!) to enable applications to store, query, update, construct, and validate XML documents in DB2 for Linux, UNIX, Windows and DB2 for z/OS.
In 2006, DB2 9.1 introduced pureXML – a set of native XML features that are deeply integrated into the DB2 engine. DB2 pureXML is vastly superior over the XML Extender, in both functionality and performance.
The XML Extender is still available in DB2 9.1 and 9.5 but has been discontinued in DB2 9.7. Hence, if you are using the XML Extender and want to move to DB2 9.7 or DB2 10, you need to migrate your XML application to DB2 pureXML.
Although the migration from the XML Extender to pureXML requires some considerations, it is not very hard. The migration to pureXML yields dramatic performance benefits and often simplifies your solution. You will need to migrate eventually, and the sooner you do it the earlier you will reap the benefits of significantly better performance and greater flexibility.
The details of the migration depend very much on which parts of the XML Extender functionality you are using, such as:
- Shredding XML to relational tables (called “XML collection” in XML Extender lingo)
- Construction of XML documents from relational tables
- Storage of XML documents in db2xml.xmlclob columns
- with or without side tables?
- extraction of values with extract UDFs?
- XML updates with the update UDF?
- XML validations against DTDs or XML Schemas
- Other XML Extender features such as XSLT transformations or interaction with Websphere MQ message queues.
Let’s briefly discuss these one by one:
1. Shredding XML to relational tables
If you are using the XML Extender to shred XML into a set of relational tables, e.g. with the db2xml.dxxInsertXML stored procedure, you have two options:
(a) Consider whether you can avoid the shredding and instead store and query your XML documents in a column of type XML. Inserting documents into an XML column is significantly faster than shredding, and you can build indexes and express XML queries on XML columns.
(b) DB2 pureXML still offers shredding capabilities: you can use the XMLTABLE function or annotated XML Schemas to convert XML to relational format. These options are typically 5x to 8x faster than the old XML Extender shredding and still make the data available in same relational tables as before so that consuming applications don’t need any changes.
For more details on shredding in DB2 9.1 or higher, see the free sample Chapter of the DB2 pureXML Cookbook. That chapter also discusses the pros and cons of XML columns vs shredding, which is also examined in a recent developerWorks article.
If you choose to continue to shred XML into relational tables, note that shredding with the XMLTABLE function can sometimes be simpler and more flexible than using annotated XML Schemas. However, if you have a large number of XML Extender DADs, you might benefit from a free tool that converts DADs to annotated XML Schemas. This tool is available for download at the bottom of the article “From DAD to annotated XML schema decomposition“.
2. Constructing XML from relational tables
In DB2 9.1 and higher, construction of XML documents from relational tables is performed with SQL/XML construction functions that are part of the SQL standard. These functions include:
As part the migration you would write SQL statements (or stored procedures, if you prefer) that read relational data and produce XML with these functions.
3. Storage of XML documents in db2xml.xmlclob columns
If are using the XML Extender to store XML in db2xml.xmlclob or db2xml.xmlvarchar columns, you will migrate these columns to native XML columns. A previous blog post describes how to move data from a CLOB column to an XML column.
If you have used so-called side tables with your db2xml.xmlclob columns, you should replace those with native XML indexes. Note that DB2 10 contains some great XML index enhancements. Using XML indexes instead of side tables is simpler, more flexible, and provides better performance.
The XML Extender also offered a set of EXTRACT functions that enable applications to use XPath to extract individual values from XML documents in a db2xml.xmlclob column. In the migration you will replace these EXTRACT functions in your queries with the SQL/XML functions XMLQUERY, XMLEXISTS, or XMLTABLE, which are faster and more powerful. For details, see the articles “Which way to query your XML data?” and “XMLTABLE By Example“.
If you are using the XML Extender UPDATE function to modify XML in db2xml.xmlclob columns, you will migrate them to native XML updates.
4. XML validations against DTDs or XML Schemas
If you are using DTDs to validate your XML documents, you can convert the DTDs to an equivalent XML Schema and use the XML Schema capabilities that are part of DB2 pureXML. That is, you can register XML Schemas in DB2’s schema repository and validate XML documents during LOAD, insert, update, select, or other operations.
5. Other XML Extender features such as XSLT transformations or interaction with MQ
If you are using XSLT style sheets with the XML Extender, you can continue using the same style sheets with the XSLTRANSFORM function.This function also allows you to pass parameters to a style sheet at runtime. Also, the DB2 functions to read and write XML documents from/to Websphere MQ message queues are still available as part of the DB2 Federation capabilities that provide connectivity between DB2 and MQ.