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.
You may have heard and seen quite some value claims about pureXML in DB2 9, and a lot of details about the pureXML features. You may wonder how they connect in the real world. I will try to present the scenarios and how pureXML features can play an important role, abstracted from real-world experiences, at the teleconference to be held on April 21, 2009. Here is the invitation in PDF. I will be speaking and answering your questions live. The presentation focuses on the scenarios that pureXML can deliver the values and how you can use pureXML to:
- Accelerate application development,
- Achieve greater business agility, and
- Improve business insights.
In addition, System z has its well-known advantages of reliability, availability, scalability, security, and savings in space, power consumption, and people cost. DB2 9 for z/OS pureXML and other XML capabilities on System z make your applications less reliant on additional systems to handle XML, reducing system complexity and improving performance.
In today's extremely competitive environment, time-to-market may determine life or death of a business, or to a less extent, profit or loss. In that kind of situation, using feasible technologies to deliver a service quickly becomes critical. And pureXML is one element of this type of technologies.
Please invite your clients, colleagues, and friends to register for this teleconference: ibm.com/software/systemz/telecon/apr21. See you then.
XML is all about its flexibility, and performance, of course, in dealing with the flexibility. XML documents that are stored in a DB2 XML column are untyped. You can specify the types in XPath expressions explicitly (using cast, such as xs:double() ) or implicitly (through operators). In many cases, explicit type casting can be avoided in XPath because of implicit casting. However, although general comparisons take sequences as operands, arithmetic operators and cast do not support sequences as operands, which could lead to cardinality errors that should be taken care of. Here I will illustrate when type casting is needed, and how to avoid cardinality errors using some examples. You may have seen the highlights of the query examples in my presentations before.
Let's assume we have created a table and have inserted one document as follows:
CREATE TABLE CATALOG(XCAT XML);
INSERT INTO CATALOG VALUES('<?xml version="1.0"?><catalog> <category cid = "001" name = "Electronics"> <product pid="101"> <name>Personal computer</name> <model>ThinkCenter 100</model> <RegPrice>1295</RegPrice> <SalePrice>998.95</SalePrice> </product> <product pid="102"> <name>IPOD</name> <model>30G</model> <RegPrice>298</RegPrice> <SalePrice>268.95</SalePrice> </product> </category> <category cid = "002" name="Apparel"> <product pid="201"> <name>Shirt</name> <description>Man''s Demin</description> <size>S</size> <size>M</size> <size>L</size> <size>XL</size> <RegPrice>60.00</RegPrice> <SalePrice>39.99</SalePrice> </product> <product pid="202"> <name>Shoe</name> <description>Man''s Leather</description> <size>8</size> <size>9</size> <size>10</size> <RegPrice>83.00</RegPrice> <SalePrice>49.99</SalePrice> </product> </category></catalog>');
Taking care of types
Example 1. "Find all the products in the Catalog with RegPrice > 100"
-- No explicit cast is needed because of the numeric comparisonSELECT XMLQUERY('/catalog/category/product[RegPrice > 100]/name' PASSING XCAT)FROM CATALOG;
-- result:<name>Personal computer</name><name>IPOD</name>
Example 2. "Find all the products on sale in the Catalog"
-- Explicit cast xs:double() is needed. Only one at either side of the comparison is enough.SELECT XMLQUERY('/catalog/category/product[RegPrice > xs:double(SalePrice) ]/name' PASSING XCAT)FROM CATALOG;
-- result:<name>Personal computer</name><name>IPOD</name><name>Shirt</name><name>Shoe</name>
-- If no cast, the comparison will be string comparison:SELECT XMLQUERY('/catalog/category/product[RegPrice > SalePrice]/name' PASSING XCAT)FROM CATALOG;
-- result: not intended - missing Personal computer because "1295" < "998.95"<name>IPOD</name><name>Shirt</name><name>Shoe</name>
Example 3. "Find all the products with more than 10% discount in the Catalog"
--No explicit cast is needed because of arithmetic multiplicationSELECT XMLQUERY('/catalog/category/product[RegPrice * 0.9 > SalePrice ]/name' PASSING XCAT)FROM CATALOG;
-- result:<name>Personal computer</name><name>Shirt</name><name>Shoe</name>
Example 4. "Comparisons tolerate type errors"
The following examples show comparison operators tolerate type errors (treat as comparison fails). Note that the "size" elements contain numeric values as well as letters.
-- Find product with size greater than or equal to 9SELECT XMLQUERY('/catalog/category/product[size >= 9]/name' PASSING XCAT)FROM CATALOG;
-- Find product with size equal to "L" or "XL"SELECT XMLQUERY('/catalog/category/product[size = "L" or size = "XL"]/name' PASSING XCAT)FROM CATALOG;
You could use numeric comparison and string comparison in the same query:
/catalog/category/product[size >= 9 or size = "L" or size = "XL"]/name
which results in what you would expect.
Avoiding cardinality errors
Let's assume we keep some regular price and sales price history for the products, and make the document look like the following:
INSERT INTO CATALOG VALUES('<?xml version="1.0"?><catalog> <category cid = "001" name = "Electronics"> <product pid="101"> <name>Personal computer</name> <model>ThinkCenter 100</model> <RegPrice from="2005-06-01">1295</RegPrice> <SalePrice from="2006-01-10" to="2006-01-31">998.95</SalePrice> <SalePrice from="2006-02-01" to="2006-02-15">1059.98</SalePrice> </product> <product pid="102"> <name>IPOD</name> <model>30G</model> <RegPrice from="2006-01-01" to="2006-09-30">298</RegPrice> <RegPrice from="2006-10-01">269</RegPrice> <SalePrice from="2006-06-08" to="2006-06-30">268.95</SalePrice> </product> </category>...<catalog>');
Let's revisit the examples above. Example 1 does not have a problem as the general comparison deals with sequences. However, Examples 2 and 3 encounter cardinality errors.
Example 5. Example 2 causes an error on the new document: xs:double() does not take a sequence of more than one item (two SalePrice nodes).
SELECT XMLQUERY('/catalog/category/product[RegPrice > xs:double(SalePrice) ]/name' PASSING XCAT)FROM CATALOG;
sqlcode : -16003 sqlstate: 10507sqlerr Message: AN EXPRESSION OF DATA TYPE ( item(), item()+ ) CANNOT BE USED WHEN THE DATA TYPE xs:double IS EXPECTED IN THE CONTEXT. ERROR QNAME=err:XPTY0004
Note that ( item(), item()+ ) means a sequence of more than one items. To avoid the cardinality problem, we can use "self" in the following XPath expression, where "SalePrice" could result in multiple items, but "xs:double(.)" takes one item at a time:
SELECT XMLQUERY('/catalog/category/product[RegPrice > SalePrice/xs:double(.) ]/name' PASSING XCAT)FROM CATALOG;
-- result:<name>Personal computer</name><name>IPOD</name><name>Shirt</name><name>Shoe</name>
Example 6. Example 3 causes an error on the new document because arithmetic multiplication does not take a sequence of more than one item (two RegPrice nodes).
SELECT XMLQUERY('/catalog/category/product[RegPrice * 0.9 > SalePrice ]/name' PASSING XCAT)FROM CATALOG;
sqlcode : -16003 sqlstate: 10507sqlerr Message: AN EXPRESSION OF DATA TYPE ( item(), item()+ ) CANNOT BE USED WHEN THE DATA TYPE xdt:numeric IS EXPECTED IN THE CONTEXT. ERROR QNAME=err:XPTY0004
Similarly, we can use "self" in the following XPath expression to avoid the cardinality error:
SELECT XMLQUERY('/catalog/category/product[RegPrice/(. * 0.9) > SalePrice ]/name' PASSING XCAT)FROM CATALOG;
-- Result:<name>Personal computer</name><name>Shirt</name><name>Shoe</name>
Since an attribute value is treated as xs:untypedAtomic, there is no cardinality error possible on attributes.
I hope the above examples help you understand the flexibility of XPath in terms of types. And I hope you've learned the "self trick," and can take measure now to avoid possible cardinality errors in SQL/XML queries when you make the documents more flexible in the future.
To make it easier for DB2 for z/OS friends to find pureXML information specific to DB2 for z/OS, we've just created a DB2 for z/OS pureXML wiki page under DB2 XML wiki page to list z/OS-specific information. It may contain cross-references to other areas, and has initial content right now. We expect the content to accumulate over the time.
In the new wiki page, I've just uploaded DB2 9 for z/OS pureXML podcast series, recorded by Guogen Zhang (that's me :-) ) in 2008. This is a 10-part introductory podcast series on pureXML business value and technical knowledge. The first part covers overview and business value, and the follow-on 8 parts cover different aspects of pureXML, from query, schema, to utilities and performance. The last part talks about best practices. Each part is about 10-15 minutes. The podcast is in mp3 audio format together with transcripts in PDF.
Give us feedback on whether this introductory podcast series is useful to you. If so, we will prepare follow-on podcast for more advanced topics of your interests. Thank you.
Check back the DB2 for z/OS pureXML wiki page often. You never know what will pop up there.
-Guogen (Gene) Zhang[Read More
(Updated on April 22, 2009 PST)
I'd like to share with you the following noteworthy courses, articles, and news related to DB2 pureXML:
Migration of DB2 for z/OS systems to a new release is a very important task that requires careful planning and systematic execution, which takes time. Yet pureXML in DB2 9 for z/OS is very attractive to application developers, but it requires DB2 9 NFM. If you have a need to start an application development using pureXML and cannot seem to wait for the DB2 systems to get to DB2 9 NFM, you may want to consider the following option (which to many is not news): developing the new application using DB2 Express-C using the pureXML features and IBM Data Studio on a Windows or Linux platform (both are no charge). You may get in sync with the migration to DB2 9 NFM for application system testing, performance testing, and deployment. Here is some more info.
DB2 Express-C is a no-charge community edition of the DB2 for LUW data server. It can be downloaded from DB2 Express-C download. Before you start, you may want to look at the Getting Started book.
You also need to download the right version of DB2 Express-C for your platform. If you have a Windows platform, look at this page to determine if your Windows is 32-bit or 64-bit. If the properties of your computer does not say 64-bit, then it's likely to be 32-bit.
You also need an IBM ID to download for license agreement. Getting an IBM ID is technically very easy. Use IBM Installation Manager to download and install, which is faster. After download, you need to unzip it and run the setup.exe or something like that. It should be fine to use the typical installation type. Extremely easy :-).
IBM Data Studio Developer
To develop using SQL/XML, Java, stored procedures, and pureQuery, you can start with the Data Studio Developer no-charge features. After you install DB2 Express-C and launch it, you can download Data Studio Developer and install it. The link is listed on the DB2 Express-C download page also. Again, it's straight-forward.
Using Data Studio Developer to explore SQL/XML queries
This is my Windows XP experience. Launch the Data Studio Developer, you will see the SAMPLE database already listed in the Data Source Explorer, if you launched DB2. Right click on the SAMPLE DB, and choose "connect". Use your Windows username and password to connect. Once it's connected, you can look into "Schemas", and under your username, find "Tables". There are a few tables in the SAMPLE DB that contains an XML column: CUSTOMER, PRODUCT, and PURCHASEORDER that contains some sample data. You can list the table content by right click on the tablename -> "Data" -> "Returns all rows" or "Sample content", to display table content.
From SAMPLE DB name, right click on it -> "New SQL Script", you can copy and paste an SQL/XML query into the window. Then Choose main menu "Run" -> "Run SQL". The status and result will show. Try this query:
SELECT *FROM PURCHASEORDERWHERE XMLEXISTS('declare default element namespace "http://posample.org"; $po/PurchaseOrder[@Status="Unshipped"]' PASSING PORDER AS "po")
Remember, in order to move the queries and applications to z/OS easily, you need to stick to the syntax and scope that is supported by z/OS pureXML (and also by LUW). One of the key detailed differences is that in LUW, you must use a variable, such as $po shown above, as the starting context item for an XPath path expression, which is optional for z/OS. This is consistent for XMLQUERY, XMLEXISTS, and XMLTABLE functions.
Once you see the XML column data, you can click on the icon, and an XML editor window will open.
You could open a new Data Project, from main menu "File" -> "New" -> "Data development Project". Say you created Project1. Within Data Project Explorer, under Project1, right clock on "SQL Scripts" -> "New" -> "SQL or XQuery Script", you can get a SQL Script window also.
If you need to have a Java project, you need to create a project with the corresponding type: from main menu "File" -> "New" -> "Project", a dialogue window will open...
I hope you will like the combination of Data Studio with pureXML, if you haven't tried yet. There are much more features to explore in IBM Data Studio.
After careful review of the XML features in DB2 9 for z/OS and their user experiences and feedback, the XML development team decided that the XDBDECOMPXML stored procedure for annotated schema-based decomposition be deprecated in DB2 9 for z/OS and may be removed in a future release. Users are advised to use the more versatile and powerful standard SQL built-in XMLTABLE function instead for decomposition and other XML processing needs in DB2 9. If you have any concerns, please let us know. You can reach me by emails through db2zxml at(@) us.ibm.com or gzhang at(@) us.ibm.com.
For the alternative, the XMLTABLE function can take as input an XML hostvar, such as :xmlhv, a parameter marker, such as CAST(? AS XML), XMLPARSE(DOCUMENT non_xml_expr), or an XML column, and produce a logical table, which can contain both non-XML and XML results. See SQL Reference XMLTABLE description and the XMLTABLE Category in this blog for other postings related to XMLTABLE usage and pointers.
There are some shortcomings of a XDBDECOMPXML based decomposition solution:1. It requires a schema to be annotated. Schema annotation fixes on a given set of table names - no change possible at runtime.2. Intrinsic annotation requires tedious work when a schema evolves. Such a solution cannot take the advantage of XML schema flexibility provided by pureXML.3. It requires schema validation during decomposition, with slow performance, high CPU cost and no zIIP/zAAP possible. In contrast, the XMLTABLE function does not require a schema, and is eligible for zIIP re-direction, and schema validation, if needed, can be performed in a separate DSN_XMLVALIDATE function that will be eligible for zIIP/zAAP redirection.
Deprecation and future removal of XDBDECOMPXML and XDBDECOMPXML100M will allow us to focus on enhancing the more valuable pureXML features and deliver more value to the customers with better solutions.
In the XMLQuickDemo, I illustrated some simple DDL statements and SQL/XML queries. In the second CLP file (2ListObjects.CLP), I tried to list some database objects related to an XML column. I was a bit lazy for the first query used to find the tables created, as I knew the implicitly created XML table name is "X"+<base table name> for the first XML column (this is only true for the first XML column of the table and the table name is shorter than 18 bytes). So I used this:
WHERE NAME LIKE '%PUR%' AND CREATOR = CURRENT SQLID;
as the WHERE predicate to find the tables just created, including the base table PURCHASEORDERS and internal XML table XPURCHASEORDERS. However, the right query should use the catalog table SYSIBM.SYSXMLRELS to find the XML table for an XML column. Let's assume I have the following two DDL statements to create a table and an XML index, and my SQL ID is DDS0075:
CREATE TABLE MYTABLE(XDOC XML);CREATE INDEX MYINDEX ON MYTABLE(XDOC) GENERATE KEYS USING XMLPATTERN '/form/formdata/data1/*' AS SQL VARCHAR(20);
I should use the following query to find the XML table corresponding to the XML column(s):
SELECT SUBSTR(XMLTBOWNER, 1, 8) as CREATOR , SUBSTR(XMLTBNAME,1,20) as NAME , T.TYPE , T.DBNAME , T.TSNAME , T.COLCOUNTFROM SYSIBM.SYSXMLRELS XT , SYSIBM.SYSTABLES TWHERE XT.TBNAME = 'MYTABLE' -- table name AND XT.TBOWNER = CURRENT SQLID -- creator name AND T.NAME = XT.XMLTBNAME AND T.CREATOR = XT.XMLTBOWNER;
The SUBSTR function is used to shorten the result length for display. The result looks like the following:
CREATOR NAME TYPE DBNAME TSNAME COLCOUNTDDS0075 XMYTABLE P DSN01018 XMYT0000 3 1 record(s) selected
In DB2 9, by default, each table will be created in its own database (when the tablespace is not specified also, see the table below). The XML tables related to the base table will be always in the same database as the base table. It may not be the best to use this default feature, but for simplicity, I use this for the example. Here are more query examples on the catalog tables to find information about other XML objects and their sample results. They are self-explanatory, except for the characters for the types:
- 'P' for both XML table and XML table space;
- 'N' for NodeID index, and
- 'V' for XML Value index.
- (DOCID index is a regular index on the base table.)
ECHO *****************************************************************;ECHO * GET INFO ABOUT XML TABLE SPACES AND STOGROUPS *;ECHO *****************************************************************;SELECT TP.DBNAME , TP.TSNAME , TP.STORNAME , TP.PQTY , TP.SQTYFROM SYSIBM.SYSXMLRELS XT , SYSIBM.SYSTABLES TB , SYSIBM.SYSTABLEPART TPWHERE XT.TBNAME = 'MYTABLE' -- table name AND XT.TBOWNER = CURRENT SQLID -- table creator AND TB.NAME = XT.XMLTBNAME AND TB.CREATOR = XT.XMLTBOWNER AND TP.TSNAME = TB.TSNAME AND TP.DBNAME = TB.DBNAME;DBNAME TSNAME STORNAME PQTY SQTYDSN01018 XMYT0000 SYSDEFLT -1 -1 1 record(s) selected
ECHO ********************************************************************;ECHO * GET INFO ABOUT INDEXES, INDEX SPACES AND STOGROUPS ON BASE TABLE *;ECHO ********************************************************************;SELECT SUBSTR(IX.TBNAME,1,20) as TBNAME , SUBSTR(IX.NAME,1,26) as IXNAME , IX.IX_EXTENSION_TYPE as IXTYPE , IX.DBNAME , IX.INDEXSPACE , IP.STORNAMEFROM SYSIBM.SYSINDEXES IX , SYSIBM.SYSINDEXPART IPWHERE IX.TBNAME = 'MYTABLE' -- base table name AND IX.TBCREATOR = CURRENT SQLID -- table creator AND IX.NAME = IP.IXNAME AND IX.TBCREATOR = IP.IXCREATOR;-- INCLUDES DOCID INDEX, BUT NOT THE NODEID INDEX:TBNAME IXNAME IXTYPE DBNAME INDEXSPACE STORNAMEMYTABLE I_DOCIDMYTABLE DSN01018 IRDOCIDM SYSDEFLTMYTABLE MYINDEX V DSN01018 MYINDEX SYSDEFLT 2 record(s) selected
ECHO ********************************************************************;ECHO * GET INFO ABOUT INDEXES, INDEX SPACES AND STOGROUPS ON XML TABLE *;ECHO ********************************************************************;SELECT SUBSTR(IX.TBNAME,1,20) as TBNAME , SUBSTR(IX.NAME,1,26) as IXNAME , IX.IX_EXTENSION_TYPE as IXTYPE , IX.DBNAME , IX.INDEXSPACE , IP.STORNAMEFROM SYSIBM.SYSXMLRELS XT , SYSIBM.SYSINDEXES IX , SYSIBM.SYSINDEXPART IPWHERE XT.TBNAME = 'MYTABLE' -- base table name AND XT.TBOWNER = CURRENT SQLID -- table creator AND IX.TBNAME = XT.XMLTBNAME AND IX.TBCREATOR = XT.XMLTBOWNER AND IX.NAME = IP.IXNAME AND IX.TBCREATOR = IP.IXCREATOR;-- NODEID INDEX ONLYTBNAME IXNAME IXTYPE DBNAME INDEXSPACE STORNAMEXMYTABLE I_NODEIDXMYTABLE N DSN01018 IRNODEID SYSDEFLT 1 record(s) selected
-- ALL THE TABLES IN A DATABASE:SELECT SUBSTR(CREATOR, 1, 8) as CREATOR , SUBSTR(NAME,1,20) as NAME , T.TYPE , T.DBNAME , T.TSNAME , T.COLCOUNTFROM SYSIBM.SYSTABLES TWHERE DBNAME = 'DSN01018';
CREATOR NAME TYPE DBNAME TSNAME COLCOUNTDDS0075 MYTABLE T DSN01018 MYTABLE 2DDS0075 XMYTABLE P DSN01018 XMYT0000 3 2 record(s) selected
-- ALL THE INDEXES IN A DATABASE:SELECT SUBSTR(IX.TBNAME,1,20) as TBNAME , SUBSTR(IX.NAME,1,26) as IXNAME , IX.IX_EXTENSION_TYPE as IXTYPE , IX.DBNAME , IX.INDEXSPACE , IP.STORNAMEFROM SYSIBM.SYSINDEXES IX , SYSIBM.SYSINDEXPART IPWHERE IX.DBNAME = 'DSN01018' AND IX.NAME = IP.IXNAME AND IX.TBCREATOR = IP.IXCREATOR;
TBNAME IXNAME IXTYPE DBNAME INDEXSPACE STORNAMEMYTABLE I_DOCIDMYTABLE DSN01018 IRDOCIDM SYSDEFLTXMYTABLE I_NODEIDXMYTABLE N DSN01018 IRNODEID SYSDEFLTMYTABLE MYINDEX V DSN01018 MYINDEX SYSDEFLT 3 record(s) selected
Please note that, as you may well know, for utilities, there is a better way to define the relevant objects using LISTDEF. You can use REPORT TABLESPACESET to list relevant objects too. The above queries can help you to find specific information. If you need to find the storage space usage, you can run the STOSPACE utility on STOGROUPs, and then add the space used by XML objects. Or better yet, if you group tables with database as units, you can calculate the space based on DBNAME to include XML object space automatically. For example, the following query displays the number of table spaces and also the corresponding total storage space, including XML table spaces, for each of the databases:
SELECT DBNAME, COUNT(*), SUM(SPACE) -- use SPACEF if necessaryFROM SYSIBM.SYSTABLESPACEGROUP BY DBNAME;
If you just want to find XML table space information, use this:
SELECT DBNAME, COUNT(*), SUM(SPACE)FROM SYSIBM.SYSTABLESPACEWHERE TYPE = 'P'GROUP BY DBNAME;
To help you understand how implicitly created XML objects impact your naming convention for physical database design and administration, the following table lists some of the attributes related to XML objects under different CREATE TABLE options (probably not exhaustive). The short notations used in the table are the following:
NM: Name, TS: Table Space, ST: Stogroup, BP: Buffer pool, TB: Table, IS: Index Space, G: Generated. Deflt: Default from subsystem parameters.
From this table, we can see that to control the storage allocation with the benefit of database grouping, better use the first two forms of the CREATE TABLE statements, which is probably your normal cases. To avoid concurrency issues related to DBD (Database Descriptor), a database should not include too many objects too. Also note that the XML indexes are associated with the base table in the catalog, even though they are associated with the XML table internally (reflected in OBD - Object Descriptor).
DB2-created XML objects is somewhat a departure from the previous practices. The main reason is for convenience of ISV packaged applications, and a direction for DB2 DDL to focus more on tables, less on table spaces and stogroups. I hope that this new convention does not introduce too much inconvenience for you, once you understand the relationships. Share your experiences with us.
In one of my previous postings, I mentioned that there is no special setup needed to get started with XML in DB2 9 for z/OS. However, if you or your folks are getting more serious, some installation and serviceability parameters (zparms) related to XML may need to be adjusted. Here I will talk about four zparms related to the virtual storage limit: XMLVALA, XMLVALS, LOBVALA, and LOBVALS. I will also explain why you could insert and select XML data larger than the virtual storage limit.
The XMLVALA and XMLVALS are the virtual storage limit for XML processing for each thread and entire sub-system, respectively. The main goal is to prevent run-away processes from bringing down your DB2 system. The default for XMLVALA and XMLVALS is 200MB and 10GB, respectively. Here is the initial guideline for setting to different values.
For XMLVALA, use at least 2x the maximum XML document size if there is no XPath (such as in V8), and use at least 4x the maximum XML document size (either generated or stored) if there are any XPath queries, up to 2GB. The maximum limit is 2GB.
For XMLVALS, use the maximum possible that will not cause the DB2 system down when this amount is really used up by XML processing, or the maximum number of threads x 2GB (or XMLVALA), whichever is smaller, with the maximum limit 50GB.
LOBVALA and LOBVALS are relevant for bind-in and bind-out of XML data when XML data needs to be materialized as a Large Object (LOB) in memory. In general, set LOBVALA to the maximum XML document size inserted or selected, and set LOBVALS to the maximum number of threads x LOBVALA.
For optimization, DB2 uses streaming techniques whenever possible to reduce the storage consumption so that only a small amount of storage is used to handle very large documents. For background, the following table lists all the cases for materialization of XML data as in-memory LOBs for INSERT, UPDATE, and SELECT. The LOAD utility is the same as local INSERT, while UNLOAD is the same as local SELECT without FETCH CONTINUE, i.e. no materialization. Thank Li-mey Lee of our team for providing the details.
XML Data Materialization During Bind-in and Bind-out in DB2 9
|Local, CLI |
and JDBC T2
- FRV (File References): YES
- Others: NO
- FETCH CONTINUE: YES
- Others: NO
- XML only, length known: NO
- XML only, at least one length=-1: all YES
- Mix of XML & LOB: always YES
|Always YES||Always YES|
From the above table, you can see that DB2 does not materialize XML data for a local application during simple INSERT or SELECT without using FETCH CONTINUE, so you can handle XML documents exceeding the zparm (XMLVALA or LOBVALA or both) limit. For a JDBC T4 application, you may be able to insert a large document exceeding the zparm limit, but you won't be able to select it back without increasing the limit.
Share with me your experience on setting the "correct" values for these zparms.
The XMLTABLE() function can be said to be the most powerful and versatile function in SQL/XML. In one of the previous postings, I listed some highlights on using the XMLTABLE function. Here I give some simple examples, especially cascading XMLTABLE functions in an SQL query.
For illustration, assume that we have the following table and have inserted one document below, which is very typical in the real world with header and body in a form.
CREATE TABLE MYTABLE (XDOC XML);
INSERT INTO MYTABLE VALUES('<form> <formheader id="1234"> <head1>head1</head1> <head2>head2</head2> </formheader> <formdata> <data1 did="d1234"> <data11>data11</data11> <data12>data12</data12> </data1> <data2 did="d2345"> <data21>data21</data21> <data22>data22</data22> </data2> <data3 did="d3456"> <data31>data31</data31> <data32>data32</data32> <data33>data33</data33> </data3> </formdata></form>');
Assume that we want to present the data in this document as a table, with data of level 1 under "formdata" as the rows, combined with some data from the header. The key to the XMLTABLE function is that the row-expression identifies nodes for the rows. The data from the header will be repeated for each row. The first XMLTABLE query using direct navigational paths looks like the following:
SELECT TX.*FROM MYTABLE, XMLTABLE('/form/formdata/*' PASSING XDOC COLUMNS ID VARCHAR(5) PATH '../../formheader/@id', HEAD1 VARCHAR(5) PATH '../../formheader/head1', NAME VARCHAR(8) PATH 'fn:local-name()', DATA XML PATH '.') TX;
Here is the result:
ID HEAD1 NAME DATA1234 head1 data1 <data1 did="d1234"><data11>data11</data11><data12>data12</data12></data1>1234 head1 data2 <data2 did="d2345"><data21>data21</data21><data22>data22</data22></data2>1234 head1 data3 <data3 did="d3456"><data31>data31</data31><data32>data32</data32><data33>data33</data33></data3>
To avoid repeated evaluations of column expressions for the header data, the following XMLTABLE query avoids the parent axis using two cascading XMLTABLE functions:
SELECT TX1.ID, TX1.HEAD1, TX.*FROM MYTABLE, XMLTABLE('/form' PASSING XDOC COLUMNS ID VARCHAR(5) PATH 'formheader/@id', HEAD1 VARCHAR(5) PATH 'formheader/head1', FORMDATA XML PATH 'formdata') TX1, XMLTABLE('$fd/*' PASSING TX1.FORMDATA as "fd" COLUMNS NAME VARCHAR(8) PATH 'fn:local-name()', Data XML PATH '.') TX;
It results in the same result as above.
Now let's assume we want to lower the level for the rows to the level 2 data under "formdata". We only need to get to the next level for the row expression by appending "/*" to the row path expression. Note that since each element at the data level has a unique name, the queries here illustrate the legitimate use of "*" - the wild card name test.
SELECT TX1.ID, TX1.HEAD1, TX.*FROM MYTABLE, XMLTABLE('/form' PASSING XDOC COLUMNS ID VARCHAR(5) PATH 'formheader/@id', HEAD1 VARCHAR(5) PATH 'formheader/head1', FORMDATA XML PATH 'formdata') TX1, XMLTABLE('$fd/*/*' PASSING TX1.FORMDATA as "fd" COLUMNS NAME VARCHAR(8) PATH 'fn:local-name()', Data XML PATH '.') TX;
The result looks like the following:
ID HEAD1 NAME DATA1234 head1 data11 <data11>data11</data11>1234 head1 data12 <data12>data12</data12>1234 head1 data21 <data21>data21</data21>1234 head1 data22 <data22>data22</data22>1234 head1 data31 <data31>data31</data31>1234 head1 data32 <data32>data32</data32>1234 head1 data33 <data33>data33</data33>
What if we want to get "@did" from the level 1 data as a column of the rows? We can certainly add "../@did" as a column expression. But we can avoid the parent axis by introducing another XMLTABLE function. Can you try to write the query yourself?
-Guogen (Gene) Zhang (GGZ)[Read More
XML Schema is a W3C recommendation to specify "schema"s for XML data, used to put constraints on otherwise extremely flexible XML data. The constraints include basic data types, structures, occurrences, uniqueness, and referential integrity, etc. DB2 9 for z/OS pureXML provides XML schema validation for XML data through a user-defined function (UDF) called DSN_XMLVALIDATE. The following provides some guidelines in using XML schema validation.
- An XML schema is not required for an XML column. An XML column can store any well-formed XML documents, and it contains untyped XML data, even if a document is validated using the DSN_XMLVALIDATE function. In other words, type annotations are not kept after schema validation.
- XML Schema validation can be performed in an application using any schema validation tools, or in DB2 by invoking DSN_XMLVALIDATE. To invoke schema validation in DB2, an XML schema has to be registered in DB2 XML Schema Repository (XSR). XSR consists of a set of DB2 tables storing XML schema info, and a set of stored procedures to manage the schemas. A special JDBC interface is also provided for schema registration.
- Setting up XSR requires Java V5, JDBC and Java stored procedure setup, which requires WLM setup. See the "XSR setup and troubleshooting" article in the DB2 for z/OS pureXML wiki page for references to the setup information and troubleshooting details.
- XSR tables and stored procedures are created during the installation or migration. Please customize XSR related settings by changing the installation and migration jobs DSNTIJSG and DSNTIJNX.
- XML Schema documents (XSDs) have to be provided physically to DB2 for schema registration. During registration, an SQL ID in the form of "SYSXSR.<name>" can be provided to reference the schema. The schema can also be referenced by the target namespace with optional schema location. Schema documents are compiled into a binary format for runtime use. The original schema documents and the binary representations generated at XSR_COMPLETE are stored in XSR table SYSIBM.XSROBJECTS and other XSR tables. Note that string IDs are used for XML target namespaces and schema locations in the tables.
- To validate a document against a schema, invoke DSN_XMLVALIDATE in one of the two forms: two-argument form and three-argument form. The two-argument form takes an instance document and the schema name (SQL ID), while the three-argument form takes an instance document and schema target namespace, with optional schema location. As of this writing, DSN_XMLVALIDATE function has to be wrapped in the XMLPARSE function, and has a 50MB size limit on a document. For example, the following INSERT statement inserts an XML document after validation against a schema named SYSXSR.PURCHASEORDER:
INSERT INTO MYTABLE VALUES(10, XMLPARSE(DOCUMENT DSN_XMLVALIDATE(xmllob, 'SYSXSR.PURCHASEORDER') ) );Validation may cause XML data to change if there are default values and normalization specified in the schema that are applicable to the instance document.
- DB2 invokes XLXP-C high-performance validating parser. However, validation is still 2 to 3 times as expensive as simple parsing in terms of CPU time. Avoid validation in DB2 to save CPU and improve performance if performance is a concern.
- To support XML schema versioning, either use a totally different target namespace for a new schema version (usually for major changes), or use the same target namespace with a different schema location (usually for minor changes). You have to register schema versions with different SQL names in the XSR. Usually target namespace and schema location are also used in instance documents to identify the schema version. DB2 does not have the capability to use the target namespace and schema location to identify a registered schema for validation yet.
- Use Command Line Processor (CLP) or IBM Data Studio to register and manage XML schemas. Either way invokes the underlying XSR stored procedures, and is more convenient than using the native XSR stored procedures. See XML QuickDemo for a simple example of using CLP to register a schema.
Contact us if you have any questions related to XML schema validation.
-Guogen (Gene) Zhang (GGZ)[Read More]
One of the key differences of XML from the Large Object (LOB) types in DB2 is that XML data can be indexed. The XML indexes supported in DB2 9 are also called XML value indexes, and queries on XML can use these indexes for performance.
An XML index example
An XML index is used to provide a mapping from a node value to its location. An XPath expression is required to specify the nodes within a document to be indexed. Two data types are supported for XML indexes in DB2 9 for z/OS: DECFLOAT for numeric values and VARCHAR(n) for string values. For example, the following CREATE INDEX DDL creates an XML index on TransRefGUID of the REQUESTXML column of ACORD data, as VARCHAR(24):
CREATE INDEX ACORD.ACORDINDEX1 ON ACORD.REQUEST(REQUESTXML)GENERATE KEYS USING XMLPATTERN'declare default element namespace "http://ACORD.org/Standards/Life/2"; /TXLife/TXLifeRequest/TransRefGUID' as SQL VARCHAR(24)
DB2 will take the value of /TXLife/TXLifeRequest/TransRefGUID as the key, and map to its logical location (DOCID, NODEID), and physical location (RID), using the existing B+-tree index infrastructure.This index can be used for queries that search on TransRefGUID. For example, the following XMLEXISTS predicate can potentially use this index:
XMLExists('declare default element namespace "http://ACORD.org/Standards/Life/2"; /TXLife/TXLifeRequest[TransRefGUID="2004-1217-141016-000012"]'PASSING REQUEST.REQUESTXML)
From queries, it is easy to figure out what kind of XML indexes can speed up the queries - by concatenating path steps from within a predicate to the steps outside the predicate, and using data types consistent between the queries and indexes.
Before explaining the index access procedure, I'd like to review the basic XML storage scheme so you can understand it better.
Basic XML storage scheme in DB2 9 for z/OS
The following picture depicts the high-level storage scheme for XML data.
At high-level, the XML data is stored in a separate table space, just like LOB data. The real XML data is stored in the XMLDATA column of the internal XML table. It contains the hierarchical data in records that can fit in 16KB pages. In order to support free movement of data records, logical links using NODEIDs are used. That's why we need a NODEID index to link records for a document. Similarly, since utilities, such as REORG, can be applied to the base table space and the XML table space independently, XML indexes do not contain base table RIDs, but XML table RIDs. In order to get to base table rows from XML indexes, we need the DOCID index on the base table (see below). That's why DB2 always creates a DOCID index on the base table and a NODEID index on the XML table as part of the storage scheme, although they are for totally different purposes. By XML indexes, we refer to XML value indexes created by users.
Basic XML index access plans
If you use EXPLAIN for a query and select some key columns from the PLAN_TABLE, you will see some new access type in the ACCESSTYPE column for SQL/XML queries. They are the following:
- DX: DOCID list access. DB2 searches an XML index and retrieves all the qualified DOCIDs. Then the DOCIDs are sorted and duplicates are removed. The DOCID list is further converted to a RID list of the base table through the DOCID index. Then the base table rows are fetched.
- DI: DOCID ANDing. DB2 intersects two DOCID lists from two XML indexes for conjunction of two XPath predicates.
- DU: DOCID ORing. DB2 unions two DOCID lists from two XML indexes for disjunction of two XPath predicates.
- M: Multi-index access.
If you see "R" (R-Scan) in the ACCESSTYPE for a table with an XMLEXISTS predicate, then DOCSCAN is applied for the XML column. No new type was introduced for the scan.
Here is an example for an index ANDing plan:
+---------------------------------------------------------------------+ | PLANNO | ACCESSTYPE | MATCHCOLS | ACCESSNAME | MIXOPSEQ | +---------------------------------------------------------------------+1_| 1 | M | 0 | | 0 |2_| 1 | DX | 1 | ACORDINDEX2 | 1 |3_| 1 | DX | 1 | ACORDINDEX1 | 2 |4_| 1 | DI | 0 | | 3 | +---------------------------------------------------------------------+
The following diagram illustrate the process of using the index ANDing plan. Step 4 is where a DOCID index is always used.
These are the basic XML index access plans. For example, NODEID and RID from XML indexes are not used for queries today. We are enhancing plans for better query performance, so expect more methods in the future.
Somethings specific to XML indexes
The same principles for relational indexes apply to XML indexes, such as create indexes only needed by queries and use REBUILD INDEX. The following are some unique features of XML indexes:
- Depending on the XPath in the XMLPATTERN of the CREATE INDEX statement and XML documents, DB2 may generate zero, one or more key entries for a document in an XML index.
- For a numeric index (DECFLOAT type), if a node identified by the XMLPATTERN contains a value that cannot be cast to a number, it is ignored. But insert will be successful. For example, no index entry is generated for XL if the index is DECFLOAT type.
- However, for a string index (VARCHAR(n) type), if a node identified by the XMLPATTERN contains a value that is longer than n, then the document will be rejected, or CREATE INDEX will fail. The maximum n is 1000.
- You can create as many indexes as you like on a single XML column. You could even create multiple indexes with different types on the same XMLPATTERN. This provides much flexibility for XML.
- Use fully specified XPath whenever possible to reduce the cost of maintaining the indexes.
- Index leaf nodes or nodes close to leaves. For non-leaf nodes, the key values are concatenated values from descendants, and their usage is a bit weird. For example, index on name for <name><firstname>John<firstname><lastname>Doe<lastname><name> will result in "JohnDoe" as the key, and you have to use [name="JohnDoe"] in queries to qualify for the index-based search. In addition, DB2 9 does not support a node across record boundaries.
To produce consistent query results with or without XML indexes, DB2 tries to tolerate cast errors during XMLEXISTS predicate numeric comparison. For example, if a node size contains "XL", comparison [size > 10] will tolerate the "XL" value, which is equivalent to evaluate to false.
DECFLOAT is used instead of DOUBLE for the numeric index type due to its precision. Date and time are not yet supported, but you can use string indexes if you use ISO format (which is required in XML) without timezone or always use the same timezone for the data, and use string comparison in the queries to search the documents.
If you never search inside XML documents, but get XML data in and out as a whole, you probably don't need to use the XML type, since VARCHAR or VARBINARY or LOB types can serve the purpose.
To summarize, XML indexes use XPath to identify nodes to be indexed, and can be used for queries with XMLEXISTS and XMLTABLE predicates.
-Guogen (Gene) Zhang (GGZ)[Read More
In this first posting of the new year, I'm going to share something non-technical. We are looking forward to the uptake of the pureXML projects in many of our clients and ISVs. As for any new technology, pureXML adoption takes leadership and courage. The reward will be ample, especially in this unprecedented, tough global economy. With pureXML, you could support new products and services in reduced time to market, achieve more with the same resources, and improve the bottom-line of the businesses.
Having read the book "Primal Leadership - learning to lead with emotional intelligence" by Daniel Goleman, et al. before, I recently came across a series of three Harvard Business Review (HBR) articles that are pretty much short versions of the book. "Leadership that gets results" focuses on six leadership styles.
The resonant styles are:
- the visionary/authoritative styles,
- the coaching style,
- the affiliative style, and
- the democratic style.
And the dissonant styles are:
- the pacesetting style and
- the command/coercive style.
"The best leaders don’t know just one style of leadership — they’re skilled at several, and have the flexibility to switch between styles as the circumstances dictate."
"What makes a leader" focuses on the emotional intelligence that is necessary to be effective leaders. The four emotional intelligence domains/skills are:
- social awareness, and
- relationship management(empathy and motivation).
"Primal leadership - the hidden driver of great performance" focuses on a process of five steps to achieve greater emotional intelligence:
- What do you want to be?
- Who are you now?
- How do you get from here to there?
- How do you make change stick?
- Who can help you (in the above process)?
If you are not familiar with this research result, I highly recommend you search on the web to find some summary in more detail, or get the book or articles.
Another book that I read over ten years ago is also one of the best-sellers many know well: "The seven habits of highly effective people" by Stephen Covey. The seven habits are:
- be proactive;
- begin with the end in mind;
- put first things first;
- think win/win;
- seek first to understand, then to be understood;
- sharpen the saw.
"The key to success in business is action. But in most companies, people are rewarded for talking - and the longer, louder, and more confusingly, the better." (from HBR article "The smart-talk trap") Talking is probably better than deceiving and bad politics. And trustworthiness and integrity are essential in a leader's character. (I don't know why I'm writing these sentences.)
I wish you fulfill your new year resolution! And make pureXML one of your technology priorities this year.
- Guogen (Gene) Zhang (GGZ)[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.)