Introduction

XML support in DB2 UDB Version 8.x was based on DB2's relational infrastructure. XML data was either stored intact as a character large object (CLOB) or shredded to relational tables. DB2 UDB Viper, on the other hand, 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, you need to compare the different techniques for storing and querying XML data in the DB2 8.x, to similar or extended XML functionality available in DB2 Viper. Based on this comparison, this article tries to identify the areas of the application code that need to be changed.

XML support in DB2 UDB Version 8.x

DB2 8.x provides XML functionality to the underlying relational data using SQL/XML and the DB2 XML Extender. DB2 XML Extender provides a number of user-defined types (UDT) and user-defined functions (UDF), and stored procedures to enable XML support. XML documents are stored in a relational form and the XML Extender infrastructure is used to provide an XML feel to the relational data. A subset of SQL/XML functions are also supported for publishing relational data as XML. More details can be found at the DB2 XML Extender Web site.

DB2 also allows the creation of user-defined java functions and stored procedures that can incorporate XML and XSL parsers (installed as a part of DB2 8.x) into the code.

XML support in DB2 Viper

DB2 Viper support for native XML storage removes the need to shred XML into relational tables for querying and publishing purposes. The new storage retains XML in a parsed, annotated tree form that's similar to XML document object model (DOM). For applications that still need to shred XML data into relational tables, enhanced shredding capability using annotated XML schema mapping has been added to this release.

XML feature comparison - DB2 UDB Version 8.x and DB2 Viper

Note: The tight integration of the XQuery engine in the database for the native XML storage makes it possible to obtain query execution plans for both SQL and XQuery.

Back to top

XML features introduced in DB2 UDB Viper

Before you dive into a step-by-step migration of a sample application, take a brief look at DB2 UDB Viper XML support.

Storage of XML data

While interacting with DB2's native XML support, the abstraction shown in Figure 1 is a column of type XML in a relational table. Any well-formed XML statement can be inserted into that column. In the physical storage layer though, the primary storage unit is a node.

For tables that have a mix of relational and XML columns, the relational columns are stored in relational format and XML columns are stored as a parsed tree.

XML index

The XML index is a new index that can provide efficient evaluation of XML path expressions to improve performance during queries on XML documents.

