Migration Experience "XML in CHAR-Format etc. in DB2 for z/OS" to pure XML in

Peter Suhner

Migration Experience "XML in CHAR-Format etc. in DB2 for z/OS" to pure XML in

Norbert,

I've got some insight (unfortunately only a little bit, so I can't provide too much detail - sorry!) on one of our DB2 LUW applications which is currently converting to XML.

This is basically a Java OLTP application which also provides some reporting capability. To better support the reporting part, they had duplicated and reshaped the OLTP data into XML format and shredded it into CLOBs and VARCHARs. While the original OLTP data has around 50GB, the reporting data has grown exponentially over time and reached 200GB. So they lately have decided to migrate that part of the data and store it in native XML format.

They had to write a conversion utility for the migration, meaning that this was not possible by simply using SQL.

Results:
- Amount of reporting data down from around 200GB to about 12GB
- Runtime for their more complex reports from 5 hours down to 30 minutes
- Of course, 12GB gives you faster reorgs and backups than 200GB. And less storage cost.
- I don't know whether you can take this as a general rule as this is the only real life experience I have with such a conversion
- Maybe other listers can endorse or object based on their experience?

Challenges/Problems:
- Building knowledge of XML format and XQUERY language
- I've got the impression that the learning curve isn't too steep for experienced Java developers
- But it is defnitely much more of a problem with department people (end users who are used to using SQL for their ad-hoc reports)
- Bug in DB2 LUW v9.1 (at least I'd call it a bug - haven't opened a PMR with IBM yet)
- Reports of a certain size (in number of columns) somehow didn't display the real data for some columns, but only NULLs instead
- Reducing the number of columns in the select clause suddenly showed the real data again
- This was a complex query containing both, SQL and XQUERY language, plus many CASE expressions and the like
- The problem persisted even with the latest available FixPack (FP9)
- Tests with DB2 9.7 FP3a showed correct results
- So I'm currently very busy with upgrading all of this to DB2 9.7...

Agreed, that's all for the LUW platform and I have no practical experience with the XML engine on z/OS, but I expect them to be somehow similar.


Best regards,
Peter

_______________________
Peter Suhner
[login to unmask email]


Please consider the environment instead of printing this message




> Date: Tue, 11 Jan 2011 04:21:57 -0500
> From: [login to unmask email]
> Subject: [DB2-L] Migration Experience "XML in CHAR-Format etc. in DB2 for z/OS" to pure XML in DB2
> To: [login to unmask email]
>
> Hello,
>
> We have installed DB2 for z/OS V9 NFM nearly one year. We know that many of our DB2 application projects have to handle XML structures since years. Some of that projects also have their "XML-Data" already in DB2 in CLOBs or VARCHAR columns. Now we want to build the first prototyp with one of this projects (that already has XML-Data in DB2), to get real experience with the new pure XML functionalities and to see the advantages in DB2 ( and also the possible pitfalls).
>
> How has already experiences in that area or is planning that "kind of XML-migration"?
>
>
> Norbert Wolf
>
> Datev eG
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
> * If you are going to attend only one conference this year, this is it! *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv