The Hierarchy Strikes Back



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 embarked on the mission to spread the gospel of the goodies to come decided to write a White Paper on it to help ease it in.

This is the 1st blog (and partial chapter) on one interesting aspect of DB2.


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 make 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.

With DB2 V8 we got some XML functions, suddenly we could play a bit with a “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 evolvement:

DB2 V8:

                No XML native data type


                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)


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 J


DB2 Next (12?) – wait and see!


Do you want more? – Come to IDUG Barcelona - October 13-18, 2013, at The Gran Hotel Princesa Sofia.

I’ll present a drill down version of XML usage, with code samples.

Isaac Yassin 
IBM Gold Consultant
IBM Champion - Information Management
IBM Certified Solution Expert
IBM Certified System Administrator - DB2 10 for z/OS
IBM Certified Database Administrator - DB2 for z/OS V7, V8, 9 & 10
IDUG Israel RUG co-Chair 







Views: 225

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.


Introducing IBM Db2 for z/OS Developer Extension for Microsoft Visual Studio Code

Started by Calene Janacek in Application Development and DB2 Jul 30. 0 Replies

We are excited to announce that the first iteration of IBM Db2 for z/OS Developer Extension is available now as a free downloadable extension in the…Continue

QMF Governor

Started by Maitena Gallastegi Ginea in Application Development and DB2. Last reply by Maitena Gallastegi Ginea Jul 30. 4 Replies

Hi,We are using QMF Governor to limit the QMF queries of users.We have configured correctly and it is working OK. We want to get statistics of those queries canceled by QMF Governor but we are not able to discover where that information is stored.…Continue

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service