15 pureXML Performance Best practices in DB2 9

DB2 9 introduces pureXML support, which means that XML data will be stored and queried in its intrinsic hierarchical format. To query XML data, DB2 provides two languages, Sql/xml, and XQuery. In addition, DB2 9 also has mature XML indexing capabilities and support for XML Schema validation. While most of the existing guidelines on DB2 performance apply to XML data, this article will also provide some additional XML-specific performance tips.

Brief introduction

The pureXML support in DB2 9 provides an effective, multifaceted capability for managing XML data. For many XML applications, performance is an area of high priority. Both DBAs and application designers can ensure good performance through their own work. First, we have all the traditional performance guidelines on DB2, including cpu/memory/disk configuration balance, table space and buffer pool tuning, locks, logging, query execution plans, and more. All of these topics have been discussed in previous DB2 articles (see Resources) and still apply when managing XML data in DB2.

Fortunately, many of these problems are handled by autonomous functions such as DB2 and self tuning memory management. They provide a high level of performance for many applications and require little manual intervention. However, XML applications with higher performance requirements can also benefit from other performance considerations. This article focuses on this situation and gives tips and guidelines for optimal performance for XML-related applications in DB2 9.

This article discusses and elaborates 15 XML performance prompts (sorted in sequence). These 15 tips cover a wide range of areas, but experience shows that applications with performance problems typically need only one or two hints to achieve the desired performance.

Tip 1: Rationally Select the granularity of the XML document.

Tip 2: To achieve better XML performance, use DMS and larger pages.

Tip 3: How to put the XML data into a separate tablespace if necessary.

Tip 4: How to configure DB2 to quickly insert XML data into chunks.

Tip 5: Check XML performance with the new snapshot monitor element.

Tip 6: Understand the cost of XML schema validation.

Tip 7: In an XPath expression, use a fully qualified path whenever possible.

Tip 8: Define a skewed XML index and avoid indexing anything.

Tip 9: Put the document filter verb into the xmlexists instead of putting it into the xmlquery.

Tip 10: Use square brackets [] to avoid Boolean predicates in Xmlexists.

Tip 11: Use Runstats to collect statistical information for XML data and indexes.

Tip 12: How to expose relational data to XML using the Sql/xml publishing view.

Tip 13: How to use the XMLTable view to expose XML data in relational format.

Tip 14: Use a sql/xml statement with a parameter placeholder for a short query or OLTP application.

Tip 15: Avoid code page conversions that occur during XML inserts and retrievals.

In the discussion of these performance hints, we assume that you are familiar with basic DB2 management and performance practices and are familiar with basic db2s PureXML support. For example, you should know XML columns, XML indexes, and how to query XML data with Sql/xml and XQuery.

DB2 XML Performance Tips

Tip 1: Rationally Select the granularity of the XML document

When designing XML applications and XML document structures, you may be faced with the choice of which business data is placed in a separate XML document. For example, in the following department table, we use an XML document (medium granularity) for each department. This is a reasonable choice if the department is the primary granularity on which the application accesses and processes data. Alternatively, we can decide to combine multiple departments into an XML document, for example, the department that belongs to the same unit into an XML document (coarse-grained). However, if you typically work with only one department at a time, the performance of this granularity is suboptimal.

Table 1. CREATE TABLE Dept (Unitid Char (8), Deptdoc XML)

Unitid	Deptdoc
Wwpr	<dept deptID='PR27'> 　 <employee id='901'> 　　　<name>Jim Qu</name> 　　　<phone>408 555 1212</phone> 　 </employee> 　 <employee id='902'> 　　　<name>Peter Pan</name> 　　　<office>216</office> 　 </employee> </dept>
Wwpr	<dept deptID='V15'> 　 <employee id='673'> 　　　<name>Matt Foreman</name> 　　　<phone>416 891 7301</phone> 　　　<office>216</office> 　 </employee> 　 <description>This dept supports sales world wide</description> </dept>
S-use	...
...	...