Overview of new DB2 Version 9.5 pureXML enhancements

Document options Document options requiring JavaScript are not displayed Discuss Rate this page Help us improve this content

Level: Intermediate

Manoj Sardana (msardana@in.ibm.com), System software engineer, IBM

01 Nov 2007

This article describes IBM ® DB2® version V9.5 pureXML™ enhancements and new features for Linux, Unix and Windows. DB2 Version 9 supports XML as a native data type and has the support for SQL/XML and XQuery language along with other features like schema support, publishing functions, XML support for utilities, decomposition and the like. DB2 V9.5 enhances some of these features and introduces new features to make XML handling more effective. This article explains these enhancements and new features in the context of how it will make XML handling more efficient and easier to use.

DB2 9 XML support overview

DB2 9 introduced the native XML data type. It stores XML in a parsed hierarchical (native) format and allows a user to query the data using XQuery and SQL/XML languages. DB2 XQuery expressions use XML documents stored in the DB2 database as the source of XML for querying. The functions xmlcolumn and sqlquery are used to concatenate the XML values stored in the database and provide XML sequences to XQuery parser.

Apart from the XQuery language, DB2 9 provides SQL/XML functions to work with both XML and relational data at the same time in a single query. The SQL/XML functions xmlquery, xmltable and xmlexists help embedding XQuery in SQL statements.

DB2 9 also supports schema validation. New commands and stored procedures were introduced for registering the schemas to the database to make them function as database objects. These registered schemas can be used to validate the XML values prior to or after an insert operation using the xmlvalidate function. These schemas can also be annotated for decomposing the XML data into relational tables. Publishing functions like xmlelement, xmlattributes and the like can be used to transform relational values into an XML document. The utilities (import, export, etc.) were also updated for XML data support. For more details on XML support in Version 9 refer to the Resources section.

DB2 V9.5 - What's new

The existing DB2 9 functionality is quite powerful in handling and working with XML data. DB2 V9.5 enhances some of the existing features and introduces additional functionality to make XML handling more powerful and efficient. Below is a list of the functionality addressed in this article:

• Support for XML in Non-Unicode databases
• Sub-document update
• Base-table storage/compression
• Compatible XML schema evolution
• Validation triggers
• Validation check constraints
• XML replication
• XML federation
• XML load
• sqlquery() parameters
• User-friendly publishing functions
• Default parameter passing for SQL/XML functions
• XSLT function
• XML decomposition enhancements
• XML index enhancements
• Index advisor and optimizer enhancements
• DB2 Data Web services

Most of the code samples in the following sections are based on the DB2 V9.5 sample database. You can create the sample database by running the command db2sampl from DB2 V9.5 command line processor. It can also be created from the first step. The first step is a tool provided by DB2 that gets executed after the installation of DB2 and can also be executed at a later stag by going to Start> All Programs > IBM DB2 > db2 copy name > Set up tools > first steps on Windows.

Back to top

Support for XML in non-Unicode database

DB2 9 allows users to create the database with XML data only in UTF-8 codepage. This means that, even if the values in the XML document are in ASCII format, it needs to be stored in a UTF-8 database. DB2 V9.5 removes this restriction and allows a user to create the database with an XML column in any code set. Because this restriction is removed, even if the database is not created in UTF-8, a user can alter a table to add an XML column or create a new table with XML columns.

The following code creates a sample database and a sample table with an XML column:

                
db2 CREATE DATABASE sampledb
db2 CONNECT TO sampledb
db2 CREATE TABLE record(id INT, record XML)
      

Back to top

Sub-document update

DB2 V9.5 allows a user to update a portion of an XML document stored in the database. It introduces the XQuery transform expression which uses four updating expressions -- insert, delete, replace and rename to modify the XML document fragments. A transform expression is a part of the XQuery language and hence can be used in an XQuery expression. With DB2 V9.5 an updating expression can be used only within a transform expression. A transform expression has the following clauses:

• COPY: The copy clause of the transform expression binds the source XML value to a variable. The updating expression works on this copy further in the query.
• MODIFY: A modify clause modifies the copied XML as per the updating expressions. Multiple updating expressions can be used in the MODIFY clause.
• RETURN: The return clause returns the modified values.

