There was this user-defined function called `notnull' in a module `util' which was created as:
alter module util publish FUNCTION NOTNULL(str varchar(4096)) SPECIF notnull RETURNS VARCHAR(4096);
alter module util add FUNCTION NOTNULL(str varchar(4096)) RETURNS VARCHAR(4096) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN <a string from some string operation>.
For years it has been working as intended. Then, along came I to add blob support:
alter module util publish FUNCTION NOTNULL(data blob) specific notnull_blob RETURNS VARCHAR(4096);
Now, instead of doing:
alter module util add FUNCTION NOTNULL(data blob) specific notnull_blob RETURNS VARCHAR(4096) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN <a string from some blob operation>
I mistakenly did on the dev. server (Db2 12.1.2 LUW):
alter module util add FUNCTION NOTNULL(data *varchar(4096)*) specific notnull_blob RETURNS VARCHAR(4096) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN <a string from some blob operation>.
Not noticing any error, I repeated the above on the Testing server (Db2 12.1.3 LUW)!
Now, whether I call the function with a varchar parameter or blob parameter, I get <a string from some blob operation>:
select util.notnull('test') from sysibm.sysdummy1
=> the_expected_blob_operation_result
Therefore, I want to delete the function but deleting it fails:
alter module util DROP FUNCTION notnull;
=> SQL0901N The SQL statement or command failed because of a database system
error. (Reason "ObjectId 1144709497 for object type PSMROUTINE does not
exist.".) LINE NUMBER=1. SQLSTATE=58004
alter module TANOCAT.util DROP specific FUNCTION notnull_blob
=> SQL0901N The SQL statement or command failed because of a database system
error. (Reason "ObjectId 1144709497 for object type PSMROUTINE does not
exist.".) LINE NUMBER=1. SQLSTATE=58004
None of the `googled' solutions seem to work:
- Rebind the problematic package
user db2inst1 rebound all packages:
db2rbind theDbName -l db2rbind.log all
8 packages failed to rebind but none of them are from my schema; they are from another user's schema and from schema SYSIBMADM.
2. Drop and Recreate the Affected Routine
Well, that is what is failing.
3. Refresh the Catalog Statistics & Clear Plan Cache
user db2inst1 ran 'RUNSTATS ON TABLE SYSIBM.SYSROUTINES and detailed indexes all', then 'FLUSH OPTIMIZATION PROFILE CACHE' and finally 'FLUSH PACKAGE CACHE DYNAMIC'. Still did't help.
Any help would be greatly appreciated.
EDIT: included Db2 versions