SQL0476N trying to drop a duplicate stored procedure
I received an error SQL0476N when trying to drop a duplicate stored procedure. How do I drop the procedure without getting the error?
DB2® Universal Database™ (DB2 UDB) allows you to create two identically named stored procedures within a schema as long as they don't have the same number of parameters. For example :
CREATE PROCEDURE schema1.PART ( IN NUMBER INT , OUT PART_NAME CHAR (35) ) ...
CREATE PROCEDURE schema1.PART ( IN COST DECIMAL (5,3), IN NUMBER INT, OUT COUNT INT ) ...
The second statement will succeed as long as the number of parameters are not the same. Otherwise, a SQLSTATE 42723 is generated even if the data types are not identical.
To drop a duplicate stored procedure, you must qualify the DROP PROCEDURE with the same data types that were specified on the CREATE PROCEDURE statement in the corresponding position. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance to be dropped.
DROP PROCEDURE schema1.PART (DECIMAL, INTEGER, INTEGER)
Another method to drop the procedure is to use the specific-name identifies the specific procedure instance in the implied schema.
More support for:
DB2 for Linux, UNIX and Windows
Routines (SP & UDF) - SQL
Software version: 9.1, 9.5, 9.7
Operating system(s): Platform Independent
Software edition: All Editions
Reference #: 1195923
Modified date: 20 September 2013