If you think that mainframe computers are old dinosaurs that only run ancient COBOL code – think again! Now mainframes also run XQuery!

While DB2 for Linux, UNIX, and Windows has been supporting XQuery and SQL/XML since Version 9.1 (released in 2006), DB2 9 for z/OS “only” supported XPath and SQL/XML.

I have put the word “only” in quotes because for many applications XPath and SQL/XML are fully sufficient. When you combine XPath expressions with SQL/XML functions such as XMLTABLE plus other SQL language constructs you can write very powerful XML queries and accomplish many of the same things that you can do with XQuery.

DB2 10 for z/OS has added a variety of new XML features such as node-level XML updates, XML-type parameters and variables in stored procedures and user-defined functions, and enhancements for XML Schemas and XML indexes.

With APARs PM47617 and PM47618, DB2 for z/OS now also supports XQuery within the SQL functions XMLTABLE, XMLQUERY, XMLEXISTS, and XMLMODIFY.

So what are the additional capabilities and benefits that XQuery provides? Examples include:

  • You can compose new XML structures using direct element constructors
  • You can use FLWOR expressions (for-let-where-order by-return) to iterate over and manipulate intermediate query results
  • You can join and combine information from multiple XML documents
  • You can use XQuery comma-expressions to construct and use new sequences of XML nodes
  • You can code if-then-else logic to implement conditional expressions
  • etc.

Let’s look at some examples.

Construct new XML structures with direct element and attribute constructors

The following query constructs a new order summary document from each order (XML document) that is selected from the “orders” table. New elements, such as <orderSummary> and <orderedItems> are constructed by providing the start and end tags explicitly. Similarly, the attribute orderNumber is also constructed explicitly. The content of the constructed elements and attributes is computed by XPath (or XQuery) expressions that extract selected information from each source document.

SELECT XMLQUERY('
          <orderSummary orderNumber="{$po/order/orderNo/text()}">
             <orderedItems>{$o/order/items/item/itemName}</orderedItems>
          </orderSummary>'
       PASSING orders.orderdoc AS "po")
FROM orders
WHERE ...

FLWOR expressions

The next query joins the tables “orders” and “items” on their XML columns “orderdoc” and “details”, respectively. The join predicate in the XMLEXISTS ensures that we find the items that match a given order and that we don’t produce a Cartesian product. For each pair of order document and item document, the FLWOR expression in the SELECT clause combines information from both documents into a new documents that contains the item name, the ordered quantity, and the item details.

SELECT XMLQUERY('for $o in $po/orders/items/item
                   for $i in $it/item
                 where $o/itemName = $i/name
                 return <orderdItem>
                           {$i/name}
                           {$o/item/quantity}
                           {$i/details}
                        </orderdItem>'
         PASSING orders.orderdoc AS "po", items.details as "it")
FROM orders, items
WHERE
XMLEXISTS('$po/order/items/item[itemName = $it/item/name]'
           PASSING orders.orderdoc AS "po", items.details as "it")

 

For more information on XQuery in DB2 10 for z/OS, here is a good place to continue reading:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.xml/src/tpc/db2z_expover.htm

Advertisements

TPoX 2.1 has been released!

November 15, 2011

First, what is TPoX?  I have two answers to that question.

Answer 1:

TPoX, short for “Transaction Processing over XML”, is an XML database benchmark that executes and measures a multi-user XML workload. The workload contains XML queries (70%) as well as XML insert, update, and delete operations (30%). TPoX simulates a simple financial application that issues XQuery or SQL/XML transactions to stress XML storage, XML indexing, XML Schema support, XML updates, logging, concurrency and other components of an XML database system.

The TPoX package contains:

TPoX has been developed by Intel and IBM, but is freely available and open source since 2007. A variety of TPoX performance results and other usage of TPoX have been reported.

Answer 2:

TPoX is a very flexible and extensible tool for performance testing of relational databases, XML databases, and other systems. For example, if you have a populated relational database you can use the TPoX workload driver to parameterize, execute, and measure plain old SQL transactions with hundreds of simulated database users. I have used TPoX for a lot of relational performance testing, because it’s so easy to setup and measure concurrent workloads. The workload driver reports throughput, min/ax/avg response times, percentiles and confidence intervals for response times, and other useful metrics. Oh, and by the way, TPoX happens to include an XML data generator and a set of sample XML transactions, in case you’re interested in XML database performance.

 

In the latest release, TPoX 2.1, we have further enhanced the extensibility of the TPoX Workload Driver. The XML data and XML transactions are still the same.

Some of the enhancements in TPoX 2.1 include:

  • higher precision in reported response times
  • proper handling and counting of deadlocks, if any
  • easier post-processing of results in Excel or other spreadsheets software
  • new types of workload parameters such as random dates, random timestamps, sequences, etc.
  • in addition to SQL, SQL/XML, and XQuery, transactions can now be also supplied as Java plugins, allowing you to run and measure anything (concurrently!) that you can code in Java, such as:
  • complex transactions that include application logic
  • calls to web services or message queues
  • obtaining data from RSS or ATOM feeds
  • transactions against databases or content repositories that do not have a JDBC interface

We have already found these extensions extremely valuable for some of our own performance testing, and we’re happy to share them. You can download TPoX 2.1 (free, open source) and find more detailed information in the release notes as well as the TPoX documentation that is included in the download.