XML application migration from DB2 8.x to DB2 Viper, Part 1: Partial updates to XML documents in DB2 Viper

The "update" stored procedure

The first of a three-article series on migrating your XML applications from DB2® UDB V8.x to DB2 Viper, this article describes a method for performing partial updates to XML documents stored natively in DB2 Viper, using a stored procedure that's included as a download. For more information, read additional articles in this series.

Hardeep Singh (hardeep@us.ibm.com), Architect DB2 XML tooling, DB2 XML application migration, IBM

Photo: Hardeep SinghHardeep 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.



11 May 2006

Also available in Chinese

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.


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

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);

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))',?,?);

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.


Downloads

DescriptionNameSize
Update stored procedure classesdb2xmlfunctions.jar13 KB
Update stored procedure source codeXMLUpdate_code.zip9 KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, XML, SOA and web services
ArticleID=111244
ArticleTitle=XML application migration from DB2 8.x to DB2 Viper, Part 1: Partial updates to XML documents in DB2 Viper
publish-date=05112006