SQL0476N error during execution of DROP PROCEDURE statement.

Technote (FAQ)


Question

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

Answer

PROBLEM

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


CAUSE

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.


SCOPE

Product
Operating System
Version
DB2® Universal Database
Linux®/UNIX®/Windows®
8
DB2 Universal Database
Linux/UNIX/Windows
9.1


SOLUTION

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.

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
DB2 Programming Interfaces - SQL Stored Procedures

Software version:

9.1

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1287821

Modified date:

2007-11-22

Translate my page

Machine Translation

Content navigation