Comentários (6)

1 rpillai comentou às Link permanente

nice ! Keep it coming Serge !

2 patogorman comentou às Link permanente

Great, Thanks Serge

3 Maksym comentou às Link permanente

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 comentou às Link permanente


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 comentou às Link permanente

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

6 Stubacca comentou às Link permanente

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"