The four updating expressions are explained below :

1. The insert expression is used to insert a new XML node into an existing XML document. You can specify the position of the insertion within the XML document.
2. The replace expression is used to update a particular value of a particular node.
3. The delete expression is used to delete a particular node from the XML document.
4. The rename expression is used to rename a node.

Because the transform expression is a part of the XQuery language, it can be used within an SQL statement using the xmlquery function and can also be used in an update statement to update an XML value.

The code in Listing 2 updates the info column of the customer table in the sample database. It updates the XML document to match the CID attribute with the value of a cid relation column.

                
UPDATE CUSTOMER
SET info = 
      XMLQUERY('transform 
                      copy  $po := $INFO
                      modify  
                           do replace value of $po/customerinfo/@Cid  with $CID
                return  $po' 
                passing info as "INFO", cid as "CID") 
       WHERE cid=1000

      

If an XML validation check constraint exists on the XML column of the table, the user may need to validate the new XML value before the update either manually or by creating a trigger.

The following code example removes an item from the purchaseorder table and gives the modified document as a result of the query.

                
xquery 
transform 
copy 
   $po := db2-fn:sqlquery(‘select porder from purchaseorder where 
                           custid = 1002 and orderdate=“2006-02-18”’)
modify  
   do  delete $po/ PurchaseOrder/item[partid = “100-201-01”]
return  $po

      

The sample xupdate.db2 gives various examples of the transform expression. You can find this sample under the sqllib/samples/xml/xquery/clp directory.

Back to top

Base-table row storage/compression

In DB2 9, XML data is stored in the different storage location than the relational data. This storage location is called XML data area (XDA). DB2 9 stores all the XML documents to this storage location which means accessing XML values along with relational data need more I/O. If the size of the XML documents is not big and the page size has enough space after storing the relation values that it can accommodate the XML value too, storing the XML in the same page provides good performance benefit. Some of benefits are:

• Compression : As the XML data is stored along with the relational data, it can be compressed using compression technology introduced in DB2 9. As the relative size of the XML value is bigger than that of a relational data, a good amount of compression can be achieved.
• Query performance : Inlining of XML data makes base table size larger than usual as now the XML data is stored at the same location as relational. This gives a benefit while querying the data if XML data is accessed as often as other relational values in the table.

                
db2 CREATE TABLE emp1(id INT, info XML INLINE LENGTH 1024)

Use the INLINE option to specify that XML data is stored along with the relational data. It is advantageous for the queries that fetch XML data as all the data will be found in the same place. On the other hand, for the queries that access non-XML data, this may result in more I/O to find the relational data.

The ideal situation for base table row storage of XML data is when the table has only one column of XML type and the maximum size of the XML document doesn't exceed the page size.

Back to top

Compatible XML schema evolution

To increase the flexibility and provide better schema evolution, DB2 V9.5 introduces the update feature for an XML schema. A previously registered schema can be updated with the new schema if they are compatible with each other. Two schemas are compatible if the XML documents validated with the older schema are still valid with the new schema.

For example, an addition of a new optional element in the new schema is compatible with the old one as the XML documents validated with the old one remain valid because of the optional nature of the new element. New XML documents can have this optional element and can be validated using the new schema. There is no need to do anything once the schema is updated as the old documents are still valid. Schema update fails if the schemas are not compatible. The annotation and the identifiers for the old schema are retained too.

To update a schema, the XSR_UPDATE stored procedure is introduced. The stored procedure checks for compatibility and updates the schema only if the new schema is compatible. To update a schema, a user needs to register both the schemas independently and then call the XSR_UPDATE stored procedure. A user can choose to retain the old schema or drop it once the old one is updated.

Lets take an example of info column in customer table. The info column has the addr element with the following definition. (You can find the complete schema under the sqllib/samples/db2sampl directory).

                
<xs:element xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                          name="addr" minOccurs="1" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="street" type="xs:string" minOccurs="1" />
              <xs:element name="city" type="xs:string" minOccurs="1" />
              <xs:element name="prov-state" type="xs:string" minOccurs="1" />
              <xs:element name="pcode-zip" type="xs:string" minOccurs="1" />
            </xs:sequence>
            <xs:attribute name="country" type="xs:string" />
          </xs:complexType>
        </xs:element>
              

