LISTAGG issues when it crosses 4000 chars

Nanthakumar Yoganathan

LISTAGG issues when it crosses 4000 chars

Hi Team,

I am trying to retrieve colname from syscat.columns for a particular table. I am just collecting all the colnames in listagg function. Unfortunately it is giving exceptioin as it is crossing 4000 chars.  

I have tried casting as given in one of forum but still i am failing.

any alternate, i have used

listagg(cast(colname as varchar(255)), ',')  also

listagg(colname) 

Regards,

Nanthakumar Yoganathan

alain pary

RE: LISTAGG issues when it crosses 4000 chars
(in response to Nanthakumar Yoganathan)

hello , 

Do you try listagg(trim(colname))  to clean the blank ? 

regards 

 

Alain 

Roy Boxwell

LISTAGG issues when it crosses 4000 chars
(in response to Nanthakumar Yoganathan)
You get the same result on z



SELECT SUBSTR(A.DBNAME, 1, 8)

,SUBSTR(A.NAME, 1, 8 )

,SUBSTR(B.NAME, 1, 18)

,LISTAGG( SUBSTR(C.NAME, 1, 18)

, ' ') WITHIN GROUP (ORDER BY C.COLNO ASC)

FROM SYSIBM.SYSTABLESPACE A

,SYSIBM.SYSTABLES B

,SYSIBM.SYSCOLUMNS C

WHERE A.DBNAME = B.DBNAME

AND A.NAME = B.TSNAME

AND B.TYPE IN ('T','X')

AND B.CREATOR = 'MVNXTEST'

AND B.NAME = C.TBNAME

AND B.CREATOR = C.TBCREATOR

GROUP BY A.DBNAME, A.NAME, B.NAME

;

---------+---------+---------+---------+---------+---------+---------+---------+



---------+---------+---------+---------+---------+---------+---------+---------+

DSNT408I SQLCODE = -137, ERROR: THE LENGTH RESULTING FROM LISTAGG IS GREATER

THAN 4000

DSNT418I SQLSTATE = 54006 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXRSOR SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = -126 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'FFFFFF82' X'00000000' X'00000000' X'FFFFFFFF'

X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

---------+---------+---------+---------+---------+---------+---------+---------+



Using LISTAGG the result set cannot be longer than 4000 bytes... unless you CAST around it like this:



CAST( SUBSTR(C.NAME, 1, 18) AS VARCHAR(10000))



Then it works up to 10000 fine on z/OS



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Nanthakumar Yoganathan [mailto:[login to unmask email]
Sent: Monday, February 25, 2019 3:05 PM
To: [login to unmask email]
Subject: [DB2-L] - LISTAGG issues when it crosses 4000 chars



Hi Team,

I am trying to retrieve colname from syscat.columns for a particular table. I am just collecting all the colnames in listagg function. Unfortunately it is giving exceptioin as it is crossing 4000 chars.

I have tried casting as given in one of forum but still i am failing.

any alternate, i have used

listagg(cast(colname as varchar(255)), ',') also

listagg(colname)

Regards,

Nanthakumar Yoganathan



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

Peter Vanroose

RE: LISTAGG issues when it crosses 4000 chars
(in response to Roy Boxwell)

In Reply to Roy Boxwell:

DSNT408I SQLCODE = -137, ERROR: THE LENGTH RESULTING FROM LISTAGG IS GREATER THAN 4000
[...] Use CAST( SUBSTR(C.NAME, 1, 18) AS VARCHAR(10000))
then it works up to 10000 fine on z/OS

The idea of the default max. length of 4000 is such that it would fit on a single page for the default page size (4K).
If you want to set the max. length as large as possible, cast to VARCHAR(32700) instead of just 10000. That leaves a little bit extra space until the max size of a VARCHAR is reached, in case you still want to prepend/append some stuff to it ...

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Roy Boxwell

LISTAGG issues when it crosses 4000 chars
(in response to Peter Vanroose)
That’s a bit all on a wing and a prayer isn’t it? The other columns in the query would almost certainly push it over the edge...

Anyway for my purposes 4000 is more than enough!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Peter Vanroose [mailto:[login to unmask email]
Sent: Tuesday, February 26, 2019 4:15 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: LISTAGG issues when it crosses 4000 chars



In Reply to Roy Boxwell:

DSNT408I SQLCODE = -137, ERROR: THE LENGTH RESULTING FROM LISTAGG IS GREATER THAN 4000
[...] Use CAST( SUBSTR(C.NAME, 1, 18) AS VARCHAR(10000))
then it works up to 10000 fine on z/OS

The idea of the default max. length of 4000 is such that it would fit on a single page for the default page size (4K).
If you want to set the max. length as large as possible, cast to VARCHAR(32700) instead of just 10000. That leaves a little bit extra space until the max size of a VARCHAR is reached, in case you still want to prepend/append some stuff to it ...

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
https://www.abis.be/ https://www.abis.be/html/enDB2Calendar.html



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)