pls refer to Serge's blog
page for LISTAGG. here just some discussion about this function.
Sevn questioned that it has 4000 character limit.
From the DB2 info center copied from 10.1 beta 2 document and it's the same as 9.7 version.
The result data type of LISTAGG is based
on the data type of string-expression.
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))
|GRAPHIC(n or VARGRAPHIC(n)
||VARGRAPHIC( MAX(2000, n))
If the actual length of the aggregated result string
exceeds the maximum for the result data type, an error is returned
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.
It's wrong. Actually the limit in Sevn's case should be 3863 -- based only by my test.
The limit comes from the system temporary tablespaces available on your database. The limit varies when the max(pagesize_sys_temp_tbsp) changes,
This also reveals us something. The internal of listagg should use some RELATIONAL calculation than normal scratchpad way where the pagesize should not be a problem -- then the scratchpad size? or no, as a pointer may refer to GB buffer.
I don't like this version of LISTAGG and don't think the introducing new key word WITHIN GROUP is great idea. I had thought it would be OLAP enabled, say, be able to use the cause of "OVER(PARTITION....ORDER BY...ROW or RANGE...)" which has less limit and greater flexibility.
Highly suspected the internal is something like recursive CTE, looping to get the resultset. It may use VARCHAR as internal format than CLOB as CLOB has much bigger max size.
I used to use RECURSIVE CTE to do this calculation. with CLOB, the limit is 2G. This way has less efficiency(will make you crazy if you're dealing with something huge
) than XML but it's more relational, whatever
A mad query to make huge aggregated string:
with t as ( select t1.colname, rownumber()over() as rn from syscat.columns t1), t2 (colname, n ) as ( select cast(repeat(colname,50) as clob), 1 as n from t where rn = 1 union all select t2.colname || t.colname, n+1 from t2, t where rn = t2.n + 1) select length(colname) from t2 where n = (select max(n) from t2)