In the future, a user wants to have a HouseNo element which is an optional element. To update the already registered schema so that the identifiers remain the same, you need to first register the new schema with an additional element. The definition of the new addr element is:

                
<xs:element xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                          name="addr" minOccurs="1" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="HouseNo" type="xs:string" minOccurs="0" />
              <xs:element name="street" type="xs:string" minOccurs="1" />
              <xs:element name="city" type="xs:string" minOccurs="1" />
              <xs:element name="prov-state" type="xs:string" minOccurs="1" />
              <xs:element name="pcode-zip" type="xs:string" minOccurs="1" />
            </xs:sequence>
            <xs:attribute name="country" type="xs:string" />
          </xs:complexType>
        </xs:element>
              

After registration, the following stored procedure can be used to update the existing schema with the new one:

                
db2 call XSR_UPDATE('RSCHEMA','CUSTOMER','RSCHEMA','CUSTOMER1',0)
              

The last argument value, 0, indicates that the new schema should not be dropped after the update. If the value is set to any other non-zero value, the new schema is dropped after the update operation.

The sample code xsupdate.db2 demonstrates the compatible XML schema evolution. The sample can be found in the sqllib/samples/xml/clp directory.

Back to top

Validation trigger support

To increase the application flexibility and provide the user automatic validation of an incoming XML document, DB2 V9.5 extends the XML support in before triggers. Before triggers are triggers created using the BEFORE option and executed before the inster/update/delete operations. XML values can be referenced in new variable in before triggers. The action part of the trigger can apply the xmlvalidate function on the new values. The WHEN clause of the trigger can be used to check if the new value is validated or not validated according to any of the specified schemas. This can be done by using the IS VALIDATED or IS NOT VALIDATED ACCORDING TO XMLSCHEMA clause in the WHEN condition. Based on the output from the WHEN condition, you may want to validate the XML value or set a new value. At present, only the xmlvalidate function is allowed on the new transition variable of XML type. When created, this trigger is automatically activated and executed for every insert in the table and hence can be used to validate the XML values in case they are not validated in insert statement.

The following code is a DDL statement to create the customer table and a trigger defined on the table. The trigger is activated whenever there is an insert operation on the table. The trigger validates an XML document before inserting if the document is not validated in the insert statement using the xmlvalidate function. The example assumes that the table customer does not exists and the customer schema is already registered in the database.

                
CREATE TABLE Customer ( Cid        BIGINT NOT NULL,
                          info       XML,
                          History    XML,
                          CONSTRAINT PK_CUSTOMER PRIMARY KEY (Cid))
                       
CREATE TRIGGER Trigger_customer NO CASCADE BEFORE INSERT ON customer 
     REFERENCING NEW AS n 
     FOR EACH ROW MODE db2sql
     WHEN (n.info IS NOT VALIDATED ACCORDING TO XMLSCHEMA ID customer)
     BEGIN ATOMIC
       SET n.info = XMLVALIDATE(n.info ACCORDING TO XMLSCHEMA ID customer);
     END@   
      

The sample xmltrig.db2 gives different scenarios and the operations which can be performed inside the trigger for assigning new values and validation of XML values. The sample can be found in the sqllib/samples/xml/clp directory.

Back to top

XML validation check constraints

A check constraint is a type of constraint which can be put on a table column at the time of the creation of the table. Whenever the constraint is valid, DB2 allows the insert operation, otherwise the insert fails.

DB2 V9.5 supports check constraints for XML values. A user can enforce validation on an XML column using check constraints. Similar to the before trigger, the IS VALIDATED ACCORDING TO XMLSCHEMA clause can be used in the check constraints to enforce the validation. The only difference here is that this constraint only checks for the validation condition and does not actually do the validation. A user either needs to explicitly validate the XML value using xmlvalidate in the insert statement or use a before trigger to do the automatic validation. Insert succeeds only when the XML value is valid according to the schemas specified in the check constraint.

The before trigger and check constraint on a table XML value always ensures that the XML values are valid as per the specified schema. While a before trigger does the automatic validation whenever insert operation is issued, a check constraint forces the user to explicitly use xmlvalidate function. Both can be used together to enforce the integrity of the XML values.

