Troubleshooting
Problem
Attempts to drop a DB2 schema may result in SQL0478N error when either using drop schema command or calling the system routine SYSPROC.ADMIN_DROP_SCHEMA.
Symptom
For example, when you drop a schema named MYSCHEMA by running:
db2 drop schema MYSCHEMA restrict
or
db2 "call SYSPROC.ADMIN_DROP_SCHEMA('MYSCHEMA ', NULL, 'ERRORSCHEMA', 'ERRORTABLE')
You may receive one of below errors:
Note: The system routine SYSPROC.ADMIN_DROP_SCHEMA itself may return status 0, so you will have to check the error in the associated error table provided in the parameters.
1) SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"
cannot be processed because there is an object "MYSCHEMA.NAME1", of type "PROCEDURE",
which depends on it. SQLSTATE=42893
The error suggests there is a DB2 PROCEDURE named "MYSCHEMA.NAME1" depending on the schema. However, if you try to drop the procedure, it may fail with the error SQL0204N:
db2 "drop specific procedure MYSCHEMA.NAME1"
SQL0204N "MYSCHEMA.NAME1" is an undefined name. SQLSTATE=42704
which means the procedure doesn't exist in the database.
2) SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA"
cannot be processed because there is an object "MYSCHEMA.MYEMB", of type "PLAN",
which depends on it. SQLSTATE=42893
The error suggests there is a DB2 package named "MYSCHEMA.MYEMB" which depends on the schema.
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tables","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF010","label":"HP-UX"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Log InLog in to view more of this document
This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.
Was this topic helpful?
Document Information
Modified date:
01 May 2025
UID
swg21592942