The XML index uses an XMLPATTERN (an XPath without predicates, only a child axis (/) and a descendent-or-self axis (//) ) to index paths and values in XML documents stored in a single XML column. When creating an index, its possible to specify what paths to index and what type. Any nodes that match the path expression, or the set of path expressions, in XML stored in that column are indexed. The index points directly to the node in storage that is linked to its parent and children for fast navigation.

create index idx3 on dept(deptdoc) generate key using
xmlpattern '/dept/employee/name' as sql varchar(35);

XML Schema Repository

The XSR is a repository for all XML artifacts used to validate and process XML instance documents stored in XML columns. The XSR removes the additional overhead required to locate external documents, along with the possible performance impact. Each database contains an XML schema repository that resides in the database catalog and comprises catalog tables, catalog views, and some system defined stored procedures (for registering) to enter data into these catalog tables. XML schemas, DTDs and external entities must be explicitly registered in the XSR before use.

Annotated schema mapping

DB2 Viper provides a new shred mechanism using annotated XML schemas. Instead of creating a separate mapping file (DAD) the XML schemas are annotated with the relational mapping information. These annotated schemas are then registered to the XSR. They are used at runtime to decompose XML documents being inserted to the column. Data types for decomposed XML values are standard relational types. The shredded relational columns can be indexed using relational indexing. For more information, refer to "From DAD to annotated XML schema decomposition" (developerWorks, April 2006).

Figure 2 illustrates an example of an annotated schema mapping. It shows how an entire XML document can be stored in the same record along with data that has been extracted from the XML document.

The intact XML document is stored in an XMLPO column (type XML) along with the extracted orderID, extracted to the column ORDERID (VARCHAR), and orderDate, extracted to the column ORDERDATE (DATE).

SQL/XML functions

XML publishing functions

Document construction is the process of composing an XML value out of its small parts like elements and attributes. SQL/XML provides several functions for this process that enable construction of XML documents from relational data.

The following SQL/XML functions for publishing existed in DB2 UDB Version 8.x

• XMLAGG: Aggregates XML values provided as input and returns the concatenation of all non-null input XML values.
• XMLATTRIBUTES: Generates attributes for the XML element being constructed.
• XMLCONCAT: Concatenates non-null input expressions of type XML to return a sequence of XML values
• XMLELEMENT: Constructs an XML element based on input arguments.
• XMLFOREST: Constructs a forest of XML elements from input arguments.
• XMLNAMESPACES: Constructs namespace declarations for elements generated by XMLELEMENT or XMLFOREST.
• XML2CLOB and XMLSERIALIZE: XML serialization functions convert the XML value into a string value.

DB2 Viper supports all the SQL/XML function in DB2 V8 (plus accepting or returning a new XML type).

The following new SQL/XML publishing functions are also supported:

• XMLCOMMENT: Generates an XQuery comment node.
• XMLPI: Generates an XQuery processing instruction node.
• XMLTEXT: Generates an XQuery text node.
• XMLDOCUMENT: Generates an XQuery document node.

The following are new SQL/XML functions for IN/OUT and validation:

• XMLPARSE: Parses character/BLOB data, and produces XML value.
• XMLCAST: Cast to or from an XML type.
• XMLVALIDATE: Validates the XML value against an XML schema and type, and annotates the XML value.
• IS VALIDATED: Checks if the XML value has been validated.

XMLPARSE

A string value that represents an XML document can be transformed to a DB2 XML type using the SQL/XML function XMLPARSE. The function takes a serialized XML document and converts it into an instance of the XML type. XMLPARSE is used when the default parsing options need to be changed. The use of XMLPARSE in general causes the system to forget that it is dealing with XML data because the input to XMLPARSE is a string or binary type.

insert into customer (cid, info) values (?,XMLPARSE(
DOCUMENT cast(? as BLOB(2M)) preserve whitespace ))

XMLCAST

This pseudo function casts an XML value to a non-XML data type, or converts a non-XML value to an XML value. For example, it converts a SQL/XML value to a SQL datatype. It supports casts to a subset of SQL scalar types. The source of the cast is assumed to be derived from XML schema atomic types or untyped text, or is a node whose typed-value is an atomic type or untyped text.

values(XMLCAST(XMLQUERY('$m/customer/@cid' PASSING
xmlparse(document '<customer cid="100"><name>hardeep
singh</name></customer>') AS "m") AS INTEGER) )

XMLValidate

The XMLValidate function takes an XML value and validates it against an XML schema, returning an XML value augmented with the default values and type annotations that are defined in the XML schema. There is no column level XML schema validation for a native XML column. Validation has to be explicitly added to each insert or update statement. This concept allows you to save XML documents from different XML schemas in the same column

Note: Validation against DTDs is not supported. DTDs are only used to add default values and resolve entity references.

insert into purchaseOrders (pid,porders) values (2, XMLValidate(?))

Is validated

The is validated predicate can be used to check whether XML data has already been validated. It is used in check constraints and in filtering rows in a WHERE clause, and is not impacted by the XML schema used for validation

Alter table purchaseOrders add constraint xml_check check
(pOrder is validated))
Select pOrder from purchaseOrders where pOrder is validated

XMLSERIALIZE

XML values are processed in an internal representation that is not a string and not directly comparable to string values. An XML value can be transformed into a serialized string value representing the XML document using the XMLSERIALIZE function. This function is quite useful in environments where the DB2 driver does not support XML type.

The XMLSERIALIZE function returns a serialized XML value of the specified data type generated from the XML expression argument. The result has the data type specified by the user. If the result of the XML expression is null, the result is the null value. The serialized XML value that is returned must be smaller than 2GB. The serialized result is encoded with UTF-8.

SELECT XMLSERIALIZE ( CONTENT
XMLELEMENT ( NAME "customer",
XMLATTRIBUTES (e.cid AS "id"),
XMLELEMENT (NAME "name", e.name),
XMLELEMENT (NAME "email", e.email),
XMLELEMENT (NAME "zip", e.zip))
AS CLOB(7200)) AS CINFO FROM customer e where cid=1000

The following are new SQL/XML functions and predicates for querying XML data:

• XMLTABLE: Executes an XQuery, returns the result sequence as a relational table.
• XMLEXISTS: Determines if an XQuery returns a sequence of one or more items.
• XMLQUERY: Executes an XQuery, and returns the result sequence.

XMLTABLE constructs a table from an XML document

XMLTABLE provides a standard way to publish relational data from an XML document. It queries an XML value and returns a relational rowset (shreds XML to relational). The virtual table created as a result of the shredding can be inserted into a pre-existing SQL table using the SQL insert statement.

The XMLTABLE syntax allows the specification of the shape (number of columns and their datatypes) of the relation generated. The XQuery argument determines the generation of row instances and the values of each column.

The query shown in Figure 4 takes the input XML document (product) and shreds out the @pid and the root node from it. It then stores these into the XMLPRODUCT table.

XMLQUERY

XMLQUERY is a SQL/XML publishing (pseudo) function whose purpose is to evaluate an XQuery expression and return an XML value, that might be an XQuery sequence of zero or more items, to the application.

The query shown in Figure 5 returns only some elements of the XML data stored in the DESCRIPTION column for every row in the XMLPRODUCT table.

XMLEXISTS

This XMLEXISTS predicate queries a SQL/XML value using XQuery. If the query returns a sequence that is not empty, the function returns the true condition, otherwise it returns false.

Back to top

Impact of DB2 Viper XML support on XML based applications

An applications design is influenced by the storage model for the XML data (intact or shredded). The storage model is influenced by functionality limitations on storing, querying, and modifying the XML data and any performance requirements. Therefore, with the introduction of new functionality, the XML storage model can change since many of the limitations of the DB2 XML Extenders have been overcome.

The rest of the article describes, in detail, the difference in DB2s capabilities of handling XML data. How to migrate existing applications to DB2 Viper in order to use the new feature set is also reviewed.

Set up the database

Right up front you may notice that some of the steps needed for setting up a database for XML support have changed.

In order to understand the migration process, first look at the some basic steps for creating a database for storing XML data in DB2 Version 8.x and DB2 Viper.

1. Create a database and enable it for XML.

   DB2 Version 8.x

   db2 create db MIGRATE dxxadm ENABLE_DB MIGRATE

   
   

   DB2 Viper

   db2 create db MIGRATE using codeset utf-8 territory us

   
   
2. Register DTD/XML schemas

   DB2 Version 8.x. Only DTDs can be registered.

   insert into db2xml.dtd_ref (dtdid, content, usage_count, author, creator, updator) values ( 'customerdtd', db2xml.XMLCLOBFromFile( '/home2/hardeep/migrate/customer.dtd'), 0, 'xml', 'xml', 'xml');

   
   

   DB2 Viper. Only XML schemas are used for validation.

   db2 register xmlschema http://migrate.db2 from /home2/hardeep/migrate/customer.xsd as migrate.customer db2 complete xmlschema migrate.customer

   
   
3. Create a table with a column for storing XML data.

   DB2 Version 8.x. Using XML Extender data type XMLCLOB.

   CREATE TABLE PurchaseOrder ( POid BIGINT NOT NULL PRIMARY KEY, Info DB2XML.XMLCLOB)

   
   

   Note: Column can be of type XMLCLOB, XMLVARCHAR, and XML FILE.

   DB2 Viper. Column of type XML.

   CREATE TABLE PurchaseOrder ( POid BIGINT NOT NULL PRIMARY KEY, Info XML)

   
   

   Note: The XML type is a standard SQL type unlike XMLCLOB, XMLVARCHAR, and XML FILE.

4. Create indexes on the XML data.

   DB2 Version 8.x. Create DAD mapping and enables the XML column to create indirect indexes on the side tables.

   dxxadm enable_column migrate purchaseorder info "/home2/hardeep/migrate/po.dad" -r poid

   
   

   DB2 Viper. Create XML indexes using an xmlpattern.

   create unique index order_key on purchaseorder (info) generate key using xmlpattern '/purchaseOrder/@poid' as sql double;

   
   
5. Create Mappings for shredding incoming XML to relational

   DB2 Version 8.x. Create DAD mapping and enable the XML collection.

   dxxadm enable_collection migrate customerinfo /home2/hardeep/migrate/customer.dad

   
   

   DB2 Viper. Annotate the XML schema and enable it for decomposition.

   db2 register xmlschema http://migrate.db2 from /home2/hardeep/migrate/customer.xsd as migrate.customer db2 complete xmlschema migrate.customer ENABLE DECOMPOSITION

   
   

Back to top

Migrating an existing XML database from DB2 Version 8.x to DB2 Viper

Now that you have some idea about the difference in the way the database is setup for XML support you can start looking at the basic steps needed to migrate an existing XML database from DB2 Version 8.x to DB2 Viper.

Create a new UTF-8 database for DB2 Viper

The first thing to understand is that, since DB2 Viper supports only UTF-8 encoding for XML data, if your existing database is not in UTF-8 you need to create a new database with the UTF-8 codepage set.

To create the new database use the following command:

db2 create db migrate using codeset utf-8 territory us

Next, you need to export all of your database objects from your old database and then import them over to your new database (UTF-8). This is a manual operation. This article only discusses the migration process of DB2 objects that relate to XML data and leave the rest for you to look up in the DB2 migration documentation.

A migration utility is provided with this article. This helps you in generating some of the data definition language (DDL) scripts needed to migrate the database objects.

Migrating DTDs and XML schemas

Since DB2 Viper does not use DTDs for XML validation it is necessary to convert any DTDs used by the current XML application to XML schemas. In order to do this you first need to load the DTDs from your existing DB2 Version 8.x database (DTD_REF table) to the local file system.

select db2xml.XMLFileFromCLOB(db2xml.clob(content),
'/home2/hardeep/migrate/customer.dtd') from db2xml.dtd_ref where DTDID='customerdtd';

Note: Since XML schemas are not saved in the database, you can get them from the file system.

Once you have copied the DTD from the DTD_REF table to your file system, you need to convert it to an XML schema. There are a number of free Web-based utilities for XML that can help you with the conversions between DTDS and XML schemas (search using the keywords: DTD Schema convert).

Register the XML schemas

In order to use the XML schemas to validate XML documents, you first need to register them in the XML schema repository.

db2 register xmlschema http://migrate.db2 from
/home2/hardeep/migrate/po.xsd as migrate.po
db2 complete xmlschema migrate.po

Note:Large schemas require sufficient space in the application heap.

db2 update db cfg using applheapsz 1024

Migrate tables with XML data to the new database

In DB2 Version 8.x, for storing intact XML documents, the column could have been of type CLOB, BLOB, VARCHAR or XML Extender type DB2XML.XMLCLOB, DB2XML.XMLVARCHAR, or DB2XML.XMLFILE. For DB2 Viper the column type is XML.

Therefore, a table that was created (in DB2 Version 8.x) with a CLOB column for storing XML data:

CREATE TABLE Product(Pid VARCHAR(10) NOT NULL PRIMARY KEY,
Name VARCHAR(128), Category VARCHAR(32),Price DECIMAL(30,2), Info CLOB);

Is now created with an XML column for storing XML data:

CREATE TABLE Product(Pid VARCHAR(10) NOT NULL PRIMARY KEY,
Name VARCHAR(128), Category VARCHAR(32),
Price DECIMAL(30,2), Info XML);

Note: In the above table, if the data in the NAME, CATEGORY, and PRICE columns was extracted from the XML document, for creating relational indexes, then the new table does not need these columns at all since now you can index into the stored XML document itself. The schema for the table could be simplified to only two columns.

CREATE TABLE Product(Pid VARCHAR(10) NOT NULL PRIMARY KEY, Info XML);

A table that was created (in DB2 Version 8.x) with a DB2XML.XMLCLOB column for storing XML data:

CREATE TABLE PurchaseOrder(Poid BIGINT NOT NULL PRIMARY KEY,
Status VARCHAR(10) NOT NULL WITH DEFAULT 'New',
Info DB2XML.XMLCLOB NOT LOGGED NOT COMPACT NOT NULL);

Is now created with an XML column for storing XML data:

CREATE TABLE PurchaseOrder(Poid BIGINT NOT NULL PRIMARY KEY,
Status VARCHAR(10) NOT NULL WITH DEFAULT 'New', Info XML NOT NULL);

Import XML data from DB2 Version 8.x to DB2 Viper

Once you have created all your tables in the new database, the next step is to export data out of the old database (DB2 Version 8.x) tables and import it into the new database (DB2 Viper) tables. In order to better understand this process, look at how the import utility for DB2 Viper handles XML data.

The DB2 IMPORT utility provides a simple way for you to populate your XML columns with data from XML files. The utility uses a delimited ASCII file containing a parameter that specifies each XML files that you need to import. By convention, this file has a .DEL extension. Each line in the file represents a row of data to be imported into your table. If a line contains an XML Data Specifier (XDS), IMPORT reads the data contained in the referenced XML file and imports that into DB2.

EXPORT XML data from DB2 Version 8.x

Since the EXPORT utility for DB2 Version 8.x does not produce a .DEL file with an XDS, the IMPORT utility for DB2 Viper can also handle a similar .DEL files created by the EXPORT utility for DB2 Version 8.x for large objects (LOBs).

So for XML data stored in columns of type CLOB, BLOB, VARCHAR, DB2XML.XMLCLOB, DB2XML.XMLVARCHAR, or DB2XML.XMLFILE, the first step is to EXPORT the table containing the XML data, using the LOB option.

EXPORT TO delfilename, OF DEL LOBS to lobfilespath MODIFIED BY
LOBSINFILE select statement

Note: The lobfilespath path should exist and also the delfilename should have a full path and this path should also exist, as shown in the following example.

DB2 EXPORT to C:/temp/migration/export/prod.del of del LOBS
to C:/temp/migration/export/lobs modified by LOBSINFILE
select pid,name,category,price,Info from product

Note: The path C:/temp/migration/export/lobs needs to exist before the call is made.

Note: If you were going to drop the name, category, and price columns from your new table, you would modify the select statement to only select the pid and info columns.

The output of the call would produce a prod.del file and one or more lob files containing the XML data.

100-100-01,Denim Roll Cuff Crop Pants,pants,+024.89,prod.del.001.lob.0.474/
100-100-02,Bermuda Shorts,shorts,+09.99,prod.del.001.lob.474.488/

Important: If the column type for the XML data is a VARCHAR, you need to cast the column to a CLOB in the select statement to prevent the XML data from being inlined in the DEL file.

DB2 export to C:/temp/migration/export/prod.del of del LOBS
to C:/temp/migration/export/lobs modified by LOBSINFILE
select pid,name,category,price,cast(Info as clob) from product

The cast (Info as clob) makes sure that the XML data is exported to lob files rather than being added to the delimited (DEL) file.

If you did not cast the column, the output would look like this:

100-100-01,Denim ,pants,+024.89,<product pid= </product>
100-100-02,Bermuda ,shorts,+09.99,<product pid= </product>

The IMPORT command fails with this format as it considers it to be a malformed XDS.

IMPORT XML data into DB2 Viper

The exported data can now be imported to the table created in the new database using the IMPORT utility. You pass the name to the DEL file and the path to the LOB files.

DB2 IMPORT FROM C:/temp/migration/export/prod.del OF DEL LOBS FROM
C:/temp/migration/export/lobs MODIFIED BY XMLCHAR INSERT INTO product;

Note: For XML data shredded to relational columns there is no need to migrate the data.

Migrate indexes on the XML data

In DB2 Version 8.x XML, data stored in XML columns can have indirect indexes using relational side tables. The mapping information that associated values in the side with documents in the XML column is defined in the DAD files.

With DB2 Viper's ability to create XML indexes over paths inside XML documents, the need for creating indirect relational indexes using side tables is over. Some of the key parameters that are used to create the XML index are similar to the parameters in the DAD mapping. The values from these parameters (in DAD mapping) can be used in creating the XML indexes. Analyze an example of a DAD mapping used to create side table for a purchaseorder column of type DB2XML.XMLCLOB.

<DAD>
 <Xcolumn >
<table name="Shipto">
<column name="name" type="varchar(128)"
path="/purchaseOrder/shipTo/name" multi_occurrence="NO"/>
</table>

The parameters of interest in the mapping are: //column/@path and //column/@type

The comparative XML indexes in DB2 Viper are:

create index order_name on purchaseorder (info) generate key using
xmlpattern '/purchaseOrder/ shipTo/name' as SQL VARCHAR(128);

The //column/@path value in the DAD is the same as the xmlpattern in the XML index and the //column/@type in the DAD is the same as the SQL type.

Also of interest is the root ID that is passed to the enable_column call in DB2 Version 8.x when an XML column is enabled with the DAD file.

dxxadm enable_column migrate purchaseorder info
"/home2/hardeep/migrate/po.dad" -r poid

This root ID identifies the name of the single primary key in the table that is added to the side tables and is used to tie the side tables to the table with the XML column. This value of the root ID is unique across the XML column. This root ID corresponds to the path value in the XML document that uniquely defines that document in the XML column.

So when you create the XML indexes for DB2 Viper, you can create a unique index on the path that defined the root ID (primary key), in DB2 Version 8.x, if it exists in the XML document.

create unique index order_key on purchaseorder (info) generate key using
xmlpattern '/purchaseOrder/@poid' as sql double;

Migrate mapping files for shredding XML to relational

If your application needs to shred incoming XML data to relational tables, and you feel that this need still exists, then you need to replace your existing DAD based mapping with annotated XML schema based mappings.

Note: In both type of mappings the relational tables must pre-exist.

Since mapping information about relationships between the XML document and the relational schema is stored in the DAD files, these DAD files can be used to understand the annotations required for the XML schemas. For a tool that can automate the process of converting your DAD files to an annotated XML schema, refer to From DAD to annotated XML schema decomposition (developerWorks, April 2006).

Once the XML schemas have been annotated, they can be registered to the XSR and enabled for decomposition.

db2 register xmlschema http://migrate.db2 from
/home2/hardeep/migrate/customer.xsd as migrate.customer
db2 complete xmlschema migrate.customer ENABLE DECOMPOSITION

Database migration utility

Example code for a migration utility is provided with this article. This utility generates scripts to help in the process of data migration. The generated scripts help you to do the following:

• Export the data.

• Alter the XML columns.

• Import the data.

• Register the DTD/XML schemas.

• Create XML indexes.

• Drop the side tables.

Migrate command

Migrate -p[path] -d[database] -s[schema] -t[able] -c[olumn] -[b[ackup]]

• path: The destination directory for the exported files.
• database: The name of the database.
• schema: The name of the schema.
• table:The name of the table to be migrated.
• column:The name of the XML columns in the table.

• backup: If the table needs to be backed up to a backup schema before migrating.

The utility writes the script file migrate_script.db2 to the current directory.

Compile the java code

To compile the java code, complete the following steps:

1. Create a directory /temp/migrate.
2. Copy the MigrateUtility_code.zip (Found in the Download section of this article.) to your temp directory. Extract the Migrate.java and XMLParse.java to the /temp/samples directory.
3. Compile the java files.

   On Microsoft Windows: Open a db2 command window.

   SET CLASSPATH= .;%DB2PATH%\java\db2java.zip; %DB2PATH%\java\db2jcc.jar;%DB2PATH%\java\db2jcc_license_cu.jar; "%DB2PATH%\java\jdk\bin\javac.exe" -d . *.java

   
   

   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

   
   

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

4. Run the utility.

   "%DB2PATH%\java\jdk\jre\bin\java.exe" Migrate -p /temp/migrate d migrate -s hardeep -t purchaseorder -c info -b xmlextenderdata

   
   

The following is a sample of a generated script:

CONNECT TO MIGRATE;
-- Scripts to backup data to schema- xmlextenderdata
CREATE SCHEMA xmlextenderdata;
CREATE TABLE "xmlextenderdata"."PURCHASEORDER"( "POID" BIGINT NOT NULL,
"STATUS" VARCHAR(10) NOT NULL WITH DEFAULT 'New' ,
"INFO" "DB2XML"."XMLCLOB" NOT LOGGED NOT COMPACT NOT NULL)
IN "USERSPACE1";
DECLARE bcurs_purchaseorder CURSOR FOR SELECT * FROM hardeep.purchaseorder;
LOAD FROM bcurs_purchaseorder OF cursor INSERT INTO xmlextenderdata.purchaseorder;

-- Scripts to migrate data
export to /temp/migrate purchaseorder/ purchaseorder.del of del lobs to
/temp/migrate purchaseorder/lobs modified by LOBSINFILE SELECT * from purchaseorder;

drop table purchaseorder;
CREATE TABLE purchaseorder ( "POID" BIGINT NOT NULL ,
"STATUS" VARCHAR(10) NOT NULL WITH DEFAULT 'New' ,
"INFO" XML NOT NULL ) IN "USERSPACE1";
import from /temp/migrate/purchaseorder/purchaseorder.del of del lobs
from /temp/migrate/purchaseorder/lobs modified by XMLCHAR INSERT INTO purchaseorder;
ALTER TABLE purchaseorder ADD PRIMARY KEY ("POID");

-- Scripts to restore data from backup schema- xmlextenderdata schema
CONNECT TO MIGRATE;
drop table purchaseorder;
CREATE TABLE "HARDEEP "."PURCHASEORDER" ( "POID" BIGINT NOT NULL ,
"STATUS" VARCHAR(10) NOT NULL WITH DEFAULT 'New' ,
"INFO" "DB2XML"."XMLCLOB" NOT LOGGED NOT COMPACT NOT NULL ) IN "USERSPACE1";
ALTER TABLE "HARDEEP "."PURCHASEORDER" ADD PRIMARY KEY ("POID");
DECLARE rcurs_purchaseorder CURSOR FOR SELECT * FROM xmlextenderdata.purchaseorder;
LOAD FROM rcurs_purchaseorder OF cursor INSERT INTO hardeep.purchaseorder;

Back to top

Migrate DB2 queries

DB2 Viper can query both relational and XML data simultaneously using a mix of SQL/XML and XQuery.

As some of the original reasons for shredding the XML documents (fast search using relational columns) are no longer valid, the relational schema needed to store the XML data can become much simpler. As a consequence, a part of the application code that dealt with manipulating the XML data can now be replaced by a DB2 query that could provide the same functionality inside the database server using SQL/XML and XQuery. The impact of this on applications built on top of XML data is phenomenal:

• Business logic embedded in SQL queries becomes independent of the application environment. Also, business logic written using SQL/XML or XQueries can be saved in views or stored procedures for reuse.
• Middle tiers become leaner and easier to manage.
• Simplifies service oriented architecture (SOA) with the database becoming a data and service provider.
• Data is moved out of the database only after it has been fully massaged reducing traffic between database and application.

Some of the application queries that are affected are shown in Table 5:

Inserting XML data to DB2

Intact XML stored in a single columns

If XML data was stored intact in the existing application, then there should not be a major impact on the application code and the DB2 queries dealing with the XML data insertion.

DB2 Version 8.x. Storing data into a CLOB, VARCHAR, XMLCLOB, or XMLVARCHAR:

insert into customer (info) values (?)

DB2 Viper. Column of type XML:

The code would remain the same unless you needed to parse or validate the data.

insert into customer (info) values (?)

In case you need to validate the data against a registered XML schema, you would add the XMLVALIDATE to the insert statement.

insert into customer (info) values (XMLValidate(?))

Note: In XML Extender, validation can be stated implicitly in the DAD mapping for the side tables.

If you need to override the default parsing of the XML document, you can specify the parsing options using the XMLPARSE in the insert statement.

insert into customer (info) values (XMLPARSE(DOCUMENT
cast(? as BLOB(2M)) preserve whitespace ))

Note: The XML data that was stored intact could also have been shredded and mapped to relational tables for querying purposes. This could have been accomplished using DAD mapping for XML column side tables, or it could have been done in the application code. Since, in DB2 Viper, you can directly index and search into nodes inside the XML document, there is no need to shred the XML to relational for the sole purpose of querying. Application code dealing with the shredding could be redundant.

XML shredded to relational tables

For XML documents where the need to shred exists, the queries and application code dealing with the data insertion and shredding changes slightly.

DB2 Version 8.x. XML documents shred using XML Extender shred functions dxxInsertXML and dxxShredXML.

Call dxxShredXML (?, ?,?,? );
Call dxxInsertXML(customerdad, ?,?,? );

DB2 Viper. XML documents shred using annotated XML schema mapping.

Call xdbDecompXML (NULL,migrate.customer,?,?,1,NULL,NULL,NULL)

If the document is shred to a single table, you can use XMLTABLE to do the shred.

Insert into product(pid,info) select x.pid, X.doc from XMLTABLE
('$info//product' passing xmlparse( DOCUMENT cast(? as XML) ) AS "info"
COLUMNS PID VARCHAR(12) PATH '@pid', doc XML by ref PATH '/') as x

If the shredding is being done inside the application using a DOM, you don't need to change anything unless you want to migrate the shredding to annotated XML schemas or use XMLTABLE.

Update XML documents stored in DB2

DB2 Version 8.x XML Extender provides a UDF to allow partial updates to XML documents in DB2. Update takes a location path to specify the element or attribute that is to be updated. It returns a data type similar to the column being updated. The content of every element or attribute in the XML document that matches the location path is updated with the supplied value.

Update purchaseorder set status='shipped', info=db2xml.update(
info,'/purchaseOrder/@status','shipped') where poid=2001;

Since the Update UDF handles one location path at a time, in order to do more extensive changes to the document, it needs to be retrieved to the client application, modified, and then inserted back to the database using a SQL update statement.

Note: The update trigger on XML Extender columns also updates the corresponding side tables, in order to keep the data in the XML column in sync with the data in the side tables.

DB2 Viper does not have any functions to do subelement updates to an XML document inside the database. The reason for this is that the XQuery standards have not as yet defined the syntax for updates, although there are proposals under way. DB2 did not want to introduce short term proprietary functions into the language for this purpose.

A workaround to this problem is a Java stored procedure that can be found in the article Partial updates to XML documents in DB2 Viper (developerWorks, May 2006). This stored procedure can do partial updates to XML documents inside DB2. The update stored procedure is also capable of deleting, replacing, and appending nodes in the XML document.

Note: Under the covers, the stored procedure does a full document update.

Call DB2XMLFUNCTIONS.XMLUPDATE (
'<updates><update action="replace" col="1"
path="/purchaseOrder/@status">shipped </update></updates>',
'Select info from purchaseorder where poid=2001',
'update purchaseorder set info=? where poid=2001',?,?);

Note: Since the source code is also published, the stored procedure can be used as is or as a model for writing your own Java-based stored procedures to manipulate XML data inside DB2.

Query and publish XML documents stored in DB2

Application code dealing with querying and publishing is the one that is affected the most. For XML data stored in XML Extender columns, the SQL queries change completely, since they involve the side tables, which are no longer maintained.

The following SQL queries two tables to return the details for products that have thumbnail images:

 Select SQLPRODUCT.DETAILS, from SQLIMAGES,SQLPRODUCT
 where SQLPRODUCT.PID=SQLIMAGES.PID and SQLIMAGES.TYPE='thumbnail'

This could be replaced with a single query over a single column:

 for $product in db2-fn:xmlcolumn("XMLPRODUCT.DESCRIPTION")/product
 where $product/description/images/image/@type="thumbnail"
 return $product/description/details/text()

Extract and query XML data inside the XML document

DB2 XML Extender extract UDFs provide a number of scalar and table function to extract data from inside an XML document. Any SQL statement involving extraction of data from an XML document using the DB2 extract UDFs also have to be replaced with equivalent calls to XMLEXIST, XMLQUERY, or XMLTABLE.

select db2xml.extractVarchar(info,'/customerinfo/name') from xmlcustomer

Can be replace with:

Select xmlcast( XMLQuery('$cinfo/customerinfo/name'
passing info as "cinfo") as varchar(32)) from
xmlcustomer

select info from customers where db2xml.extractvarchar(info,'/customerinfo/name')=?

Can be replaced with:

select info from xmlcustomer where XMLExists('
$cinfo/customerinfo[name=$name]' passing info as "cinfo",
cast(? as varchar(32)) as "name")

DB2 XML Extenders provide ExtractCLOB () and ExtractCLOBs () UDFs to extract fragments of an XML document.

select db2xml.extractclob(info,'/customerinfo/addr') as RESULT from
customer where cid = ?

Can be replaced with:

select x.add from xmlcustomer, XMLTABLE ('$info//customerinfo' passing
info AS "info" COLUMNS add XML by ref PATH 'addr') as x where cid = ?

Transform and publish XML data

Besides the extraction UDF that can be used to get to subdocument information, DB2 XML extenders also provide an XSLT UDF to transform XML documents inside the database.

The two XSL UDFs defined in DB2 XML Extenders are XSLTransformToClob() and XSLTransformToFile().

SELECT XSLTransformToClob( info, /temp/xslfilename.xsl) FROM xslcustomers
SELECT XSLTransformToClob( info, ?) FROM xslcustomers
Pass the XSL as a CLOB at runtime

Although DB2 Viper has the XSL UDF as a part of the XML extender support, there is not any built-in function for XSL transformation (This means that you need to enable the database for XML extenders in order to use the XSLT UDFs.). Since XQuery has a lot of overlapping functionality with XSL, you can use XQuery in conjunction with the SQL/XML functions to transform and publish XML documents.

The following query illustrates the use of XQuery to query and transform natively stored XML documents. The XQuery constructs a new element called "customer", and adds the name, address, and phone nodes from each XML document stored in the XMLCUSTOMER table matching the criteria (@country=United States) to it. These customer elements are then returned inside a single root node "customerlist."

Transform and publish relational data as XML

DB2 XML Extender DAD files and stored procedures for collections provide bidirectional mapping. The same mapping can be used for shredding and publishing. Unfortunately, the annotated XML schema functionality does not provide any publishing capabilities. So the only way to publish is to use SQL/XML function.

Back to top

Considerations

This section contains some of the things you need to consider while migrating. These could be limitations or best practices that could help you in making your decisions when you migrate your XML application. For a comprehensive list, refer to the documentation for DB2 Viper ( Leveraging native XML support ) and ISV success with DB2 Viper: Prepare your applications, routines, and scripts for migration to DB2 Viper (developerWorks, March 2006).

Restriction on XML columns

The following are restrictions on XML columns:

• An XML column cannot be included as columns of keys, including primary, foreign, and unique keys, dimension keys of multi-dimensional clustering (MDC) tables, sequence keys of range-clustered tables, and distribution or partitioning keys.
• An XML column cannot be part of any index except an index over XML data.
• An XML column cannot have a default value specified by the WITH DEFAULT clause; if the column is nullable, the default for the column is NULL.
• An XML column cannot be used in a materialized query table.

• An XML column cannot be used in a range-clustered table.

• An XML column cannot be used in a MDC table.

• An XML column cannot be referenced in CHECK constraints.

• An XML column cannot be referenced in generated columns.

• An XML column cannot be referenced in triggers.

XML schemas

The following are considerations regarding XML schemas:

• Large schemas require sufficient space in the application heap. It is recommended that you enlarge the application heap as follows:

  db2 update db cfg using applheapsz 1024

  
  
• Since no XML schema is associated with an XML column, it is technically possible to insert XML documents from diverse schemas in a single column (schema chaos).

Indexes

An XML index can be used for a query predicate only if:

• The index contains the query predicate. For example, if the index is equally or less restrictive than the predicate. This includes namespaces.
• The query predicate matches the index data type.
• /text() is used consistently in predicates and index definitions.

Wherever possible, use fully specified XPaths. Avoid wildcards (*, //), avoid /text().

• /customerinfo/phone instead of //phone
• /customerinfo/addr/state instead of /customerinfo/*/state
• /customerinfo/name instead of /customerinfo/name/text()

Annotated XML schemas

• Annotations do not contribute to the validation of the instance document.
• Most of the XML schema components can have annotations.
• Decomposition only looks at annotations on elements declaration, attribute declaration, and global annotations that belong to a predefined namespace.
• XML schemas with recursion cannot be used for decomposition. It fails during registration.

• Referential integrity in the database is not supported.

• Validation is optional and it might add defaults; changing the document, possibly changing the result of the shredding.

• Annotated schemas cannot be used for publishing purposes.

XMLPARSE

The biggest issue to avoid is code page conversions that could make the input document encoding different from its encoding declaration, or that could introduce substitution characters (if the target code page cannot represent all input characters).

• The use of implicit XMLPARSE is highly recommended. The server expects an XML type, and therefore does not do any code page conversions.

  insert into customer (cid, info) values (?,?)

  
  
• The use of explicit XMLPARSE causes the server to expect a character or binary type (based on the cast clause). The use of XMLPARSE, and the cast of a CLOB, cause the document to be codepage converted to the database codepage on the server. This is typically not needed or desirable.

  insert into customer (cid, info) values (?,XMLPARSE(DOCUMENT cast(? as CLOB(2M)) preserve whitespace ))

  
  
• If XMLPARSE needs to be used to change the default parsing options, it is recommended to cast the data to a binary type instead of a character type. This avoids codepage conversion issues. Since you are reading the input XML file in streams of raw bytes, there are no codepage conversions on the server or on the Java side. The cast to a BLOB prevents the document to be codepage converted on the server.

insert into customer (cid, info) values (?,XMLPARSE(DOCUMENT
cast(? as BLOB(2M)) preserve whitespace ))

XMLVALIDATE

The following are considerations regarding XMLVALIDATE:

• Validation is expensive.

• Validation against DTDs is not supported.

XMLQUERY

The following are considerations regarding XMLQUERY:

• While it is not possible to bind runtime predicates in a pure XQuery, it is indirectly possible to apply runtime predicates through the SQL/XML functions or predicates such as XMLQUERY, XMLTABLE, and XMLEXISTS.
• For an XQuery comparison, if the base type is known, no implicit casting is done and illegal comparisons are not allowed.

XMLEXISTS

The following are considerations regarding XMLEXISTS:

• Use XMLEXISTS in the where clause rather than XMLQUERY.
• XMLEXISTS only returns false if the result of the query is an empty sequence (This means that XMLEXISTS(false) returns a true since the result of the query is not an empty sequence.). Therefore, the following query returns all the rows in the customer table because when the customerinfo/name is not Hardeep the query returns a single sequence with a value false, not an empty sequence.

  select c.info from xmlcustomer c where xmlexists(' $i/customerinfo/name = "Hardeep" ' passing c.info as "i")

  
  
• Use brackets [] in the XMLEXISTS predicates. The previous query would have worked if you had put the name check inside the square brackets, since now for all customerinfo, where name is not Hardeep, an empty sequence is returned from the query and XMLEXISTS returns false if the result is an empty sequence.

  select c.info from xmlcustomer c where xmlexists(' $i/customerinfo[name = "Hardeep"] ' passing c.info as "i")

  
  
• Combine multiple XML predicates in a single XMLEXISTS. The following two queries are not the same:

  select * from customer where xmlexists(' $c/customerinfo[name = Hardeep]) and xmlexists('$c/customerinfo[phone = 555-555-555] '.) select * from customer where xmlexists(' $c/customerinfo[name=Hardeep and phone=555-555-555]...)

  
  

  If some document contains two customerinfo elements and one of them has a name=hardeep and phone=111-111-111, while the other has a name=Marja and a phone=555-555-555, then the first query returns this document while the second query does not.

Back to top

Acknowledgement

Thanks to Matthias Nicola, Henrik Loeser, Ying Chen, and Amyris Rada for their help with this article.

Back to top

Download

Information about download methods

Resources

Learn

• "XML application migration from DB2 8.x to DB2 Viper, Part 1: Partial updates to XML documents in DB2 Viper" (developerWorks, May 2006): Learn a method for performing partial updates to XML documents stored natively in DB2 Viper, using a stored procedure that's included as a download.
  
  
• DB2 XML Extender: Visit the DB2 XML Extender Web site.
  
  
• "ISV success with DB2 Viper: Prepare your applications, routines, and scripts for migration to DB2 Viper" (developerWorks, March 2006): Learn about DB2 Viper product changes that are most significant to ISVs.
  
  
• "Firing Up The Hybrid Engine": Learn about the details of the native XML capibilities of DB2.
  
  
• Visit the IBM DB2 Viper Release Candidate 1 for Linux, UNIX, and Windows Information Center.
  
  
• "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.
  
  
• " Query DB2 XML data with XQuery" (developerWorks, April 2006): Learn how to query data stored in XML columns using XQuery.
  
  
• " From DAD to annotated XML schema decomposition" (developerWorks, April 2006): Learn about the benefits of migration to annotated XML schema decomposition at various levels, such as functionality, usability, and performance.
  
  
• "Default mapping for annotated XML schema" (developerWorks, April 2006): Find a good starting platform for not only trying out the new function, but also further enhancing the mapping in a given XML schema.
  
  
• "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.
  
  
• DB2's XML support: Learn more about DB2's XML support.
  
  
• 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

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• XML features introduced in DB2 UDB Viper
• Impact of DB2 Viper XML support on XML based applications
• Migrating an existing XML database from DB2 Version 8.x to DB2 Viper
• Migrate DB2 queries
• Considerations
• Acknowledgement
• Download
• 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