The code in Listing 9 alters the table customer created in Listing 8 to put a check constraintson the table:

                
db2 ALTER TABLE customer ADD CONSTRAINT check_info 
            CHECK(info IS VALIDATED ACCORDING TO XMLSCHEMA ID customer)
      

The check constraint created above always checks that the document is validated according to the customer schema. In case the trigger does not exist, the user needs to explicitly validate the value using the xmlvalidate function.

The sample code xmlcheckconstraint.db2 demonstrates how users can create a view on the different tables with same structure with check constraints and implement partitioning of tables by schema.

Back to top

XML replication support

DB2 V9.5 supports XML data replication to another database that supports XML data. Replication can be done using WebSphere ®Replication Server version 9.5 or WebSphere Data Event Publisher version 9.5. The WebSphere Replication Server can be used to replicate to the federated targets that supports XML data types, or the XML can be mapped to CLOB/BLOB columns.

Replication for XML data is done within the transaction message just like any other relational column, so the maximum transaction message length puts a restriction on the size of XML being replicated. In case the size of the data is large, a placeholder document can be inserted in place of the original document. An exception can also be inserted into the exceptions table.

While doing the replication, it's impossible to replicate the XML schema registration. In addition, the XML data cannot be validated during replication.

Back to top

XML federation support

The WebSphere Federation Server Version 9.1 supports pureXML and hence can be used to integrate the local and remote XML store data. XML data from various databases can be viewed as local and DB2 XQuery and SQL/XML can be applied to query the data. A view can be created on the remote federated database to view the data as a serialized string and can again be parsed to XML value at the WebSphere Federation Server. Now DB2 can query this data using SQL/XML and XQuery languages by using the nicknames created for the view.

The XML data coming from the different federated sources can also be validated using db2 xmlvalidate function in the same manner it is used to validate the local XML values

For more details on how to use WebSphere Federation server for pureXML please see the Resources section.

Back to top

Load support

DB2 9 primarily supports two ways of populating a table with XML values. An insert statement to insert the XML values to the table and an import utility to import the bulk data into the table.

DB2 V9.5 extends this support for a load utility. Most of the options supported by import for XML data are supported for load. You can specify the path for XML data using the XML FROM clause. XML data can be validated during load using the XMLVALIDATE USING clause. The different options are XDS, SCHEMA and SCHEMALOCATION HINTS. While specifying the XDS option, DEFAULT, IGNORE and MAP clauses can be used. The meaning of these options are same as in import. The codepage of the data can be specified using file type modifiers XMLCHAR and XMLGRAPHIC. The XML data specifier (XDS) is used in the data file to specify the XML value. The behavior of the load restart remains the same. It rebuilds all the indexes by scanning all the XML documents.

The sample code xmlload.db2 demonstrates the load options available in DB2 V9.5 for XML data. You can find the sample in the sqllib/samples/xml/clp directory.

Back to top

XSLT support

DB2 V9.5 provides the functionality of processing XML document using XSL transformations within the database itself. The XML document stored in the database can be transformed to the HTML format by applying an XSLT stylesheet. To do this, DB2 V9.5 introduces the xsltransform function. It also supports stylesheets which use parameters. The xsltransform function can apply the XSLT stylesheet stored in a database table column as an XML document on an XML document. This gives the user the flexibility to retrieve a transformed XML document from the database and directly represent it on the web.

Now, suppose you have this XML document:

                
<?xml version="1.0"?>
   <products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <product pid="100-201-01">
       <description>
         <name>Ice Scraper, Windshield 4 inch</name>
         <details>Basic Ice Scraper 4 inches wide, foam handle</details>
         <price>3.99</price>
       </description>
     </product>
    </products>
			

