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

Daniel Luksetich

Migration Experience "XML in CHAR-Format etc. in DB2 for z/OS" to pure XML in
I had an issue recently where XML was stored in a varchar column.

With the help of people here on the listserv the migration/conversion was
as simple as this:

select xmlparse(document rqst_xml preserve whitespace)
from schema.table_that_contains_rqst_xml_column
;

On Tue, 11 Jan 2011 22:36:44 +0100, Peter Suhner
<[login to unmask email]>
wrote:
> 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

_____________________________________________________________________
* 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