In my previous blog post I wrote about the new XML enhancements in DB2 10 for z/OS, the new release of IBM’s relational database for the mainframe. One of the exciting new XML features are node-level XML updates.

While DB2 9 for z/OS only supported full-document replacement, DB2 10 for z/OS allows you to insert, delete, or modify individual XML elements or attributes (“nodes”) within an XML document. This is based on the XQuery Update Facility, which defines the syntax and semantic of node-level XML updates.

In DB2 10 for z/OS, such XML updates are always embedded in an SQL Update statement, such as the following statement that updates the XML column “info” in the table “customer”:

UPDATE customer
SET info =  XMLMODIFY( <xquery update expression>)
WHERE ….

The WHERE clause of the UPDATE statement can contain relational and/or XML predicates to select zero, one, or multiple rows (documents) for update. For each qualifying row the XMLMODIFY function applies the XQuery Update expression to the respective XML document.

The XMLMODIFY function is new in DB2 10 for z/OS and can only be used on the right-hand side of a SET clause in an UPDATE statement.

The following update expressions are supported in the XMLMODIFY function:

Let’s look at some examples for each of those update expressions.

The following UPDATE statement modifies the document with the relational id “123” and changes the value of the <city> element to “San Jose”:

UPDATE customer
SET info =  XMLMODIFY(‘replace value of node /customer/addr/city with “San Jose” ‘ )
WHERE customer.id = 123

If you want to update an element or attribute that occurs multiple times per document then you need to select exactly one of its occurrences. In other words, the target of the update expression must be a single node per document. It cannot be zero or multiple nodes per documents. Otherwise an error is returned.

Assuming that a customer document can have multiple <phone> elements, the following UPDATE statement changes the value of the phone element whose @type attribute has the value “home”:

UPDATE customer
SET info =  XMLMODIFY(‘replace value of node /customer/phone[@type = “home”]  with “408-463-4963”  ‘ )
WHERE customer.id = 123

The new value can also be provided via a parameter marker or host variable. For example, the next UPDATE provides the new phone number via the host variable :newnum. The second argument of the XMLMODIFY function contains the host variable and assigns an XQuery variable name (“new”) that can be referenced with a $-sign in the update expression itself. In this way the variable $new refers to the host variable:

UPDATE customer
SET info =  XMLMODIFY(‘replace value of node /customer/phone[@type = “home”]  with $new ‘, :newnum as “new )
WHERE customer.id = :cid

Next, let’s assume we need to replace the home phone number with a cell phone number. This can be done with a node replacement. The following UPDATE provides a new <phone> element (shown in green). It uses the XMLPARSE function to convert the text representation of the new element to data type XML,  and assigns the new element to the variable $new. This variable is used in the “replace node” expression to replace the selected existing phone element:

UPDATE customer
SET info =  XMLMODIFY(‘replace node /customer/phone[@type = “home”]  with $new ‘ ,
XMLPARSE(document ‘<phone type=”cell”>408-123-4567</phone>‘)  as “new”
)
WHERE customer.id = 123

Again, the new element could also have been provided  by a host variable.
You can also add and remove nodes from a document. The next UPDATE deletes the phone element with the cell phone number from the document:

UPDATE customer
SET info =  XMLMODIFY(‘delete nodes /customer/phone[@type = “cell”] )
WHERE customer.id = 123

Note: the “delete” expression is the only update expression that is allowed to affect zero, one, or multiple nodes per document. While the “replace” expression can only be applied to one node per document, the “delete” expression can remove multiple nodes at once!  The following UPDATE deletes all <phone> elements that appear on the path /customer/phone:

UPDATE customer
SET info =  XMLMODIFY(‘delete nodes /customer/phone )
WHERE customer.id = 123

To insert a new element, attribute, or document fragment you need to provide the new node and specify a target location where the new node should be placed in the document. For example, the next UPDATE statement inserts the new <phone> element (in green) into the document so that it appears after the existing  <email> element:

UPDATE customer
SET info =  XMLMODIFY(‘insert node $new  after /customer/email ,
XMLPARSE(document ‘<phone type=”cell”>408-123-4567</phone>‘)  as “new”)
WHERE customer.id = 123

Instead of using the keyword “after” you can also specify that a new node should be inserted “before” an existing element or as a child “into” an existing element. When you use the keyword “into” to add a node as a new child to an existing element, then you can also specify “as first into” or “as last into” to explicitly make it the first or the last child node.

The XPath the specifies the target location of the new node, such as /customer/email in the example above, must identify exactly one node, and must node lead to zero or multiple nodes.

And finally, if your XML data contains namespaces, you must declare the namespaces in the XMLMODIFY function just like you would in an XMLEXISTS or XMLQUERY function. This next update declares a default element namespace that applies to all elements in the XPath /customer/addr/city:

UPDATE customer
SET info =  XMLMODIFY(”declare default element namespace http://www.example.com&#8221;;  replace value of node /customer/addr/city with “San Jose” ‘ )
WHERE customer.id = 123

From here on you should try it out for yourself, or let me know if you have any questions!

 

 


The release of DB2 10 for z/OS was one of the big topics at IBM’s Information on Demand conference in Las Vegas and at the IDUG Europe conference in Vienna a couple of weeks ago. DB2 10 for z/OS is the brand-new version of the DB2 database for mainframe computers running the z/OS operating system.

If you think the mainframe is dead – think again! Mainframe computers are still running many of the world’s most mission-critical databases and applications. Many leading companies in banking, insurance, telecommunications, manufacturing, logistics, and other industries still rely on the mainframe’s unsurpassed reliability and performance for transaction processing. And the wide-spread adoption of XML does not exclude the mainframe world.

DB2 9 for z/OS introduced pureXML with native XML storage, XML indexing, XML Schema support, SQL/XML queries, and XML support in various DB2 utilities such as load, unload, and others. Overall, the XML support in DB2 9 for z/OS is similar to that in DB2 for Linux, UNIX, and Windows (LUW).

The new XML features in DB2 10 for z/OS include:

  • Node-level XML updates: While DB2 9 for z/OS only supported full-document replacement, DB2 10 for z/OS allows you to insert, delete, or modify individual XML elements or attributes within an XML document. This is based on the XQuery Update Facility and is similar to the XML update support in DB2 LUW.
  • Stored procedures and user-defined functions (UDFs) can now have parameters and variables of data type XML. This allows for more flexible and more powerful XML application development.
  • Binary XML for data transmission between database server and client applications. This new binary encoding reduces the size of XML on the wire and the associated network latency. As a result, XML insert and retrieval operations are faster.
  • Concurrency control for XML is now based on a new multi-versioning approach, which allows for higher concurrency and performance. It avoids XML locking for readers. (Note: Multi-versioning does require new function mode.)
  • Increased support for native XML date and time data types.
  • One or multiple XML Schemas can be assigned to an XML column, as a so-called XML column type modifier. As a result, schema validation happens automatically for insert, update, and load operations. This XML column type modifier can be altered and supports schema evolution.
  • XML Schema validation can be offloaded 100% to zIIP and zAAP processors, which reduces CPU cost. This enhancement has also been added to DB2 9 for z/OS (PK90032, PK90040).
  • Enhanced XML index support: XML indexes can now be used to for case-insensitive search (if desired) and to check for the existence or absence of an element or attributes regardless of its value. This has been added to DB2 9 for z/OS too (PK80732, PK80735).
  • Support for XPath functions fn:matches, fn:replace, and fn:tokenize, which allow for powerful string manipulation.

    And there are even more XML enhancements in DB2 10 for z/OS that I haven’t listed above, such as enhancements in utilities like CHECK DATA, LOAD, and UNLOAD. Some of the items on the list above deserve a more detailed discussion and some examples, and I’ll try to get to that in subsequent blog posts. Let me know if you have any specific questions on any of these features.