Function LISTAGG fails with SQL0440N after upgrade to DB2 V9.7 Fixpack 4 or higher
After upgrading to DB2 V9.7 FP4 or higher, followed by db2updv97 on database. Attempts to run LISTAGG function may result in error SQL0440N.
$ db2 "select APPL_ID, listagg(APPL_ID, ',') within group (order by APPL_ID) as tt from msdb2.lockevents group by appl_id"
SQL0440N No authorized routine named "LISTAGG" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
Diagnosing the problem
LISTAGG will fail after upgrading to fixpack 4 and running db2updv97, if the instance/database is not recycled after db2updv97. The in-build function cache is populated during database initialization. After db2updv97, recycle is necessary to refresh the cache. Hence, the current cache didn't contain the new LISTAGG function.
Resolving the problem
After running db2updv7 against the database, recycle the database by either using deactivate/activate database commands or db2stop/db2start to recycle the instance.
More support for:
DB2 for Linux, UNIX and Windows
Routines (SP & UDF) - Internal Routines
Software version: 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris
Reference #: 1588635
Modified date: 20 February 2014