Comentários (12)

1 csn7446 comentou às Link permanente

Hi Serge,

I find it somewhat amusing to find the information on LISTAGG deriving the result size from the first argument when greater than 4000 here and not in the documentation. Actually not really. The 4000 limit has bit us a few times and we reverted to using the XML functions, which in turn have the odd ability to under certain still unknown circumstances crash the instance and mark the DB bad, we have an open PMR on this one. Now, how hard can it be to document this properly. Good post though and nice function, especially now when we know how it works.
Best regards

2 Yonghang comentou às Link permanente

Serge, do you think near future version, say, 2 years which is more predictable, of LISTAGG will be compatible with "partition by" and "window aggregation group" ?

3 SergeRielau comentou às Link permanente


I'm re-reading teh docs:
And if you take a look at Table 1.
Result data type of LISTAGG function
Data type of string-expression Data type of result
CHAR(n) or VARCHAR(n) VARCHAR( MAX(4000, n))
It seems to describe the issue you are noting.
Perhaps I'm not clear on what it is that you find lacking.
Generally if you find issues with the docs please, please, please use the feedback button. There is no need for a PMR, it takes only a minute and the email goes straight to our doc people.
We're customer driven :-)
Naturally I cannot make any statements in a forum such as this about commitments and/or time frames.
Admittedly I have a long list of things on my wishlist that I prefer to do first.
But then, I'm not a customer....

4 Yonghang comentou às Link permanente

sounds the limit of LISTAGG result is 32664 with casting. Not sure whether it's good enough for Sven.

5 Yonghang comentou às Link permanente

the max length of listagg depends on max(pagesize_system_temp_tbsp).

32k, 32664,
16k, 16280,

6 csn7446 comentou às Link permanente

Ok Serge, I see your point now but I have like 10 developers of whom some are quite bright and non of them could derive that out of the docs and I still think that that little line in your post "If the first argument is bigger however DB2 will take it's derived size to derive the size of the result." should have been in the docs with the accompaning example.
That would have made life so much simpler, and the PMR was not for the docs, it was for the XML function combination we use for our really big departments :-) it should have said "on that one", see how easy it is to be unclear.

Well 32K would certanly have been enough in some situations but not all, now the developers reverted to the XML functions when they ran in to the default 4000 limit and due to the bad behavior of those functions at the time, we would like to limit the use of them as much as possible. And in the end LISTAGG is a much simpler and nicer way of doing this.

7 SergeRielau comentou às Link permanente


I'm sorry you got onto a wild goose chase there.
Note that I did not misread your PMR comment.
I simply meant to make a general comment that people should use the feedback button liberally since it has a very low hurdle.
Yonghang is correct that there are other limits that kick in.
That is that GROUPing requires the means to SORT and SORTs require the means to TEMP (if a SORT spilles).
And DB2's maximal pagesize is 32K.
So grand sum of all columns in the select list, order by etc has to fit into 32K.
If it wouldn't be for that restriction we would have simply maxed out VARCHAR for LISTAGG.
Note that explicitly explaining these downstream limitation each function is impractical. For one it would clutter the docs and more importantly it would make it very hard to change anything.

8 SergeRielau comentou às Link permanente


The docs are being updated to the below. Let me know if you still think that is insufficient.
The result data type of LISTAGG is based on the data type of string-expression:
* If the data type of string-expression is CHAR(n) or VARCHAR(n),
the data type of result is VARCHAR( MAX(4000, n))
* If the data type of string-expression is GRAPHIC(n or VARGRAPHIC(n),
the data type of result is VARGRAPHIC( MAX(2000, n))
The result data type can exceed VARCHAR(4000) or VARGRAPHIC(2000) if a derived size is used to determine the size of the result, to a maximum of 32 KB if WITHIN GROUP is specified. The following example successfully yields a return data type of VARCHAR(10000):
If the actual length of the aggregated result string exceeds the maximum for the result data type, an error is returned (SQLSTATE 22001).
The result can be null. If the function is applied to an empty set or all of the string-expression values in the set are null values, the result is a null value.

9 csn7446 comentou às Link permanente

Hi Serge,

perfect! That's exactly what I requested. I believe this will make the docs much clearer and easier to understand.
Thanks for reconsidering.

10 SergeRielau comentou às Link permanente

Great, glad we could improve this.