IBM Support

SQL0476N error during execution of DROP PROCEDURE statement.

Technote (FAQ)


How to resolve an SQL0476N error when attempting to drop a procedure?



When multiple stored procedures are created with the same name but with a different number of parameters, then the stored procedure is considered overloaded. When attempting to drop an overloaded stored procedure using the DROP PROCEDURE statement, the following error could result:

db2 drop procedure SCHEMA.PROCEDURENAME

DB21034E  The command was processed as an SQL statement because it was not valid Command Line Processor command.  During SQL processing it returned: SQL0476N  Reference to routine "SCHEMA.PROCEDURENAME" was made without a signature, but the routine is not unique in its schema.  SQLSTATE=42725


The error is returned because the stored procedure is overloaded and therefore the procedure is not unique in that schema. To drop the procedure you must specify the data types that were specified on the CREATE PROCEDURE statement or use the stored procedure's specific name per the examples below.


Operating System
DB2® Universal Database
DB2 Universal Database


In order to drop an overloaded stored procedure you can use either of the following statements:
db2 "DROP PROCEDURE procedure-name(int, varchar(12))"
db2 "DROP SPECIFIC PROCEDURE specific-name"

Note: The specific-name can be identified by selecting the SPECIFICNAME column from syscat.routines catalog view.

Document information

More support for: DB2 for Linux, UNIX and Windows
Routines (SP & UDF) - SQL

Software version: 9.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1287821

Modified date: 22 November 2007