Introduction

XML support in DB2 Universal Database (UDB) Version 8.x is based on DB2's relational infrastructure. Previous to the DB2 Viper release, XML data was either stored intact as a character large object (CLOB) or shredded to relational tables.

In contrast, DB2 Version 9.1 has true native support for XML data. XML is now handled as a new data type, and XML data is stored in a parsed annotated tree that is separate from the relational data storage. XML indexing based on an XML pattern has been introduced, along with support for XQuery and SQL/XML as the languages to query and publish XML data. In order to understand the impact of these new XML features on migration, it is necessary to compare the different techniques for storing and querying XML data in the DB2 8.x, to similar or new XML functionality available in DB2 UDB Version 9.

This article is the first of a three-article series on migrating XML applications from DB2 8.x to DB2 Viper. The series starts with a description of a Java-based stored procedure that you can use for performing sub-document updates for XML data. You can download the source code and a jar file for the updated stored procedure, and follow the instructions for installing it.

The second article compares the XML features in DB2 Version 8.x with those in DB2 Viper. It then briefly discuss the new XML features introduced in DB2 Viper, and goes into the details regarding the impact the new XML support has on migrating existing XML-based applications. This article also has the source code for a Java-based utility to help generate scripts to migrate the database objects.

The last article in the series walks you through a step-by-step sample migration scenario. It includes source code for the sample scenario.

Update stored procedure

For XML documents stored natively in DB2, there is no out-of-the-box functionality for performing sub-document updates. The reason for this missing functionality is the current lack of standards for defining updates in XQuery.

One solution to this problem is to bring the document over to the client, modify it, and then save it back into the database. This approach is limited by the XML capabilities of the client environment and also requires a level of expertise in writing document object model (DOM)-based clients.

By creating an update stored procedure it is possible to update XML documents in the database without needing to bring them over to the client. This stored procedure enables partial updates of XML documents stored natively in the database.

The stored procedure allows for:

• Changing the value of any text or attribute nodes in the target XML document
• Replacing an element node (along with all its children) in the XML document with another XML element
• Deleting a node in the XML document
• Inserting a new element
• Multiple updates to the source document
• Updates to multiple source documents
• Replacing another XML document with the modified one
• Inserting the modified document into a new record

The update information can be:

• Statically embedded in the update call
• Dynamically created at runtime using SQL
• Computed using an arithmetic expression on the original text or attribute value

Note: Behind the scene, the update stored procedure still does a full document update.

Back to top

XMLUPDATE command

DB2XMLFUNCTIONS.XMLUPDATE (commandXML, querySQL, updateSQL, errorCode, errorMsg)