along with this corresponding XSLT stylesheet:

                
<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:template match="products">
   <html>
   <head/>
     <body>
        <table border="1">
     <th>
        <tr>
            <td width="80">product ID</td>
            <td width="200">product name</td>
            <td width="200">price</td>
            <td width="50">details</td>
         </tr>
      </th>
      <xsl:apply-templates/>
      </table>
     </body>
    </html>
    </xsl:template>
    <xsl:template match="product">
    <tr>
       <td><xsl:value-of select="@pid"/></td>
       <td>><xsl:value-of select="./description/name"/></td>
       <td><xsl:value-of select="./description/price"/></td>
       <td><xsl:value-of select="./description/details"/></td>
     </tr>
     </xsl:template>
  </xsl:stylesheet>
              

These documents need to either be stored in the table, or they can be passed as parameters. While passing the values as a parameter, make sure that the documents are well formed XML documents. The data type for the parameter is either XML, VARCHAR, CLOB or BLOB. Assuming both the document and stylesheet are stored in the table, the following statement can be used to transform the XML document:

Note: The example assumes that the table in which the documents are store is named xslt and the column name for XML document is xmldoc and XSL document is xsldoc.

                
SELECT XSLTRANSFORM (description USING stylesheet AS CLOB (10M))
	FROM product_details

The query gives you an HTML document as an output which can be viewed in a browser. Listing 13 shows this HTML output:

                
<html>
 <head>
  <META http-equiv="Content-Type" content="text/html; charset=UTF-8">
 </head>
 <body>
  <table border="1">
   <th>
    <tr>
      <td width="80">product ID>td> 
      <td width="200">product name>/td>
      <td width="200">price>/td>
      <td width="50">details>/td>
    </tr>
   </th>
   <tr>
     <td>100-201-01>/td>
     <td>Ice Scraper, Windshield 4 inch>/td>
     <td>3.99>/td>
     <td>Basic Ice Scraper 4 inches wide, foam handle>/td>
   </tr>
  </table>
 </body>
</html>

Users can also store the XML documents and an XSLT stylesheet in separate tables and can apply a single XSLT stylesheet on multiple XML values by joining the tables.

Back to top

Publishing functions

Publishing functions are used to transform the relational data into an XML value. DB2 9 introduces SQL/XML support which has been enhanced and simplified in DB2 V9.5. Some of the DB2 9 SQL/XML functions like xmlelement require providing the name of all the XML elements, attributes and other nodes while the values of these elements are either derived from the relational columns of a table or provided explicitly. Sometimes users need XML values to be generated but don’t want to bother about the element names.

DB2 V9.5 provides extensions to existing publishing functions by introducing the new function xmlrow and xmlgroup. These functions derive both the names and values of the XML elements from the columns of the table. While the xmlrow output is the sequence of the row values represented as XML, the xmlgroup function groups all the values under a root node.

The following table is a sample employee table which has the address details of the employees with the following record.

The following query applies the xmlrow and xmlgroup function to this row.

                

db2 SELECT XMLROW(id, name, street, city,state, country) FROM EMPLOYEE
<row>
 <ID>1</ID>
 <NAME>manoj</NAME>
 <STREET>sector14</STREET>
 <CITY>gurgaon</CITY>
 <STATE>haryana</STATE>
 <COUNTRY>india</COUNTRY>
</row>

db2 SELECT XMLGROUP(id, name, street, city,state, country) FROM EMPLOYEE
<rowset>
 <row>
  <ID>1</ID> 
  <NAME>manoj</NAME> 
  <STREET>sector14</STREET> 
  <CITY>gurgaon</CITY>
  <STATE>haryana</STATE> 
  <COUNTRY>india</COUNTRY> 
 </row>
</rowset>

      

In DB2 9 to get the same result, xmlelement needs to be applied to each of the column values in addition to providing the element name explicitly.

The samples code xmlintegrate.db2 provides some more and complex examples for these functions while comparing it with the V91 version of the same queries. You can find this sample under the sqllib/samples/xml/clp directory.

Back to top

Parameter passing to sqlquery function

In DB2 9, the sqlquery function is used to embed an SQL statement within an XQuery expression. This function takes a string value as input which is a valid SQL full select statement. In DB2 9, there is no way of passing the parameter to this function from XQuery statement.

