how to resolve db2 listagg exceed max length?

Question

I'm using db2(v10.5.0.5) and here's my sql:

SELECT id, listagg(sql, '') 
FROM (
SELECT column1 || column2 || column3 || column4 || column5 ||
column6 || COALESCE(column7, 0) || column8 || COALESCE(column9, 0) || 
COALESCE(column10, 0) AS id,
column1 || column2 || column3 || column4 || column5 ||
column6 AS name, sql 
FROM t_test_data  
) t1 WHERE id IS NOT NULL GROUP BY id HAVING id 
= 'id_test';

But I've got an error:

The length resulting from "LISTAGG" is greater than "32672".. SQLCODE=-137, SQLSTATE=54006

Any way to get rid of this limiation? ('sql' is defined with clob.)

Answer 1

Use XMLAGG instead.
It can work with LOBs.
You don't need SUBSTR & '|' || as in the example below probably, since you don't you token delimiter according to your listagg(ATTR_VALUE, '').

SELECT 
LENGTH 
(
  -- Just to elimitate the 1-st token delimiter, if any
  SUBSTR 
  (
    XMLSERIALIZE 
    (
      XMLQUERY ('$D/text()' PASSING XMLAGG (XMLELEMENT (NAME "a", '|' || TEXT)) as "D")
      AS CLOB (2M)
    )
    , 2
  )
)
FROM SYSCAT.VIEWS

|1      |
|-------|
|610,275|