• commandXML -- This argument is an XML string that encapsulates the update commands. These commands are then applied to the XML documents selected by the querySQL.

  The structure of this command is:

  <updates namespaces="">
  <update using="" col="" action="" path="">update value</update>
  </updates>
  
  • <updates> -- This is the root element that wraps all the update command elements.
  • @namespace -- The value for this attribute should be prefix:namespace strings separated by semicolons. The prefixes are then used in any paths expressions used to navigate in the XML document.
    • Essential -- No (It is only needed if namespaces are used in any path.).
    • Even default namespaces have to be qualified with a prefix.
  • <update> -- This element defines each modification that needs to be done on the target XML document.
    • Occurrence -- One or more of these elements can be defined.
    • Each occurrence handles one modification to the document.
  • @col -- The value for this attribute should be a number corresponding to the location of the column being modified in the querySQL.
    • Essential -- Yes.
    • Valid value -- Column position starting from one.
  • @path -- The value of this attribute is the XPath location of the node in the target XML document. If the path is invalid the stored procedure is aborted.
    • Essential -- Yes.
    • Valid value -- XPath expression.
    • Be sure to set the namespaces attribute if you are using namespaces in your XPath.
    • You cannot use wildcards for namespaces.
  • @using -- The only valid value for this attribute is SQL. If this attribute is present and set to SQL, then the update value (child node of the <update> element) is considered to be a SQL query. The first column from the first row of the query result is used as the new update value. If the query fails, the stored procedure is aborted.
    • Essential -- No.
    • Valid value -- SQL.
    • For XQuery you can either use the keyword XQuery or embed the XQuery inside your SQL using SQL/XML functions.
  • @action -- This attribute defines the action to be taken on the target node (located using the XPath defined in the @path attribute) in the XML document. If the action fails, the stored procedure is aborted.
    • Essential -- No. If action is not set, it is assumed to be a replace.
    • Valid value -- Replace, append, delete, and compute:
      • replace -- Replace the target node with the update value.
      • append -- Append the update value as a child to the target node.
      • delete -- Delete the target node.
      • compute -- The update value is treated as a parameterized expression. The question marks (?) in the expression are replaced with the existing text value of the target node. The expression is then computed and the resulting value replaces the existing value in the target node. The XPath for a computed value should be a leaf node only.
  • update value -- This is the child node of each update command (//update/*). It can either be a text node or an element.
    • Essential -- No. You do not need it for action=delete.
    • Valid value -- When the @using attribute is set to SQL, the child node should be a text value. It is treated as the SQL expression. When the @action attribute is set to compute, the child node should be a text value. It is treated as the parameterized expression. In all other cases, the child node is considered as the value to be replaced.
• querySQL -- Any valid SQL select statement that retrieves the XML document(s) that need to be updated.
  • Essential -- Yes.
  • Valid value -- Only XML columns can be selected. If any other column is selected, the stored procedure is aborted.
• updateSQL -- It represents a parameterized update SQL. The modified XML document is bound to the update SQL as a runtime parameter. It allows a modified XML document to be saved to other XML columns in the database.
  • Essential -- No. If this argument is null, then updatable cursors are used to modify the selected columns.
  • Important: When executing the update stored procedure from the command-line processor (CLP), you always need to set the value for the updateSQL parameter. If this value is set to null or empty string, CLP throws a JCC exception: Column not updatable. It works (updateSQL set to null) when you call the update the stored procedure from inside your application code (Java).
• errorCode -- A value of -1 indicates that the stored procedure was aborted due to some error. If the update was successful, a positive value indicating the number of records that have been updated is returned.
• errorMsg -- Error messages, including any exception thrown by the XML parser and the JCC driver.

  Note: If you get a java.lang.OutOfMemoryError then increase the java heap size:

  db2 update dbm cfg using JAVA_HEAP_SZ 1024

  
  

Back to top

Set up the stored procedure

First you need to install the update stored procedure jar into DB2. This only needs to be done once. Next, for each database, you need to register the stored procedure separately.

Important:

• If you want to execute XQueries in the stored procedure, you need to have the JCC driver set up for DB2. Make sure DB2 is running, and then execute the following command from your DB2 command window:

  db2set DB2_USE_DB2JCCT2_JROUTINE=on

  
  

• Increase your default JAVA heap size so that it is able to serialize the XML documents in memory by running the following command from your DB2 command window:

  db2 update dbm cfg using JAVA_HEAP_SZ 1024

  
  

• If you are calling the update stored procedure in an application loop remember to close the callable statement object after each call or you could end up running out of handles for the callables statement. You will receive the following DB2 SQL error message:

  SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLH207 0X5359534C564C3031)

  
  

Steps to set up the update stored procedure

