Search code examples
sqlstored-proceduresdb2

Cannot drop DB2 stored procedure due to undefined name error

I have a stored procedure for DB2. I'm unable to drop the procedure. I'm getting the below error while executing the DROP PROCEDURE <PROCEDURE_NAME> command.

Error:

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=MYSCHEMA.MyProcedure, DRIVER=3.50.152 Message: "MYSCHEMA.MyProcedure" is an undefined name. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.50.152

Procedure details:

  • ROUTINESCHEMA: MYSCHEMA
  • ROUTINENAME: MyProcedure
  • ROUTINETYPE: P
  • OWNER: ROLEA

Command executed:

DROP PROCEDURE MYSCHEMA.MyProcedure;

Error:

"MYSCHEMA.MyProcedure" is an undefined name. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.50.152

Additional information:

  • The procedure exists in the ROUTINESCHEMA MYSCHEMA.
  • My procedure has no definition
  • Unable to view the stored procedure. When I try to open it in the editor, I get an 'object not found' error.

Question

Why am I getting this error and how can I successfully drop the procedure?

Solution

  • DB2 is generally case-sensitive and folds ordinary identifiers to uppercase. Use, delimited identifiers to keep the exact capitalization:

    DROP PROCEDURE MY_SCHEMA."MyProcedure";