DB2 10.5 for Linux, UNIX, and Windows

Tools that support XML

Both IBM® and third-party tools provide support for working with the pureXML® feature. Shipped with DB2® database server, or available separately for download, the following tools are available to you from IBM:

IBM Data Studio

Support for XML includes the following:

Stored procedures: You can create and run stored procedures that contain XML data types as input or output parameters.
Data output: You can view documents contained in XML columns as a tree or text.
SQL editor: You can create SQL statements and XQuery expressions that work with both relational and XML data.
XML schemas: You can manage schema documents in the XML schema repository (XSR), including registering and dropping schemas, as well as editing schema documents.
XML document validation: You can perform validation of XML documents against schemas registered in the XSR.
SQL user-defined functions: You can create and run SQL user-defined functions that use XML parameters.

Command line processor

Several DB2 commands support the native storage of XML data. You can work with XML data alongside relational data from the DB2 command line processor (CLP). Examples of tasks that you can perform from the CLP include:

Issuing XQuery statements by prefixing them with the XQUERY keyword.
Importing and exporting XML data.
Collecting statistics on XML columns.
Calling stored procedures with IN, OUT, or INOUT parameters of XML data type.
Working with the XML schemas, DTDs, and external entities required to process XML documents.
Reorganizing indexes over XML data and tables containing XML columns.
Decomposing XML documents.

IBM Database Add-Ins for Microsoft Visual Studio

You can use the IBM Database Add-Ins for Microsoft Visual Studio to create tables with XML columns and indexes over XML data. You create an XML column as you would any other column in this tool. Simply specify the data type as XML. You can create an index by using the XML Index Designer in this tool. You do not have to manually specify the XML pattern expression as required by the CREATE INDEX syntax for an index over XML data. Instead, you can graphically select the XML nodes that you want to index from either a tree representation of a registered XML schema, a document from the XML column, or an XML schema in a local file. The tool generates the XML pattern expression for you. Alternatively, you can manually specify the XML pattern expression. After you have specified all of the other index attributes, the tool generates the index for you.

EXPLAIN

You can issue the EXPLAIN statement on XQuery statements and SQL/XML statements to quickly see the access plan for these statements, including whether DB2 database server uses indexes. To issue the EXPLAIN statement for an XQuery statement, use the XQuery keyword followed by an XQuery statement that is enclosed in single or double quotation marks, as in the following example:

EXPLAIN PLAN SELECTION FOR XQUERY 'for $c in 
db2-fn:xmlcolumn("XISCANTABLE.XMLCOL" )/a[@x="1"]/b[@y="2"] return $c'

DB2 captures the access plan information in the EXPLAIN tables. The expected sequence size for any XML columns are stored in the SEQUENCE_SIZES column of the EXPLAIN_STREAM table. You might also notice data in the EXPLAIN_PREDICATE table for several predicates that you do not recognize. These predicates are generated by the DB2 database server during the EXPLAIN operation to evaluate XPath expressions that are used in an index scan. You do not need to evaluate this predicate information. These predicates not part of the optimizer plan and thus have a value of -1 in the PREDICATE_ID and FILTER_FACTOR columns.

Alternatively, you can avoid manually interpreting the EXPLAIN tables by using IBM Data Studio to view graphical depictions of these access plans. For more details, see Diagramming access plans with Visual Explain.

The following nodes are displayed in the graphs to show XML operations:

IXAND: Indicates that the DB2 database server applied the AND predicate to the results of multiple index scans.
XISCAN: Indicates that the DB2 database server used an index over XML data to access the data.
XSCAN: Indicates that the DB2 database server evaluated XPath expressions and extracted XML document fragments from XML documents.
XANDOR: Indicates that the DB2 database server applied the AND and OR predicates to the results of multiple index scans.
XTQ: Indicates that the DB2 database server used a special table queue (TQ) to route each item in a global XML sequence to its original database partition, retrieve XML data from XML documents based on the evaluation of the item, and aggregate XML data into an output sequence.