Next week in Vegas…

October 21, 2010

What happens in Vegas, stays in Vegas – or so they say. But, that’s not going to be quite true for IBM’s Information on Demand Conference on 24-28 October at the Mandalay Bay Hotel and Resort.

The IOD conference is packed with a broad range of technical sessions, business sessions, hands-on labs, and lots of networking opportunities. More than 160 customer speakers will present their first-hand experiences with IBM Software. For example, one customer will talk about their migration of an XML database application from Oracle to DB2 9.7 (session TLU-2928A). And then there is the Expo, where an impressive set of business partners shows their solution and services.

DB2 pureXML is going to be well represented at the conference, most notably with a set of hands-on labs for both DB2 on Linux, Unix, Windows and DB2 for z/OS. Some of the labs you can choose from include:

HOL 1468 – PureXML for DBAs: Setup and Storage for OLTP and Warehouse
HOL 1480 – Creating XML Solutions with DB2 pureXML
HOL 1152 – Learning pureXML in DB2 for z/OS with IBM Optim Development Studio
HOL 1170 – Learning SQL/XML with CLP and SPUFI
HOL1391  – New Features and Enhancements in IBM DB2 10 for z/OS
HOL1409 – Getting Started with IBM DB2 for z/OS pureXML for DBAs and Developers

Whether you attend presentations, roam around in the Expo, or gain practical skills in the hands-on labs, I’m sure you’ll take a home a wealth of valuable information. So, a lot of what happens in Vegas next week is not going to stay in Vegas…

You’ll most likely be able to meet some of your very favorite XML people at Information on Demand Conference on 24-28 October in Las Vegas, e.g.,:
Lee Ackerman, Jane Man, Matthias Nicola, Bryan Patterson, Guogen Zhang
in meet the expert sessions, in presentations (there is a session on migrating an Oracle XML application to DB2) or in Hands on Labs such as:
HOL 1468 – PureXML for Database Professionals: Setup and Storage for OLTP and Warehouse
HOL 1480 – Creating XML Solutions with DB2 pureXML
HOL1391  – New Features and Enhancements in IBM DB2 10 for z/OS
HOL1409 – Getting Started with IBM DB2 for z/OS pureXML for DBAs and Developers
HOL 1391 – Learning Exciting new Features and Enhancements in DB2 10 for z/OS
HOL 1152 – Learning pureXML in DB2 for z/OS with IBM Optim Development Studio
HOL 1170 – Learning SQL/XML with CLP and SPUFI
The Hands on Lab (HOL) numbers may change.
You can register for IoD here

On Tuesday, October 12, at 12noon US Eastern Time (9am US Pacific Time) there is an interesting webcast and discussion on using Rational Software Architect (RSA) and Rational Application Developer (RDA). The speakers are Lee Ackerman and Jean-Louis Marechaux, two high caliber experts in IBM’s Information Management and Rational division, respectively.

With the increased prevalence of XML in industry solutions, SOA and Web 2.0, this webcast will look at how you can use Rational Software Architect/Rational Application Developer along with DB2 pureXML to work with and manage XML data. The session will briefly introduce the pureXML capabilities of DB2 and discuss how you can generally work with XML in software solutions. The webcast will then explain how Rational Software Architect/Rational Application Developer can help you model, generate, query and code XML-based software solutions. For example, you will learn about the XML-related editors and wizards in RSA and RDA, among many other things. RSA and RDA offer support for the manipulation of XML documents, XML Schemas, DTDs, XSLT, XQuery, SQL/XML, web services and WSDL, SOAP, and other SOA technologies.

To attend the webcast, please call into: 1-877-848-7046 or 1-636-651-0036 with passcode 4258904. Also, please  contact Susan Malaika to be gain access to the web chat where you can ask questions during the session and download the slides.

More information on Rational Software Architect and Rational Application Developer can be found here:

Please call into: 1-877-848-7046 or 636-651-0036 – passcode: 4258904
And please also join this Web chat When you enter the Web chat, you can use the settings option to change your name from anonymous.
The charts will be made available in the Web chat room.

Prior to  the emergence of native XML database technology, many XML applications have stored their XML documents in files systems, in LOB columns (large objects) in a database, or they have shredded the XML to relational tables.