DB2 V9.5 enhances this function to introduce a new parameter function which takes an integer value as input. Now, the sqlquery function can take multiple arguments as input where the first argument is a string value representing a full select followed by the values of the parameters. The first string argument to the sqlquery function can contain the parameter function which is replaced by the arguments passed to this function after the first mandatory string argument. The integer value passed to the parameter function tells the position of the argument in the invocation of an sqlquery function which is replaced in place of this function call. For example, parameter(1) tells the parser to replace this with the first argument after the string argument. The type of the argument should be the same as the type of value the full select is expecting. A cast function can be used to cast the value to an appropriate type.

Let us take an example using the customer table in the sample database. The sample database can be created by running the db2sampl command or from the first step. The first step is a tool provided by DB2 that gets executed after the installation of DB2 and can also be executed at a later stag by going to Start> All Programs > IBM DB2 > db2 copy name > Set up tools > first steps on Windows.

The customer table has the column cid as a relational column and key value which represents customer id. info XML column has an attribute Cid which again represents the customer id. If the data is consistent, then the attribute Cid value should be the same as cid column value for specific row. The following query checks the number of rows which are consistent. The value of the Cid attribute is passed to the sqlquery function to compare it to the relational cid value.

                
xquery declare default element namespace "http://posample.org";
for $i in db2-fn:xmlcolumn("CUSTOMER.INFO")/customerinfo/@Cid 
for $j in db2-fn:sqlquery("select info from customer where cid=parameter(1)", $i)
return
<out>{$i} </out>
			

The query returns all the customer ids for which data is consistent.

The sample code xqueryparam.db2 provides good examples for one parameter and multiple parameter passing to sqlquery function. The sample can be found in the sqllib/samples/xml/clp directory.

Back to top

Default passing behaviour of existing functions XMLQuery, XMLtable and XMLExists

In DB2 9, The functions xmlquery, xmltable and xmlexists are used to embed the Xquery statement within an SQL statement. The parameter is passed to these functions from the SQL statement using the PASSING clause of these functions.

