Re: Drop SQL function with unknown parameters

On 26-May-2010 14:16, Dennis Lovelady wrote:

We need to drop an SQL UDF from the database. When we select from
SYSFUNCS, this function appears only one time. When we do "DROP
FUNCTION <functionname>" the system returns SQL0476
("Routine<functionname> not unique."). We don't know what
parameters were used to create the function - it was created
before the dawn of history in this client's perspective - and
don't know how to parse the SPECIFIC_PARAMETERS column - nor do
we understand why that would be necessary since there appears to
be no overloading.

What options do we have if we really want to get rid of this UDF?

Drop by its /specific name/. Use SPECIFIC_SCHEMA.SPECIFIC_NAME from SYSFUNCS rather than ROUTINE_SCHEMA.ROUTINE_NAME, and use DROP SPECIFIC FUNCTION versus DROP FUNCTION syntax; i.e. use the first, rather than the second of the two SQL DROP invocation examples that follow:

DROP SPECIFIC FUNCTION specschema.specname
;
DROP FUNCTION funcschema.funcname
;

If the query of SYSFUNCS does not show two functions of the same name, then ensure there is a file QASQRESL over SYSROUTINE keyed over RTNNAME and RTNSCHEMA. IIRC, that file\key is used to find the non-specific name for a DROP FUNCTION and DROP PROCEDURE.

Regards, Chuck