DB2 11 ESP for z/OS XML Features – The Hierarchy Strikes Back

DB2 11 for z/OS XML Features – The Hierarchy Strikes Back

By Isaac Yassin, IDUG DB2 11 White Paper Editorial Committee

Within few months we’ll have DB2 for z/OS next incarnation – the 11th in number (Sequoia – DB2 11). A group of dedicated DB2 bigots J have embarked on the mission to spread the gospel of the goodies to come, and are writing an IDUG Technical White Paper to help ease it in. The White Paper is due for release later in the year, but in the meantime here’s a taster of one interesting aspect of the new release.

IMS Beginnings

Way back we used IMS database. Everything was hierarchical as the roots were constructed to handle massive BOM (Bill of Material) needed for the Saturn moon rocket and the Apollo project. We lived happily with that and no one knew better until the arrival of the relational model. From that day, our life has changed.

No more complex hierarchies and complicated program logic to handle data. SQL became the king. Handling SSA and playing around with how to access the data disappeared in a blink. Some complained, some objected, some claimed that IMS is better / faster / … pick you choice. However the vast majority voted with their feet (actually – hands) and moved over to SQL with the notion of never looking back.

DB2, being IBM’s flagship in the relational DBMS market has come a long way since its’ debut. My life with DB2 started with V1.2 used for a transportation system. One of the major achievements at that time was a view used to join 5 tables, nearly everyone thought it’s going to fail, but DB2 surprised us with how easily it took the join and made it work (BTW – it is still working today in V10).

During the years we chimed along happily with the relational model, normalization and mathematical designs. With experience we had some new design ideas which were a convoluted hierarchy. We found out that some designs really wanted a touch of hierarchy to work better. We found workarounds and did not want to look back.

DB2 and XML

With DB2 V8 we got some XML functions, suddenly we could play a bit with  “sort of” hierarchical structures, however the XML data had to be kept in a CLOB or spread/broken over to many tables with complicated join constructs. With DB2 9 pureXML we got what we wanted – XML usage within SQL and a way to handle hierarchies where we needed.

With DB2 10 pureXML we got a boost! DB2 11 will give us even more J

Let’s look at XML evolution:

DB2 V8:

  • No XML native data type
  • XML UDF -
  • XMLELEMENT – constructs an XML element, given a tag, some content, and optionally, one or more attributes and namespaces
  • XMLATTRIBUTES – constructs an XML attribute; only used as an argument to XMLELEMENT
  • XML2CLOB – returns a CLOB representation of an XML value (superseded by XMLSERIALIZE in Version 9)
  • XMLCONCAT – concatenates two or more XML elements
  • XMLFOREST – constructs a series of XML elements, based on column expressions
  • XMLNAMESPACES – declares one or more XML namespaces
  • XMLAGG (column function) – returns a concatenation of XML elements from several table rows

DB2 9:

  • XML is a native data type
  • XML functions are built-in, not UDF as in DB2 V8.
  • XML tablespace and needed indexes are built by DB2.
  • More XML functions:
    • XMLSERIALIZE – replaces XML2CLOB – returns a CLOB (or BLOB, or DBCLOB) representation of an XML value
    • XMLCOMMENT – generates a comment
    • XMLDOCUMENT – generates a complete document
    • XMLPI – generates a processing instruction
    • XMLTEXT – generates a text node (content, with named character entities, if necessary)
    • XMLPARSE – parses an argument as an XML document
    • XMLQUERY – applies an XPATH expression to an XML value
    • XMLEXISTS – tests whether an XPATH expression returns a sequence of one or more items (used in a WHERE clause)
    • DSN_XMLVALIDATE – validates an XML document against an XML schema
    • XMLTABLE – returns a DB2 result set with rows derived from one or more XML documents, based on an XPATH expression
    • XMLCAST – operator that converts to/from an XML expression
    • XMLCOMMENT – generates a comment
    • XML indexes

DB2 10:

  • XML index usage for XML join
  • XPATH enhancements when using index (fn:exists() , fn:not(), fn:upper-case(), fn:start-with(), fn:substring()  and more)
  • XMLTABLE query transformation – a query with XMLTABLE reference that has an XPATH expression with predicate is eligible to a re-write that adds a WHERE with the XMLTABLE predicate
  • Support for multiple versions of XML document
  • XMLMODIFY updates only the needed portion of the document (instead of all document as of DB2 9), it can insert, replace & delete a node
  • XML Date & Time data types were added with duration calculations
  • XML data type can be used in SQL procedures as parameter and as a variable
  • XML kept in BINARY format
  • We can use DEFINE NO for XML tablespace (via IMPDSDEF zparm)
  • Unload can be directed to VBS files (much faster than PDSE or USS)
  • XML validation can be done via a scalar function

DB2 11:

  • Performance improvements for LOAD with binary format
  • Cross system loader support for XML columns (LOAD … INCURSOR)
  • DB2 implicitly adds a document node when storing data with the following statements:  INSERT, UPDATE and XMLDOCUMENT.
  • Eliminate the hotspots during XML insert (retrofit to DB2 9 and 10)
  • Binary XML validation (retrofit to DB2 10)
  • Partial validation (retrofit to DB2 10)
  • Many performance enhancements
  • … more to come

DB2 Next (12?) – wait and see!

Do you want more?  Look out for the IDUG DB2 11 Technical White Paper which will be published later this year, or come to the IDUG Europe Conference in Barcelona, October 13-18, 2013 where I’ll be presenting on this topic.

Recent Stories
Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas

Fun with Date Ranges