Comments (6)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 rpillai commented Permalink

nice ! Keep it coming Serge !

2 patogorman commented Permalink

Great, Thanks Serge

3 Maksym commented Permalink

Sounds like a cool solution. But I think it only works for Unicode databases with XML support. For example, DB2 9.5 DPF would not support it. Do you have another cool solution for such case? I used recursive SQLs in the past, but it is mind-twisting.
And what would be the limit for your xml solution? I am guessing that the final string cannot be unlimited.

4 SergeRielau commented Permalink


DB 9.5 with DPF supports XML, but it would execute on the catalog node I think.
Knut Stolze I think wrote a developerWorks article eons ago that used scratchpads to implement aggregation in two stages.
The problem is that such a solution is somewhat fragile.
Recursion is the only other robust solution I can think of.
As for limits strings are limited to 32K at any rate. But XMLAGG does support LOBs, so you can reach quite high.
I'd guess that MB sized aggregates should be possible.

5 Evandro A Olivio commented Permalink

Excellent tip. That's what I was looking for. Thank you!

6 Stubacca commented Permalink

The XML functionsoultion does not work with DB2 9.5 multi-partitioned databases. It produces the an error - "pureXML data store features can be used only in a single-partition database.. SQLCODE=-1239, SQLSTATE=42997, DRIVER=3.64.106"