1. Compile the java code and create the db2xmlfunctions.jar file, by performing the following steps.

   Note: The db2xmlfunctions.jar can also be downloaded in the Downloads section. If you opt to download the file, skip to Step 2.

   1. Create a directory /temp/samples.
   2. Copy the XMLUpdate_code.zip (found in the Downloads section) to your temp directory.
   3. Extract the XMLUpdate.java and XMLParse.java files to the /temp/samples directory.
   4. Compile the java files and create the jar file for the UDF.
   5. On Microsoft Windows, open a DB2 command window:

      SET CLASSPATH= .;%DB2PATH%\java\db2java.zip; %DB2PATH%\java\db2jcc.jar; %DB2PATH%\java\db2jcc_license_cisuz.jar; "%DB2PATH%\java\jdk\bin\javac.exe" -d . *.java "%DB2PATH%\java\jdk\bin\jar" cvf db2xmlfunctions.jar com/ibm/db2/xml/functions/*.class

      
      

      On AIX, set DB2PATH to the DB2 SQLLIB directory:

      CLASSPATH=$DB2PATH/java/sqlj.zip:$DB2PATH/java/db2java.zip $DB2PATH/java/jdk/bin/javac.exe" -d . *.java $DB2PATH/java/jdk/bin/jar" cvf db2xmlfunctions.jar com/ibm/db2/xml/functions/*.class

      
      

      Note: The above assumes either the sh or bash shells. Change as appropriate for csh, tsh, and others.

2. Install the stored procedure in DB2:

   DB2 -t connect to your_dbname; CALL SQLJ.INSTALL_JAR('file:/temp/samples/db2xmlfunctions.jar' , db2xmlfunctions,0);

   
   
3. Register the stored procedure in your database:

   CREATE PROCEDURE db2xmlfunctions.XMLUPDATE( IN COMMANDSQL VARCHAR(32000), IN QUERYSQL VARCHAR(32000), IN UPDATESQL VARCHAR(32000), OUT errorCode INTEGER, OUT errorMsg VARCHAR(32000)) DYNAMIC RESULT SETS 0 LANGUAGE JAVA PARAMETER STYLE JAVA NO DBINFO FENCED NULL CALL MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'db2xmlfunctions:com.ibm.db2.xml.functions.XMLUpdate.Update' ; TERMINATE;

   
   

Remove the stored procedure

If you make any changes to the stored procedure, you should first uninstall it from DB2 before registering the new version:

DROP PROCEDURE DB2XMLFUNCTIONS.XMLUPDATE(VARCHAR(32000),
VARCHAR(32000),VARCHAR(32000),INTEGER, VARCHAR(32000));

CALL SQLJ.REMOVE_JAR(DB2XMLFUNCTIONS);

Back to top

XMLUpdate samples

For XMLUpdate samples, complete the following steps:

1. Create a test table:

   Create table XMLCustomer(cid integer not null PRIMARY KEY, info XML );

   
   
2. Insert a sample XML document into the table:

   Insert into XMLCustomer (cid, info ) values (1006 , XMLPARSE ( DOCUMENT ' <customerinfo xmlns=" http://posample.org " Cid="1006"> <name>Hardeep Singh</name> <addr country="United States"> <street>555 Bailey Ave</street> <city/> <prov-state>CA</prov-state> <pcode-zip> 95141</pcode-zip> </addr> <phone type="">543-4610</phone> </customerinfo>' PRESERVE WHITESPACE ) );

   
   

   Note: Since the update calls modify the original XML document, you might need to delete the inserted document and reinsert it for some queries.

Example queries

The following are example queries:

1. Replace a node: action=replace.

   Update the test document by replacing the simple name element with a complex name element:

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="replace" col="1" path="/x:customerinfo/x:name"> <name><fname>Hardeep</fname><lname>Singh</lname></name> </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   
2. Update using a SQL query to get the new value: using=SQL.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update using="sql" action="replace" col="1" path="//x:customerinfo[@Cid=1006]/x:addr/x:pcode-zip/text()"> select cid from XMLCustomer where cid=1006 </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   
3. Compute the value with the given expression: action=compute.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="compute" col="1" path="/x:customerinfo/x:addr/x:pcode-zip/text()"> (20+?)*32-? </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   
4. Multiple actions on the target XML document:

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update using="sql" action="replace" col="1" path="/x:customerinfo/x:addr/x:pcode-zip/text()"> select cid from XMLCustomer where cid=1006 </update> <update action="compute" col="1" path="/x:customerinfo/x:addr/x:pcode-zip/text()"> (2+?)*10-? </update> <update action="delete" col="1" path="/x:customerinfo/x:name"/> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   
5. Validate the document when you update it.

   For this you need to create and register the schema in XSR.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update using="sql" action="replace" col="1" path="/x:customerinfo/x:addr/x:pcode-zip/text()"> select cid from XMLCustomer where cid=1006 </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=xmlvalidate( ? according to XMLSCHEMA ID test.schema2) where cid=1006',?,?)

   
   
6. Use XMLUpdate to replace an attribute value.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="replace" col="1" path="/x:customerinfo/x:phone/@type"> tie line </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   
7. Use XMLUpdate to replace a text value.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="replace" col="1" path="/x:customerinfo/x:addr/x:city/text()"> San Jose </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   

   Important: It is necessary to specify text() at the end of the path. This step ensures that even an empty element (one with no existing text node) is updated. If text() is omitted and there is no existing text value to replace, the update command fails.

8. Use XMLUpdate to append a child node.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="append" col="1" path="/x:customerinfo/x:addr"> <county>Santa Clara</county> </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

   
   

   Note: The new node <county> is not in any namespace.

9. Use XMLUpdate to insert the updated XML to a new row.

   Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="replace" col="1" path="/x:customerinfo/x:name"> <name>Marja Soininen</name> </update> <update action="replace" col="1" path="/x:customerinfo/@Cid">1008</update> </updates>', 'Select info from XMLCustomer where cid=1006', 'insert into XMLCustomer (cid, info ) values (1008, cast( ? as xml))',?,?);

   
   
10. Use XMLUpdate to delete a node.

    Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="delete" col="1" path="/x:customerinfo/x:name"/> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

    
    
11. When @action is not set in the update element, it defaults to replace.

    Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update col="1" path="//x:customerinfo[@Cid=1006]/x:phone"> <phone><areacode>910</areacode></phone> </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

    
    
12. The following example shows XMLUpdate with invalid namespace or a namespace without a prefix:

    Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://my.org"> <update col="1" path="//x:customerinfo[@Cid=1006]/x:phone"> <phone><areacode>910</areacode></phone> </update> </updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

    
    

    This query returns with the error set to 1 and the following error message:

    <error type='abort' action='replace' msg='Cannot find path //x:customerinfo[@Cid=1006]/x:phone) in the XMLDocument'>
13. The following example shows XMLUpdate with a missing path in the update element:

    Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates > <update col="1"> (20+?)*32-? </update></updates>', 'Select info from XMLCustomer where cid=1006', 'update XMLCustomer set info=? where cid=1006',?,?);

    
    

    This query returns with the error set to 1 and the following error message:

    <error type='abort' action='null' msg='path not defined'></error>
14. Replace a node with a new node in the same namespace.

    Call DB2XMLFUNCTIONS.XMLUPDATE ( '<updates namespaces="x:http://posample.org"> <update action="replace" col="1" path="/x:customerinfo/x:name"> <name xmlns="http://posample.org"> <fname>Marja</fname><lname>Soininen</lname> </name> </update> </updates>', 'Select info from XMLCustomer where cid=1008', 'insert into XMLCustomer (cid, info ) values (1007, cast( ? as xml))',?,?);

    
    

Back to top

Conclusion

The update stored procedure described in this article enables partial updates to XML documents stored natively in the database. The next article delves deeper, and explores, in detail, the impact the new XML support has on migrating existing XML-based applications.

Acknowledgements

Thanks to Matthias Nicola, Bert Van der Linden, Irina Kogan, Annie Wang, Ying Chen and Xiaoli Du for their help with this article.

Back to top

Downloads

Information about download methods

Resources

Learn

• DB2 Viper Web site: Learn more about DB2's XML support.
  
  
• "Firing Up The Hybrid Engine": Learn about the details of native XML capibilities of DB2.
  
  
• "What's new in DB2 Viper: XML to the Core" (developerWorks, February 2006): Get an overview of the new XML technologies now in beta in DB2.
  
  
• "Get off to a fast start with DB2 Viper" (developerWorks, March 2006): Learn how to insert, import, and validate XML data.
  
  
• "Query DB2 XML Data with SQL" (developerWorks, March 2006): Learn how to query DB2 XML data using SQL and SQL/XML.
  
  
• "Use DB2 native XML with PHP": (developerWorks, October 2005): Learn about the the native XML capabilities coming in the next version of DB2 Universal Database for Linux, UNIX, and Windows.
  
  
• "Native XML Support in DB2 Universal Database": Compare and contrast DB2's new XML support with traditional relational database technology.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Participate in the DB2 Viper test drive. Download and try it out today.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Hardeep Singh is a member of the DB2 Native XML team. He is the architect for DB2 XML tooling and is responsible for XML Extender application migration to Viper. He has more than 21 years of industry experience.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Update stored procedure
• XMLUPDATE command
• Set up the stored procedure
• XMLUpdate samples
• Conclusion
• Acknowledgements
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers