On this last day of year 2008, I'd like to share with you on how easy it is to generate XML from relational data using SQL in DB2 V8 and DB2 9 for z/OS.
To start with, I'd like to show a simple example of SQL functions that generates the following XML:
<name> <firstname>John</firstname> <lastname>Doe</lastname></name>
The SQL functions to construct this XML is the following:
XMLELEMENT(NAME "name", XMLELEMENT(NAME "firstname", 'John'), XMLELEMENT(NAME "lastname", 'Doe') )
As you can see, the SQL functions nest exactly corresponding to the XML data nesting structure. These functions are standard SQL XML constructors or sometimes called XML publishing functions. Notice that the "NAME" keyword is required by the standard to indicate that an SQL ID ("name") is used as a name for the element, as opposed to using the value of a column ("name") in other SQL functions. Double quotes are necessary to keep the name case-sensitive.
In the next example, I'd like to show how you can group a set of elements into one XML value and nest under an element using XMLAGG. This query assumes you have an employee table:
+---------+--------------+------------+--------+ | dept | firstname | lastname | ... | +---------+--------------+------------+--------+ Shipping White Smith ... Shipping Paul Taylor ... Shipping John Doe ... ...
And you want to group employees under a department element like the following:
<Department name="Shipping"> <Emp> <firstname>John</firstname> <lastname>Doe</lastname> </Emp> <Emp> <firstname>White</firstname> <lastname>Smith</lastname> </Emp> <Emp> <firstname>Paul</firstname> <lastname>Taylor</lastname> </Emp></Department>...
The query to generate this XML data is as follows:
SELECT XML2CLOB( XMLELEMENT(NAME "Department", XMLATTRIBUTES (e.dept AS "name" ), XMLAGG(XMLELEMENT(NAME "Emp", XMLELEMENT(NAME "firstname", e.firstname), XMLELEMENT(NAME "lastname", e.lastname) ) ORDER BY e.lastname ) ) ) AS "dept_list"FROM employees e GROUP BY dept;
This is what people typically call "de-normalization" (of relational data). Notice that XMLAGG takes ORDER BY clause to specify the order of elements within a group. In this example, it's to order the elements based on the lastname. This query should work on both V8 and V9. In DB2 9, additional constructors are provided to make them complete. V8 queries continue to work in DB2 9.
There are many advantages of using SQL to generate XML from relational data. Here are a few of them:
- It is easy to extend existing COBOL or PL/I applications to generate XML by using SQL. It works for non-Unicode data.
- It is much easier to generate XML than using DOM in programming languages like Java and C/C++, enabling higher productivity.
- It makes applications light-weight, with high-performance. It delivers the XML value in one chunk, instead of piece-by-piece.
What is a QName?
Before listing some additional resources, I'd like to trickle in some terminology related to XML for beginners (please skip if you already know). An element or attribute has a name, officially called a QName, for Qualified Name, such as "hr:name", where "hr" is a prefix and "name" is a local name. Both prefix and local name are NCNames, for No-Colon Names (names without a colon in them). The prefix is for the namespace, and needs to be declared with a namespace declaration like the following:
<hr:name xmlns:hr="http://www.example.com/humanresource"> ... </hr:name>
Where "xmlns" signifies a namespace declaration, and "hr" is the namespace prefix being declared. The value "http://www.example.com/humanresource" is a namespace name or namespace URI. The reasons that XML namespace is designed this way are the following:
- To avoid conflict (the purpose of a namespace), it has to be some unique identifiers across the Web. That will be the URI (Uniform Resource Identifier).
- A URI cannot be used directly in an element name or attribute name because it does not follow the syntax rules for element/attribute names in general.
- A prefix is used instead to make it follow the name syntax rules, and it is shorter usually.
An element or attribute is said to be in the scope of a namespace if it is between the start tag (inclusive) containing the namespace declaration and the corresponding end tag. And an element has a set of in-scope namespaces. A namespace declaration is said to be superfluous if the namespace being declared is already in the scope.
An NCName becomes a QName if the prefix is empty, that is, either there is no namespace (No Namespace is used), or a default namespace is used (a namespace is bound to the empty prefix). A colon is not used in these cases. The following example illustrates a default namespace declaration:
<name xmlns="http://www.example.com/humanresource"> ... </name>
For more SQL/XML constructor details and examples on generating XML from relational data, please look at this "old" presentation of mine from IM Tech Conference '05: Generating XML from Relational Data Using SQL with Ease. Some of the examples are included in the XMLQuickDemo 7XMLGEN1.CLP and 8XMLGEN2.CLP. I've also made XML4TABLE package public. This package contains two SQL PL stored procedures for DB2 9 for z/OS: XML4TABLE and XML4ENTITY1, which can generate XML for an entire table or a given entity from multiple RI-related tables. Read the readme.txt file in the package for details. Let me know if you have any feedback.
Also, check IBM Data Studio and Rational Data Architect for tool support in generating SQL/XML queries to generate XML from relational data. And don't forget to check SQL Reference and XML Guide, and other online resources.
I wish you a very happy and prosperous new year 2009!
-Guogen (Gene) Zhang[Read More
If you are a System Programmer or System DBA, you would find nothing significant that is really new in pureXML for DB2 9 for z/OS, thanks to our architectural design principle: leveraging mature optimized infra-structure in DB2 for XML data management. We use familiar table spaces (pages and records), indexes, buffer pools, and locking scheme for XML data, and provide DBAs the same utilities and tools to administrate the familiar objects, even though XML data model is hierarchical and even though we provide application developers "revolutionary" new weapons (SQL/XML with XPath) to process XML data in DB2.
If this is the first time you looked at XML in DB2 9 for z/OS, you can take a quick look at XMLQuickDemo, where the second CLP 2ListObjects.CLP finds the DB objects involved in storing an XML column data, for background.
You probably would be a bit surprised (and feel relieved?!) when you realized (or you are told now) that there are no new utilities specific for XML, and little setup to start with XML in DB2 9 for z/OS. I've looked again and again to see what's new specific to XML. I've found the following list.
- Implicitly created DB objects for XML – DBA cannot create explicitly.
- XML keyword in some utilities, such as LOAD, UNLOAD, LISTDEF etc.
- A new XML lock type, ID '35'x in traces, and it appears in IFCID 20, 21, 107, 150, 172, and 196.
- XMLDATA in XML tables contains StringIDs in a catalog table SYSIBM.SYSXMLSTRINGS (dictionary) specific to each catalog. Therefore, UNLOAD FROMCOPY is restricted. And copies from DSN1COPY cannot be moved to another DB2 system.
- XML indexes: XPath and keys - for Application DBAs.
- XML Schema registration - for Application DBAs.
In the following, I will briefly describe what is new in pureXML for system programmers and DBAs, and what remains the same.
Setup and Configuration
As I indicated in a previous posting, getting started with pureXML requires almost nothing to set up once you get to DB2 9 for z/OS NFM. You probably only need to take care of some authorization issues, business as usual.
When the application folks get serious, you may need to take care of the zparms that are related to XML: XMLVALA, XMLVALS, LOBVALA, LOBVALS - these four are related to virtual storage limit. LOB zparms are involved because LOB manager is used for XML data bind-in and bind-out. Also the default buffer pool for XML data (BP16k0) needs to be changed.
XML Schema Repository (XSR) setup is only needed if your applications use schema validation or annotated-schema-based decomposition. Only then will you need to take care of Java stored proc and WLM setup. You would deal with SDSNLOAD and SDSNLOD2 also.
Utilities and Tools
No new utilities for XML, and no new tools for XML performance monitoring. You use all the existing and familiar utilities and tools to cover XML objects. You include XML objects using LISTDEF for backup and recovery operations. Some minor restrictions may apply to XML objects in some utilities. XML performance problems can be analyzed through accounting traces and performance traces. Business as usual.
XML indexes and XPath, and Access Types
Indexes are critical for query performance. No difference for XML queries, they require XML indexes. This is something new to application DBAs and architects. XPath is used to index XML data that is searched frequently. Even the new XPath is involved, the index infra-structure remains the same. One XML document may generate zero, one, or more index entries for an XML index.
Also some minor new access types are introduced when using XML indexes. They are DX/DI/DU for DOCID list access (single index), DOCID list ANDing, and ORing. You still get "R" for DOCSCAN evaluation of XMLEXISTS predicate.
XML Schema Registration
In case your applications would like to use XML schemas, you need to set up XSR, and register XML schemas. Something new to application DBAs. There are tools to help, such as IBM Data Studio, in registering XML schemas. If you just want to use a simple tool, I recommend CLP (Command Line Processor).
I'd like also to emphasize that knowledge in XML, XPath, and SQL/XML will make you much more valuable at this SOA age, and in this tough economy. Take advantages of XML and pureXML in DB2 as many other people do! Let me know if you have any questions and concerns.
No matter how familiar one is with SQL, DB2, and relational data model, the first time getting exposed to XML and XPath, she would likely feel overwhelmed. Getting into the XML world is not a small step. But here I'd like to demystify XPath for SQL people who is just getting started with XML and XPath. I will give you a very gentle introduction to XPath and how you can use it to query XML data in SQL/XML.
It is actually very easy to start: we need to understand thatXML data model is a natural but significant extension to the relational model, it features two very powerful structural capabilities - nesting and repeating. First, it allows nesting, to any level (DB2 allows 128 levels of nesting maximum). Nesting is very common in data structures. For example, you want to separate a name into first name and last name, then you have two levels. You can view a table to have three levels: table level, row level, and column level. When you reference a column with tablename.columnname, you actually use tablename to reference each row in the table, then use the column name to get to a column in the row. In most programming languages, you use dot (.) to separate the field names at each level, such as name.firstname. You can have an XML document as simple as name with two sub-elements - firstname and lastname in an XML column like this:
<name> <firstname>Guogen</firstname> <lastname>Zhang</lastname></name>
Now how can you get the firstname and lastname in the document? XPath is the only solution in SQL: You can use /name/firstname to get the firstname, and /name/lastname to get the lastname. So what's the difference of XPath /name/firstname from name.firstname? You start with a slash (/), and replace the dot with slash. Each slash gets you to the next level. You can use that to get any field or structure in XML data.
The second feature is repeating, like an array, but very flexible. Take a simple example, you can list multiple phone numbers in one XML column, and also associate an attribute for each phone number element to tell it's a wired phone or cell phone, or work phone or home phone:
<phones name="Guogen Zhang"> <!-- fake numbers --> <phonenumber type="home">408-555-1234</phonenumber> <phonenumber type="cell">408-555-2345</phonenumber> <phonenumber type="work">408-463-2012</phonenumber></phones>
To get a specific phone number, you can use array index notation:/phones/phonenumber.  is a short-hand for [fn:position()=2]. You can generalize this predicate to other search condition: /phones/phonenumber[@type ="cell"]. This will get the second phone number also. Here @type is to refer to attribute "type". Attributes are those things in the start tag of an element (between < and >). And you use quotes around "cell" to mean a string comparison. In contrast, for a numeric comparison you would use numeric values without quotes. For example, size = 5.
Two more commonly seen "operators" are "//" and ".." (abbreviated axes). "//" is to look at any level under a certain level (roughly descendant-or-self). For example, //phonenubmer is looking for phonenumber in the entire document. ".." is going back up one level (parent axis). Now you should know why it's called XPath, as it's like file paths in a hierarchical file system.
Now let's look at where XPath is used in SQL/XML. XPath is used in the XMLEXISTS predicate, and the XMLQUERY and XMLTABLE functions. For example, the following query retrieves the quantities of "Baby Monitor" items from purchase order documents:
SELECT XMLQUERY('declare namespace ipo="http://www.example.com/IPO";/ipo:purchaseOrder/items/item[productName = "Baby Monitor"]/quantity' PASSING XMLPO)FROM PURCHASEORDERSWHERE XMLEXISTS('declare namespace ipo="http://www.example.com/IPO";/ipo:purchaseOrder/items/item[productName = "Baby Monitor"]' PASSING XMLPO)#
In the above, we assume we have a table PURCHASEORDERS containing XMLPO column of type XML. In XMLQUERY and XMLEXISTS, PASSING is the keyword to pass the XML column from the SQL world into the XML (XPath/XQuery) world. The XMLEXISTS in the above example is to search for purchaseOder with an item whose productName is "Baby Monitor", while XMLQUERY is a scalar function to extract quantity for the baby monitor items. XMLEXISTS is the only main predicate on XML data in the SQL world (the other predicate on XML is IS [NOT] NULL). You cannot use other SQL comparisons on an XML value, but you get many comparisons within the XML (XPath) world. Also in the above query example, I illustrated the use of XML namespaces. To see purchase order document examples and more query examples, see the XMLQuickDemo I referenced in the previous two blog entries.
Isn't XPath easy to start with? For more details and tutorials on XPath, you can Google "XPath tutorial" to get very good web sites on the top of the list, such as w3schools.com and zvon.com. Try some examples, you will soon become comfortable with XML and XPath! (well - you can pick up the terminology gradually.)
Yes, you almost need nothing to set up before you try out pureXML in DB2 9 for z/OS if you are in DB2 9 NFM and have SPUFI for DB2 9. Here is how: download XMLQuickDemo I just posted in my previous blog entry. And log on to TSO and get to SPUFI, and copy the SQL statements from the download, and run from there.
Within the downloaded zip file, statements from the files with a name starting with "1" to "8" can be tried out in sequence from SPUFI without WLM/Java/XSR setup. "ADropTable.CLP" is used to clean up after yourself.
Chances are that you will run successfully. Possible problems you may encounter:
- Authorization problem: you need the privilege to create table and use bufferpool BP16K0 for XML column data.
- SPUFI abend, most likely you need the correct SPUFI version for V9.
- SQL error complaining z/OS XML system services, most likely you are using z/OS R1.7 that requires APAR OA16303 for XML system services.
- DB2 complains syntax on XPath, most likely you need to set Editor case-sensitive, also pay attention to statement termination character as semi-colon may be used within XPath so # is used.
- Also set output record length long enough to see the output. Otherwise there will be truncation of XML result. Check continue after warning to get all rows after truncation.
When you try your own XML documents and queries, especially with large XML documents or results, you may encounter sqlcode: -904 sqlstate: 57011sqlerr Message: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C900D1, TYPE OF RESOURCE 00000907, AND RESOURCE NAME... which means you need to increase LOBVALA zparm setting, default was 1024KB. DB2 uses LOB functionality for XML data bind-in and bind-out, but no LOB used for XML database storage.
DB2 9 XML column type and built-in functions on XML type do not require XML schemas. Therefore, if you don't use XML schemas, you don't need XSR, which requires Java stored procedure setup and WLM setup. Only when you need XML schemas for schema validation or annotated-schema decomposition, you need the additional setup.
Once you go through these, you can try to embed SQL/XML queries in your COBOL or PL/I applications. You can just use LOB or VARCHAR host variables to hold XML data.
Happy playing with pureXML!
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.
In IOD Global Conference 2008 at Las Vegas held at the end of October, there were some discussions on what computing era we are in. The computing era means the (new) dominant computing architecture(s) at the time. We probably had the mainframe era, PC era, the client-server era, the networking/internet era, and I thought currently we are in the cloud computing era!
The question was really related to the new major business application usage. There were days of batch data processing, OLTP, office automation, MIS, decision support systems/OLAP, business performance optimization, etc. There was no simple phrase to describe what business applications are used for today - it's going up in the food chain.
I'm more interested in a database era here today. Looks to me we have entered the era of XML databases in the database history. The first 15-20 years of database history was the era of hierarchical and network databases, when IMS was one of the most popular databases. The following 20 years is the era of relational databases, with quite a few popular products, including DB2. Then there was a period of time that object-oriented databases attempted to enter the mainstream, but never succeeded for whatever reasons (well - OODBs are really mainly for CAD type of applications).
Given the customer demand and real value of XML databases to the business for its flexibility and productivity uplifting, the era of XML databases is real, and it will last probably 20 years as a major database technology development and market growth. IMS and relational databases are not going away. In fact, they continue to grow. What I call the era is due to the heavy investment in the technology development, and major adoption and market growth.
To me, this year marked the 1st anniversary of the beginning of the XML database era as DB2 9 for z/OS with native XML support was GAed last year. Many vendors and customers share the same view. Last year, we had ISVs and cutting edge customers telling us they felt that XML was going to be big and looked same as the introduction of relational databases. This year, more customers said to me that they better learn more about XML and prepare for the inevitable adoption of DB2 XML database technology.
Do you share the same view? If you have doubt, do some investigation, or wait for history to tell if it's true or not. But I'm a total believer!
-Guogen (Gene) Zhang (GGZ)[Read More
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
Welcome to the DB2 pureXML team blog!
In this blog, we plan to keep you up-to-date with the latest DB2 pureXML news. We will:
- Keep you up-to-date with the latest news
- Let you know about articles and webcasts that can help you
- Give you tips and tricks for working with pureXML
- Tell you about new features that are being released
- And much more
We believe that DB2 pureXML is the best XML storage product in the market, and we look forward to making sure you have the latest information about DB2 pureXML.