In DB2 9, if there are a multiple occurrences of these functions within the same SQL statement, a separate PASSING clause for each occurrence is needed. Sometime this makes the query look complex and large in structure. DB2 V9.5 extends these functions to use a default passing mechanism. Now a column name is used as the variable name in the Xquery used in these functions. DB2 passes the same column by default for the variable in case an explicit PASSING clause is not used. This make the query smaller and easier to understand. The following code gives an example based in the sample database tables. The query fetches the first item in the purchaseorder for a customer with the name Robert Shoemaker.

                
SELECT XMLQUERY('declare default element namespace "http://posample.org"; 
                  $PORDER/PurchaseOrder/item[1]' )
     FROM purchaseorder AS p, customer AS c
     WHERE XMLEXISTS('declare default element namespace "http://posample.org";
                 $INFO/customerinfo[name="Robert Shoemaker" and @Cid = $CUSTID]')

      

For the xmlquery function in the SELECT clause, the porder column of the purchaseorder table is passed by default. In the same way, the xmlexists function, info column and custid columns of customer table are passed by default. Make sure to use the name of these variables in capital letters as xquery is a case sensitive language and relation column names are always stored in capital letters.

Back to top

XML validation constraints

DB2 V9.5 enhances the IS VALIDATED clause used in the SELCT statement to include ACCORDING TO XML SCHEMA ID. Now users can provide multiple schemas and select only those XML values which are validated according to these schemas. DB2 V95 also provides the flexibility of providing any XML expression instead of a column as an operand. The below example selects only those documents from the customer table which are validated using customer schema.

                
 db2 SELECT info FROM customer 
        WHERE info IS VALIDATED ACCORDING TO XMLSCHEMA ID customer

      

Back to top

Annotated XML schema decomposition

DB2 9 supports annotation of XML schemas so that the data can be decomposed into relational tables with the restriction on recursive schemas. DB2 V9.5 lifts this restriction, and now users can annotate and decompose the data even when the schema is recursive.

DB2 V9.5 extends the decomposition to provide the order of insertion. This is important when data is decomposed into multiple tables having foreign key relationship. In that case, the primary table should be populated first to maintain the referential constraint. The order of insertion can be specified using the following annotation:

                
<db2-xdb:rowSetOperationOrder xmlns:db2-xdb="http://www.ibm.com/xmlns/prod/db2/xdb1">
  <db2-xdb:order>
    <db2-xdb:rowSet>CUSTOMER</db2-xdb:rowSet>
    <db2-xdb:rowSet>PURCHASEORDER</db2-xdb:rowSet>
  </db2-xdb:order>
</db2-xdb:rowSetOperationOrder>

      

The sample code recxmldecomp.db2 and xmldecomposition.db2 gives good examples to annotate and decompose a recursive schema and order of insertion into the table respectively. The sample can be found in sqllib/samples/xml/clp directory.

Back to top

XML index enhancements

DB2 9 introduced XML indexes. XML indexes can be created on a particular node of the XML documents stored in the database. The data type for the index can be VARCHAR, DOUBLE, DATE or TIMESTAMP. If the data type of the index doesn’t match with the element type of the XML documents, DB2 inserts the XML value but an index will not be created for this particular XML value.

DB2 V9.5 introduces an additional clause for XML indexes REJECT INVALID VALUES. If the index is created with this clause and the data type of the index doesn’t match with the data type of the element in the XML document being inserted, the insert will fail. On the other hand, if the index is created after inserting the value and the data type doesn’t match, the index creation will fail.

This is the default behaviour for DB2 V 9.5 and can also be specified explicitly using the IGNORE INVALID VALUES clause.

The following example will create an index on the customer table Cid attribute with the option REJECT INVALID VALUES.

                
db2 CREATE INDEX index1 ON customer(info) 
     GENERATE KEY USING XMLPATTERN 
        'declare default element namespace "http://posample.org";
         /customerinfo/@cid' as SQL DOUBLE REJECT INVALID VALUES

      

Back to top

Index advisor and optimizer enhancements

An index advisor can be used to get a suggestion on indexing of XML and relational data together. A user can get a big performance gain by using the indexing on both XML and relation data. The DB2 9.5 optimizer uses both type of indexes to optimize the queries and help selecting the best plan for query execution.

Back to top

DB2 Data Web services

DB2 V9.5 XML data can also be exposed as Web services for database manipulation (ML) operations using Data Web services. Data Web services (DES) expose ML operations like insert, update, select and stored procedures as a Web services. These Web services can be consumed using SOAP over HTTP, POST and GET methods e.g., through Web browsers, or custom clients. Data Web services are supported by Eclipse-based tools integrated into existing database tooling.

The control center is also updated to work with XML data.

Back to top

Summary

DB2 9 introduced XML as a new data type and provided the infrastructure for handling XML values. It provided the basic functionality like querying the XML document, registering schema and validation XML documents, interaction between SQL and XQuery using SQL/XML. DB2 V9.5 enhances the current functionality and provides even more functions to handle XML data efficiently.

Back to top

Acknowledgements

Thanks to Matthias Nicola, Susan Malaya, Cindy Sirocco and Henrik Looser for their valuable technical review comments on this article. Thanks to Mel for ID review.

Resources

• What's new in DB2 Viper: Discover what's new in DB2 Viper
  
  
• Query DB2 XML data with XQuery: Use XQuery to query your XML data
  
  
• Get off to a fast start with DB2 Viper: Master the new XML features in DB2 Viper
  
  
• pureXML in DB2 9: Which way to query your XML data?: See how to query your XML data
  
  
• XML in a non-Unicode database: Find tips on how XML can be stored in non-Unicode databases
  
  
• 15 best practices for pureXML performance in DB2 9: View tips and tricks to manage your XML data with the best performance
  
  
• Industry Standard Demos and downloads: Get a quick start to work with DB2 and industry standards XML schema
  
  
• Update XML in DB2 9.5: Learn about XML update features in DB2 V9.5
  
  
• Use WebSphere Federation Server Version 9.1 to integrate XML data: Learn about WebSphere Federation Server Version 9.1 to integrate XML data
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  

• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

		Manoj works as an application developer for the DB2 Samples Development team in the IBM India Software Labs. His expertise is in the area of DB2 pureXML and Java application development. He is an IBM certified DB2 9 application developer, database administrator, and solution developer for XML and related technologies. He has authored an IBM Redbooks publication for DB2 Express-C application development. Prior to this role, he was a member of the functional verification team to test the utilities and deadlock monitoring feature for DB2.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top