New XML Features in DB2 10 for z/OS !

November 18, 2010

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.

    11 Responses to “New XML Features in DB2 10 for z/OS !”

    1. Mike Says:

      support for XQuery language (FLWOR)on zOS?


    2. FLWOR expressions are not yet supported in DB2 10 for z/OS. Do you have a specific application on DB2 z/OS where you need FLWOR expressions? If so, feel free to send me an email (mnicola@us.ibm.com) so we can discuss in more detail.

      This also reminds me to make a more general comment: XQuery language constructs, such as FLWOR, add more capabilities and more expressiveness on top of XPath. The same is true for SQL. When you use XPath in SQL functions such as XMLTABLE, XMLQUERY, and XMLEXISTS, you can use additional SQL language constructs on top of those functions to write very advanced SQL/XML queries. Using SQL/XML in UDFs and stored procedures makes it even richer. Hence, quite a few applications can actually implement all or most of their XML processing needs with SQL and XPath.

    3. Rosalie Says:

      Any reason why they have decided not to support FLWOR expressions in DB2 10 for z/OS?


      • Hi Rosalie, I suspect it was a question of how many new features can be put into a new release in a fixed amount of time. There is not just the time to develop but also to thoroughly test and document new features. In the past, a variety of XML enhancements for DB2 z/OS have been delivered as APARs on top of DB2 9 for z/OS. They are all listed in the informational APAR II14426. For example, the powerful XMLTABLE function has been delivered that way. Also, 13 new XPath function were delivered in PK55585 and PK55831. I could imagine that FLWOR expressions might also be delivered as an APAR on top of DB2 10 for z/OS at some point. However, this is just my personal opinion and there is no guarantee until an official announcement from IBM. If and when that hapens, I’ll bog about it : – ) !

        By the way, I think that the new XML features in V10 (including XML updates, XML index enhancements, binary XML transmission, XML in SPs and UDFs, etc.) are all very critical and I personally think it was good to give them priority over FLWOR expressions.

        Matthias

    4. Angelo Sironi Says:

      Hi Matthias,
      we are trying to use regular expressions (in a Cobol program) and, more specifically, the fn:matches built-in function with DB2 10 for z/OS.

      We first tried some simple queries using both DB2 10.5for LUW with Data Studio and DB2 10 for z/OS with QMF & SPUFI.

      The following query works fine on DB2 for LUW, but doesn’t work on DB2 for z/OS:

      with numbers(value) as
      (select ‘12345’ from sysibm.sysdummy1
      union all
      select ‘381e3’ from sysibm.sysdummy1
      union all
      select ‘3816673’ from sysibm.sysdummy1 )
      select ‘”‘ || VALUE || ‘”‘,
      xmlquery(‘fn:matches($VALUE,”^[0-9]{5}$”)’) from numbers ;

      Answer set:

      1 2
      ——— —–
      “12345” true
      “381e3” false
      “3816673” false

      In DB2 10 for z/OS the same query returns the following error (both when executed in QMF and SPUFI):

      DSNT408I SQLCODE = -16005, ERROR: AN XQUERY EXPRESSION REFERENCES AN ELEMENT
      NAME, ATTRIBUTE NAME, TYPE NAME, FUNCTION NAME, NAMESPACE PREFIX, OR
      VARIABLE NAME VALUE THAT IS NOT DEFINED WITHIN THE STATIC CONTEXT.
      ERROR QNAME= err:XPST0008
      DSNT418I SQLSTATE = 10506 SQLSTATE RETURN CODE
      DSNT415I SQLERRP = DSNXQNR SQL PROCEDURE DETECTING ERROR
      DSNT416I SQLERRD = -600 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
      DSNT416I SQLERRD = X’FFFFFDA8′ X’00000000′ X’00000000′ X’FFFFFFFF’
      X’00000000′ X’00000000′ SQL DIAGNOSTIC INFORMATION

      I understand that the issue is related to the reference to column VALUE; but I cannot understand why the query works on DB2 for LUW and doesn’t work with DB2 for z/OS and, more important, how to fix it.

      The second issue we have is with host variables. The following static SQL query works fine when executed by a Cobol program:

      EXEC SQL SELECT XMLCAST ( XMLQUERY ( ‘fn:matches(”abcdefghilm”,”[a-z]”)’ )
      AS DECIMAL ) INTO :H FROM SYSIBM . SYSDUMMY1

      However, the following does not:

      EXEC SQL SELECT XMLCAST ( XMLQUERY ( ‘fn:matches(”abcdefghilm”,”:H”)’ ) AS DECIMAL ) INTO : H FROM SYSIBM . SYSDUMMY1

      Nor the following:

      SELECT XMLCAST ( XMLQUERY ( ‘fn:matches(”abcdefghilm”, :VAR2)’ ) AS DECIMAL ) INTO : H FROM SYSIBM . SYSDUMMY1

      So, the question is: does fn:matches support host variables? If it does, how should they be coded?

      Many thanks for your help!

      Kind regards,
      Angelo Sironi


      • Hi Angelo,

        let me answer your 2nd question first. Host variables are supported with XML functions, they need to be passed into the XML context as XML variable. Here is an example:

        SELECT XMLCAST (
        XMLQUERY ( 'fn:matches(”abcdefghilm”, $v)' PASSING cast( :VAR2 AS VARCHAR(20)) as "v" )
        ) AS DECIMAL ) INTO : H
        FROM SYSIBM . SYSDUMMY1

        Do you happen to have the DB2 pureXML Cookbook? Section 7.6 has a bunch of similar examples. Here is one of them:

        SELECT T.*,
        XMLCAST(
        XMLQUERY('$INFO/customerinfo/phone[@type = $t]'
        passing cast(:type AS VARCHAR(10)) AS "t") AS VARCHAR(20))
        FROM customer,
        XMLTABLE('$INFO/customerinfo[@Cid = $c]' passing cast(:custid AS INTEGER) AS "c"
        COLUMNS
        custname VARCHAR(20) PATH 'name') AS T;

        Does that help?

        Matthias

        • Angelo Sironi Says:

          Hi Matthias,
          many thanks for your prompt ansswer….
          Unfortunately, it still doesn’t work…

          Here’s the query and the error code:

          EXEC SQL
          SELECT XMLCAST (
          XMLQUERY ( ‘fn:matches(“abcdefghilm”, $v)’
          PASSING cast( :VAR2 AS VARCHAR(350)) as “v” ) AS DECIMAL )
          INTO :WS-EMAIL
          FROM SYSIBM.SYSDUMMY1
          END-EXEC.

          -16009 AN XQUERY FUNCTION NAMED function-name WITH number-of-parms PARAMETERS IS NOT
          DEFINED IN THE STATIC CONTEXT. ERROR QNAME= err:XPST0017

          However, if the programmer uses a host variable (with passing etc.) for the first parameter pf the fh:matches function and a string for the second parameter, it works!!!

          So we made some progress….

          Thnks a lot.
          Kind regards,
          Angelo
          .


        • Hi Angelo,

          I was so focused on the parameter passing issue that I’m realizing only now that the fn:matches function has different rules for the first parameter (the string data) and the second parameter (the regular expression). The 2nd parameter must be a string literal and cannot be a variable.

          Have a look at the documentation:
          http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.xml/src/tpc/db2z_fnmatches.dita

          – Matthias

        • Angelo Sironi Says:

          Hi Matthias,
          many thanks again. The documentation could probably beterre clarify what a XQUERY Expressin can and cannot include. I could find a clear definition of that. usually it is refeferenced withinh the context of XML documents, but it doesn’t clarify extensively the various usage contexts. As for the second parameter (the PATTERN), tyou’re right as it states that is “a string literal”..

          Thanks a lot again.

          Kind regards,
          Angelo
          ———-
          P.S. I’ve a copy of you book DB2 Pure XML Cookbook. Very estensive and well written.
          I still need to heck the correction you suggested for my first query. I’ll let you know when done.


      • Hi Angelo,
        to your first question, why does that query work in DB2 LUW not in DB2 for z/OS: I think the reason is that you are using a syntax shortcut which is allowed in LUW but not in z/OS.

        In the xmlquery function the variable $VALUE implicitly references the column “value”. This is a shortcut notation. The full syntax requires the PASSING clause like this:

        SELECT xmlquery(‘fn:matches($v”^[0-9]{5}$”)’ PASSING values as “v”) FROM …

        Does this work?

        Matthias

        • Angelo Sironi Says:

          Hi Matthias,
          thank you,,, It works using “PASSING …”.
          Kinf regards,
          Angelo


    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Follow

    Get every new post delivered to your Inbox.

    Join 66 other followers

    %d bloggers like this: