IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
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:
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
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 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)
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.
Add a Comment