Last week, December 8 and 9, 2008, I visited Kansas City and Topeka with the help of Jeff Mucher, a DB2 advisor from IBM at Dallas. On the 12/8 afternoon, I presented Introduction to pureXML in DB2 9 for z/OS to the Heart of America DB2 Regional User Group at a hotel in Overland Park, with an audience of over 30 people. In the middle, I also did a live demo using CLP (Command Line Processor), connecting to the DB2 9 on a DemoNet native machine at Austin. I have put the CLP scripts and their output capture called XMLQuickDemo online so you can download to try out yourself. The presentation was well received with quite some interesting questions and discussions. At least one friend from DST told me right after the presentation that the DB2 for z/OS XML features are very impressive. I was happy to hear that!
Here are a few words I've heard from customers describing XML in DB2 9 for z/OS or the developers (including me :-) ): quite impressed/very impressive, brilliant, genius, clever. Impressive, huh?!
On the 12/9, Jeff and I visited a DB2 client at Topeka, KS. We met with about a dozen developers, DBAs and System Programmers to introduce DB2 XML features and discuss their application scenarios. We saw DB2 XML well fit in three scenarios for their applications:
- XML for object persistence.
- XML for flexible change representations.
- XML for event log for auditing/regulatory compliance.
The common theme in these usage scenarios is flexibility, flexibility, and flexibility. We saw more and more of these XML application patterns.
Beth wanted me to confirm if her description of XML is correct: it's more like a delivery truck, it could be UPS, FedEX, or any other truck, we don't care. We handle it based on its content. YES!
This trip had special meaning for me. I was attending KU for 2 years over 14 years ago, and this was the first time I got back to Kansas after so many years. And I was able to meet with one of my classmates not seen for over 14 years, and also one of our former interns, now a CS faculty member at KU. Also I seldom experienced snowy weather these years, and it was snowing on 12/9! What a colorful trip!
I'd like to describe one performance result at our lab of a workload that simulates an auditing application. It uses one XML column to store all kind of events in small XML documents. There are 210 XML indexes created on the XML column. For each XML document, there are about 10 indexes that will have keys generated, and the rest does not have hit. This large number of indexes enable efficient diverse queries on the event log.
The result is that the overhead of 210 XML indexes caused only 40% degradation compared with only 10 XML indexes that generates XML keys always. This is a pretty good result! We have an APAR PK75613 (overriding PK66218) to improve XML index keygen performance. Applying this will help reduce XML value index overhead in general.
By the way, if you'd like to know what APARs to apply for XML features in DB2 9 for z/OS, look into the info APAR II14426.
Last week, just before the Thanksgiving holiday in the US, Cindy Saracco from our team, along with Tad Worley, one of our Infosphere Warehouse experts, published a new paper on developerWorks titled: "Create business reports for XML data with Cognos 8 BI and DB2 pureXML: Two techniques to help you get started." We have been getting more and more questions about using Cognos with DB2 pureXML and thought it would be good to get some information out there on the subject. Because XML messages frequently contain important business data, companies are increasingly interested in querying and reporting on this data. The paper takes you through two different methods, step by step, showing examples along the way. Cindy is one of the best technical writers that I know of (inside and outside IBM), so if this topic is of interest, be sure to check out the article.
And in January, we will have a related article coming out titled "Reporting on pureXML data with QMF/DataQuant" so look for that as well.
Kate Riley Tennant
Last Friday (October 17) was a long day for me as I returned to San Jose, California from Warsaw, Poland, after attending IDUG Europe 2008 Conference, held in Hilton Warsaw from October 13 to 16 (October 17 is for one-day seminars).
Here are some important impressions from my personal experiences at the conference.
- Motivating opening keynote presentation by Marc Woods, World Champion Paralympic Athlete. Inspiring, yet humorous stories on winning gold medals, especially the one for team melody swimming, emphasizing the importance of team work and leadership.
- Ever-increasing interests in pureXML.There were quite a few sessions about pureXML.Matthias presented two sessions about SQL/XML and best practices, and I presented one for DB2 for z/OS DBAs. I have some slight update on my presentation, available here:DB2 9 for z/OS pureXML - Survival Guide for DBAs.These three sessions were completely full, some had to be turned away. Next time, the planners should probably arrange larger rooms for XML sessions to accommodate more audience.
- We had one Special Interest Group (SIG) session on XML. Participants were Phil Grainger of CA, Jim Dee of BMC Software, Matthias Nicola of DB2 for LUW, and myself. Moderated by Hans Miseur. There were very interesting discussions.
- There were threads for application development that may be for z/OS or LUW or both. Not many attendees realized there was another session on the Java driver for XML, by Bilung (Brandon) Lee, in the application development thread. I hinted Brandon to include something like z/OS or XML in his title to attract more people.
- There was a presentation on MQTs on z/OS also. The room was full also.
- There were many other excellent sessions to attend, including Curt's closing keynote.
In one of Matthias' session, one of z/OS customers asked about pureXML samples. I have put IOD 2007 DB2 9 for z/OS XML Hands-on Lab material for download. This package contains the lab material used in IOD 2007 XML lab, including DDL, Java samples, query samples, examples for schema registration, validation, and decomposition. This material is for educational use only without any warrant, express or implied, as usual.
Some fun fringes follow.
The DB2 25th Anniversary Party on Wednesday October 15 night was held in the middle of nowhere. The bus drivers got lost, on very narrow country road, and the bus I was on took about 1.5 hours to get to the party place. It was amazing that the driver was able to turn the bus around in the dark on an extremely narrow road in the field. The Polish food was good. There was traffic congestion near 11pm on Warsaw streets on the way back to the hotel.
Jim Dee of BMC software was the moderator for my session. He made fun of me in his introduction - saying that I was a genius, and knew everything about XML in the world. That definitely helped my session evaluation! :-)
We were recommended by a US attendee (forgot his name) a restaurant near Hilton Hotel called The Inn under the Red Hog, which used to cater forcommunist dignitaries like Lenin, Castro, and Chairman Mao etc. Four of us (Chinese attendees) tried it out for Thursday dinner. We shared three main dishes: roasted pork ribs, a pork shank, and Mao's chicken (just like orange chicken). They were Polish dishes, heavy flavor (need beer to dilute), big portion, enough for us. Compared with Sekret in the old town, recommended by Hilton Hotel, the Red Hog seems to be better and less expensive. Reservation is recommended as we were rushed out by next reservation.
Looking ahead, next week, IOD Global Conference will be on at Las Vegas. We have many pureXML presentations and Hands-on Labs. If you'd like to try the hands-on labs, I urge you to come even if the enrollment was full. We will admit stand-byers after 5-10 minutes in case of no-shows.
DB2 for z/OS clients are very CPU-conscious. So are we the developers in DB2, and performance has been one of the most critical factors during our design and development for XML support. We probably have one of the most efficient XML parsers implemented in software in the world, co-designed and custom-made by our z/OS friends; and we have a high-performance XML schema validating parser also. As a result, DB2 for z/OS has very efficient insert, load, and update. (Deletion and retrieval of XML documents perform very well too but do not need XML parsing).
To reduce the CPU cost, DB2 workloads from DRDA can benefit from zIIP specialty engines, and XML parsing can now be redirected to zIIP or zAAP. I hope to provide some details here.
As part of DB2 workload from DRDA, XML processing is eligible for zIIP redirection, except for XML validation (that is a UDF not eligible for zIIP for the time being). In addition, recent enhancements made the z/OS XML parsing system service 100% eligible for redirection to zIIP or zAAP, depending on the mode under which it is running:
- For the z/OS XML parsing system service invoked from the TCB mode, it's 100% eligible for zAAP redirection.
- And for the z/OS XML parsing system service invoked from the enclave SRB mode, it's 100% eligible for zIIP redirection.
Before redirection of the z/OS XML parsing system service, DB2 9 XML workload (including XML parsing system service) from DRDA via TCPIP in the following scenarios run under the SRB mode is zIIP eligible:
- WebSphere for z/OS (JCC T4)
- WebSphere distributed (JCC T4)
- Distributed transactions using DB2 Connect
- Native SQL Stored Procedure
In addition, Index Build phase of LOAD is specially executed under SRB mode to make it eligible for zIIP also (applicable to XML indexes, but no XML parsing involved).
Now with the redirection of z/OS XML parsing system service, the XML parsing invoked for the above scenarios may qualify for additional redirection at 100% rate. For example, a workload that used to have 49.6% redirected to zIIP can now have 56.60% redirected to zIIP.
For DB2 9 XML workload in the following scenarios not from DRDA run under the TCB mode, z/OS XML parsing system service is 100% eligible for zAAP redirection:
- WebSphere® for z/OS (JCC T2)
- Stored Procedure/UDF/Trigger
- Native SQL PL stored procedure
- Call Attach (CAF)
- RRS Attach
- LOAD utility
In addition, XML parsing system service invoked by a stored procedure/UDF/trigger from DRDA via TCPIP run under the TCB mode is now also 100% eligible for zAAP. For example, we've observed up to 36% or even 48% of XML LOAD CPU redirected to zAAP. This is the first time that part of a DB2 workload can be redirected to a zAAP (through the system services).
DB2 9 APAR PK50575 adds support for accounting zAAP redirection in addition to that already provided for zIIP, and records CPU time on zAAP and zIIP engines when they exist.
In order to estimate redirection, RMF workload activity report will show specialty engine eligible CPU through APPL% AAPCP and APPL% IIPCP, when setting PROJECTCPU = YES in IEAOPTxx member in SYS1.PARMLIB.
Relevant z/OS levels and APARs to enable XML parsing system services 100% eligible for specialty engines are the following:
- zAAP - z/OS V1.9, APAR OA20308 for z/OS V1.8, V1.7.
- zIIP - z/OS V1.10, APAR OA23828 for z/OS V1.8, V1.9.
For more details about zIIP redirection and some XML performance numbers, see this whitepaper: DB2 9 and z/OS XML System Services Synergy Update.
At the moment, DB2 9 for z/OS XML schema validation does not invoke z/OS system services available in z/OS V1.10. As a future direction, when DB2 XML validation is enhanced to invoke the system services, it will be 100% eligible for zIIP and zAAP redirection also.
In summary, on z/OS, XML parsing is extremely efficient, and can be redirected 100% to zIIP or zAAP. DB2 XML processing takes advantage of the redirection to lower CPU cost.
I was frequently asked by customers as well as IBMers, "what is missing without XQuery?" I'd say that using SQL/XML with XPath support in DB2 9 for z/OS, you miss almost nothing significant in terms of functionality (and performance), and you even have capabilities beyond XQuery 1.0 in SQL/XML! You can build sophisticated applications processing XML data using SQL/XML with XPath today.
XPath is used to navigate within an XML document, but it cannot construct a new document. XQuery includes XPath as its sub-language for the navigational capability. XQuery includes FLWOR expressions and document constructors as two other major features for more complex joins and document generation, among other expressions, such as if-then-else.
SQL/XML queries can embed XPath expressions. An XPath expression embedded inside the XMLQUERY() scalar function can extract pieces of documents. SQL XMLTABLE() function is one of the most powerful functions in processing XML, and with XPath, it provides the "FLW" (for-let-where) capability of a FLWOR expression with "tuples" as the result. SQL XMLAGG() function together with SQL XML constructor functions provides the "OR" (order by-return) part of a FLWOR expression. You can pretty much produce the same result as XQuery using SQL/XML. To prove that, I have converted almost all the queries in W3C XQuery use cases to SQL/XML with XPath (except for 2 due to missing fn:namespace-uri() from DB2 9 for z/OS). The queries are downloadable from here, and executable on DB2 9 for z/OS.
What about performance? You may ask. From DB2 for Linux, UNIX, and Windows (LUW) experiences, SQL/XML queries usually perform equally well or better. For example, when you need to have some grouping beyond the natural hierarchy in XML data, in XQuery, you need to use fn:distinct-values() first, and then for each value find interested aggregate data, which is expensive. In SQL, you can use explicit GROUP BY clause. This is where SQL/XML goes beyond XQuery. Furthermore, you can use XMLTABLE() function to create relational views on XML data, and apply all available SQL functionality over that data for reporting and analytics, including BI functions. This is another area where SQL/XML goes beyond XQuery 1.0. See there articles about XMLTABLE() part 1 and part 2 for more details of its usage.
Looking beyond the languages themselves, we see SQL has well established standardized application environment support. XQuery could be used in any environment with XML input and produce XML as output. However, the XQuery environment takes time to standardize, build and mature. If you want XQuery to access relational data or hybrid relational-XML data, there is no native interface of its own. Use XQuery as the primary (top-level) language embedded/invoked in other host languages? Not many vendors support that from the popular database servers, and no standardized interfaces except for Java, which did not gain much acceptance. XQuery supported by DB2 for LUW as a top-level language does not return rows and does not support host variables or parameter markers yet like in SQL. In contrast, SQL/XML makes extending existing applications with XML capability much easier.
All these reasons led to SQL/XML being the dominant language for XML database application development today. That being said, XQuery adopts a new language style to weave construction with other expressions, which is very nice for document generation. You will be able to use the XQuery features in our next major release.
Since DB2 9 for z/OS beta time in early 2006, I have been frequently asked by customers the question of the value of pureXML in DB2. Although more and more folks now realize the value of XML support in DB2, I'd like to touch upon this topic here.
XML is ubiquitous and is being used to represent all kinds of data in industries, government, and academics etc. If you are an application developer in the enterprise environment dealing with vastly increasing XML data asset and ever-changing business demand and are dissatisfied with the limitations of traditional relational databases, DB2 9 for z/OS provides XML management capability as well as relational data management. Unlike XML-enabled databases and other database offerings that rely on Large Object storage or transform XML into relational data, the pureXML technology is specifically designed and optimized for XML hierarchically structured data, and provides proven enterprise-class reliability, availability, scalability, performance and security for XML data that you have come to expect from DB2 for z/OS.
Since the XML data model is hierarchical, the same as IMS. Quite often, people ask what the differences are between XML and IMS. I think there are two: XML data is very flexible while IMS data structures are rigid; and XML has high-level query languages while IMS has procedural DL/I as its native language (although our IMS friends provided SQL and XQuery interface). SQL/XML with XPath provided in DB2 9 for z/OS makes application processing XML much easier.
We've seen the following XML usage scenarios in DB2, with more and more interesting application scenarios popping up. So this list won't be complete. Let us know if you have interesting scenarios to share.
- The first and most direct case is to process XML data, including industry standard XML format (such as FIXML, FpML, ACORD, UNIFI, MISMO etc.), forms and reports (such as XBRL), you can store it, and retrieve it, just like relational data. If you use COBOL or PL/I, to make up the weak XML processing capability in these languages, you can have applications to invoke SQL to process XML data. If you need to connect to the existing back-end systems, you can use the XMLTABLE function to convert XML data into relational views, while you can develop new applications on the XML data.
- Second, pureXML can help develop applications that handle versatile schemas that change frequently, and also help develop end-user customizable applications, which is particularly important for ISVs and IT service departments in large enterprises, where you frequently need to adjust the applications for end-users for diverse information, such as product specification and customer information.
- The third application scenario is to process sparse attribute values, such as medical records, or forms, where there are many fields overall, but only a few of them are applicable for each case. If you use relational approach, you would have to use a table very wide, with many many columns, but most of the column data are null. And you can use one XML column to handle that because XML can hold as many items as you want in a single document.
- The next usage is for object persistence. If you want to persist objects in an application, if you use a relational database, you would usually normalize objects into multiple tables, it's like you disassemble a car when you park in your garage overnight, certainly not convenient. Now with XML, it's much more flexible, you can use a single column to contain at least hiearchical data. More importantly, you can create indexes on these persistent objects. Unlike LOBs, you have no way to do that. So that makes XML for persistent data much more efficient when you need to search. Do you ever have an experience that you wish you could store an array into a single column yet you still can search it? Now you can achieve that using XML. In an extreme case, you could design all the tables with a primary key and a single XML column.
- Yet another application scenario is to migrate from legacy data models, such as network or hieararchical data model. If you migrate a hiearchical data model to a relational model, you need to introduce artificial keys, but with XML you don't need that, it's straight-forward, and you will have benefit of a high-level declarative query language.
- Next application scenario is to generate Web pages because you can use XHTML for web pages that can be generated directly from an SQL/XML statement.
- Last but not least, you can develop web services using DB2 applications to provide or consume web services directly because the web services use XML data. XML support in DB2 enables end-to-end XML solutions in an SOA environment.
In summary, DB2 pureXML makes XML data consumable, and provides the following business value:
- First, it will accelerate application development, reduce system complexity, and improve developer productivity. This will lead to improved time-to-market and reduce IT backlog.
- Second, it increases business agility, it will be able to help develop end-user customizable applications, easily accommodate the changes to data and schemas, and update applications rapidly and reduce the maintenance cost.
- Third, it can improve the business insight, help you develop applications that access information in otherwise unexploited documents, including for business intelligence and business monitoring.
- Last, you no longer need to store XML in a separate system, you can consolidate system resources onto System z, to reduce floor spaces, lower the energy consumption and people cost, and also use specialty engines for XML processing with low CPU cost, and increase security of critical XML data and simplify regulatory compliance.
If you have projects that you feel will benefit from pureXML, I urge youto consider starting with a pureXML PoT I mentioned last week.
The views expressed here are mine and do not necessarily reflect IBM's official position. Contact me at gzhang at us.ibm.com if you'd like a private conversation on the topic.
On this past Monday and Tuesday, I attended the first DB2 9 for z/OS pureXML Proof of Technology (PoT) event in St. Louis, organized by Paul Bartak, one of our Senior Executive IT Specialists in the field, at the IBM Customer Briefing Center facility near St. Louis International Airport.
I stayed there for two days. The first day is for a group of IBMers (with one customer who couldn't come the second day). We listened to Paul's presentation and exercised through the labs, learning pureXML and testing the PoT. I had fun setting up the network for ThinkPad's with VMWare, and testing connections to the DEMOnet DB2 9 for z/OS on a native z machine at Austin in the morning. I was amazed that a bunch of software guys could handle the hardware and network wiring without too much trouble (well, with some remote assistance). We even identified a malfunctioning 5-port Ethernet switch.
On the second day, there were 12 participants, from nearby three DB2 for z/OS clients, including a university from Illinois. They are DBAs, application architects and developers, and data architects. Paul gave a presentation, about 75 minutes, covering the basics of XML and pureXML features, to prepare for the labs. Then everyone went through the labs on one's own pace. I was impressed how fast the participants did. It took me about 3.5 hours to finish the lab on my first-day trial. But I saw many of these customers finished the lab in a shorter time than I did! One DBA told me that she really liked it. She had the concept and now with the lab, it's really flowing. Oh, she thanked me for delivering XML capabilities in DB2 for z/OS.
I had chance to chat with some of the participants on their intended use of pureXML. They were thinking to use XML to keep logs for auditing, store diverse system configuration information, or store purchase orders, or even for displaying data structures during debugging, etc. And I also learned some features that would be nice to have in the future.
This pureXML PoT uses a real DB2 9 for z/OS system, and is unique in the following aspects:
- It covers all the pureXML main features, including DBA tasks, SQL/XML queries with XPath, schema validation and decomposition, with real examples.
- It uses sample tables to generate XML data so there is no need to prepare XML data from other places.
- It uses the familiar tools, SPUFI, SDSF, CLP and also IBM Data Studio for schemas, and OSC for query plans and tuning.
- It includes all the DDL, Schemas, and Queries so you can work further on your own after the PoT.
We are lucky to have someone like Paul, who knows what customers need, understands the technology, and put lot of efforts to deliver a PoT that people can really learn to understand the technology. I'd like to thank Mark Wilson for his contribution to this PoT also.
This pureXML PoT will be offered in different areas. If you are interested in (having your folks) attending such pureXML PoT, please contact your local DB2 advisor, or contact me or Paul. It will also be offered as a Hands-on Lab during IOD 2008 Conference at Las Vegas,
Session: HOL-2584A DB2 9 for z/OS pureXML for DBAs
Time: Wed, 29/Oct, 10:00 AM - 01:00 PM
Location: Mandalay Bay South Convention Center - Lagoon D
Take note if you are attending IOD and interested in playing pureXML on DB2 9 for z/OS there.
--Guogen (Gene) Zhang (GGZ)[Read More
Last week one of our business partners, Thunderhead Ltd
that they were developing a new component (called Context Engine) for their Thunderhead NOW platform that is based on DB2 pureXML. They say that DB2 pureXML is "an ideal complement" for their product. But DB2 is not just a complement. Thunderhead is actually doing a deep embed of DB2 pureXML, which means that instead of two separate products, DB2 pureXML will actually be embedded into Thunderhead's new Context Engine component. This should make it easier for customers to purchase, since they don't have to buy two separate products, but more importantly, it means that customers will get tighter integration of the technologies.
This is not the first time that we have worked with Thunderhead around DB2 pureXML. Earlier this year, we worked with them to develop a solution for derivative trade confirmations. And before that, we worked with them on a solution for insurance correspondence automation.
So if you don't want to wait until the Context Engine comes out later this year to see Thunderhead with DB2 pureXML, check out these solutions. In fact, this week at Sibos (the world's premier financial services event, according to their website), the solution for the derivatives trade confirmations is being demoed at the IBM booth. So if you just happen to be there, stop by and take a look! (Oh, did I mention that Sibos was in Vienna this year?)
-- Kate Riley Tennant[Read More