Function LISTAGG fails with SQL0440N after upgrade to DB2 V9.7 Fixpack 4 or higher

Technote (troubleshooting)


Problem(Abstract)

After upgrading to DB2 V9.7 FP4 or higher, followed by db2updv97 on database. Attempts to run LISTAGG function may result in error SQL0440N.

Cause

$ 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.

Related information

LISTAGG: New in DB2 v9.7 Fixpack 4
LISTAGG aggregate function
db2updv97
DEACTIVATE DATABASE command
ACTIVATE DATABASE command
db2stop
db2start

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Application Programming - DB2 Built-in functions/Built-in UDFs

Software version:

9.7

Operating system(s):

AIX, HP-UX, Linux, Solaris

Reference #:

1588635

Modified date:

2014-02-20

Translate my page

Machine Translation

Content navigation