IBM Support

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

Troubleshooting


Problem

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.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - Internal Routines","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21588635

Page Feedback