DB2 Function LISTAGG failing with SQL0440N
Posted on 2014-12-19
We have several DB2 Servers and multiple instances all running DB2 LUW (windows) DB2 V9.7 Fixpack 9
a call to DB2 Function LISTAGG is working against some of the databases - and failing in others.
Here is the SQL:
SELECT COL1, COL2, LISTAGG(AGT_DESGNT_CD,',') WITHIN GROUP (ORDER BY AGT_DESGNT_CD ) AS AGT_DESGN_DESC
FROM myschema. mytable
GROUP BY COL1, COL2
ORDER BY COL1, COL2
Here is the error:
Error during Prepare
42884(-440)[IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "LISTAGG" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
I cannot find a pattern as to why it is failing in some databases and not in others. On one Server, I have 2 Databases - both in the same instance - and it is failing in one and not the other.