A common question is how to migrate XML documents from a LOB column to an XML column. The benefit of an XML column is that the XML is stored natively, i.e. in a parsed format, so it becomes possible t0 index, query, and update the XML efficiently. Migrating XML from LOB to XML columns is easy. Let’s take a look at how you can do this in DB2 for Linux, UNIX, and Windows:

There are several different options, at least three:

  1. You can export the old table (with the LOB column) and then load into a new table where the LOB column has been replaced by an XML column. You can then drop the old table and rename the new table to the old table name.
  2. If you don’t want to export the table to the file system, you can use “load from cursor” to move the data directly from the old to the new table.
  3. You can add an XML column to the original table, copy the data from the LOB column to the XML column, and then drop the LOB column.

To illustrate these options, let’s first create a table with a LOB column and insert a couple of XML documents:

CREATE TABLE oldtable(id INT, doc CLOB);

INSERT INTO oldtable VALUES(1, ‘<url></url>&#8217;);
INSERT INTO oldtable VALUES(2, ‘<url></url>&#8217;);

Now we can try each of the three options.

1. Export + Load

The following commands export the “oldtable”, create a new table, and load the exported data into the new table:

CREATE TABLE newtable1(id INT, doc XML);
LOAD FROM old.del OF DEL INSERT INTO newtable1(id,doc);

Yes, it’s really that simple.

Be aware that CLOB or BLOB columns are very forgiving, i.e. they allow you to insert pretty much anything and they don’t check whether any inserted XML is well-formed or not. If your LOB column contains any XML documents that are not well-formed then those document will be rejected when you try to load them into the XML column. The LOAD command gives you a summary of how many rows (documents) were successfully loaded and how many were rejected. In our example the load summary looks like this:

Number of rows read         = 2
Number of rows skipped      = 0
Number of rows loaded       = 2
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 2

After your new table is loaded, you could drop the old table and rename the new one (subject to certain restrictions on the rename command):

DROP TABLE oldtable;
RENAME TABLE newtable TO oldtable;

2. Load from cursor

Loading from a cursor means that you read data from one table and load it into another table. Instead of exporting the “oldtable” you simply declare a cursor for it, which the LOAD command can use as input. In fact, the cursor declaration can contain any SQL query, and the result of that query then defines the input to the LOAD command:

CREATE TABLE newtable2(id INT, doc XML);
DECLARE cur1 CURSOR FOR SELECT id, doc FROM oldtable;
LOAD FROM cur1 OF CURSOR INSERT INTO newtable2(id,doc);

You could potentially also use an SQL insert statement such as “INSERT INTO newtable2 SELECT * FROM oldtable” to move data from one table to another. However, this requires all the data to be logged and you may run into a log full error. Also, the LOAD utility can write data to a table faster than an insert statement can.

Based on available system resources the LOAD utility automatically chooses an appropriate degree of parallelism and other tuning options. You can sometimes improve LOAD performance (and the degree of LOAD parallelism) if you increase DB2’s utility heap (util_heap_sz). For very large LOAD operations you may also want to review the general tuning guidelines for LOAD utility.

3. Add XML column, drop LOB column

You can use the following commands to add an XML column to the original table, copy the data from the LOB column to the XML column, and then drop the LOB column:

UPDATE oldtable SET xmldoc = doc;

The potential problem here is that the UPDATE statement is a large transaction that writes a lot of data that needs to be logged. You might run out of log space.  To avoid this problem, you could:
(a) use batch-wise updates, i.e. update 1000 rows, then commit, then update the next 1000 rows, and so on.
(b) use ” alter table oldtable activate not logged initially;” to temporarily disable logging for the table. (Note that this statement has effect only for a single transaction, so you must issue it before to the UPDATE statement and in the same transaction. For this to work in the DB2 Command Line Processor, autocommit must be disabled, e.g. “db2 +c -t”)

General migration considerations

No matter whether you create and populate tables with XML columns from scratch or migrate XML documents from LOB to XML columns, you always have a set of physical design choices for your XML column and table. These include the page size (large pages tend to be better for XML performance), XML inlining, using a separate table space or buffer pool for the XML data, and so on. These decisions depend on the nature of your applications that use your database. When in doubt, keep it simple. However, it is worthwhile to point out that LOB columns do not use any buffer pool while the XML columns do. Hence, the migration from LOB to XML column means that more data will be buffered than before. Hence, you may want to increase the existing buffer pool for the table, if you can.