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.
Cause
Before dropping a DB2 schema, DB2 will check the system catalog tables if any objects still depend on the schema. If there is any of such objects existing, it will fail with SQL0478N error. You should either manually drop all the dependent objects or alternatively you can run the system routine SYSPROC.ADMIN_DROP_SCHEMA to drop the dependent objects first before it drops the schema.
However, as you can see in above error situations, you may still receive SQL0478N error even when you attempt to drop the schema calling the system routine SYSPROC.ADMIN_DROP_SCHEMA. The reason for this is there are some objects that the system routine SYSPROC.ADMIN_DROP_SCHEMA doesn't support to drop, which includes:
index extensions
nicknames
packages
typed tables
array types
user-defined structured types (and their transform functions)
typed views
jars (Java routine archives)
staging tables
Therefore, if there are any above objects depending on the schema to be dropped, you will need to manually drop them first before you can drop the schema. More details on this can be found in the DB2 information center at http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0022036.html.
Resolving The Problem
1) For above first error situation, the "PROCEDURE" name "MYSCHEMA.NAME1" returned in the SQL0478N error actually refers to a JAR(Java routine archives) installed by the DB2 JAR file administration routine sqlj.install_jar, instead of referring to a normal DB2 PROCEDURE.
This JAR file contains Java routine class files used by external DB2 JAVA routines. However, It's not dropped when you drop the corresponding JAVA routines, nor can be dropped by the system routine SYSPROC.ADMIN_DROP_SCHEMA.
you can use below query to find out all the installed JARs that depends on the schema to be dropped:
db2 "select JARSCHEMA,JAR_ID,DEFINER from SYSIBM.SYSJAROBJECTS where JARSCHEMA='MYSCHEMA'"
And you need to run below command to drop the JARs manually:
db2 "call sqlj.remove_jar('MYSCHEMA.NAME1')"
Note: NAME1 is the JAR name from the JAR_ID column of the previous query.
After dropping all these dependent JARs, you should be able to retry the drop schema command or SYSPROC.ADMIN_DROP_SCHEMA, which should run successfully.
2) For the above second error situation, the reason that you can't drop the schema MYSCHEMA is that there is a package named MYSCHEMA.MYEMB that depends on the schema. And similarly the system routine SYSPROC.ADMIN_DROP_SCHEMA does not support dropping of packages. So you will have to drop the package manually.
you can use below query to find out all the packages that depend on the schema to be dropped:
db2 "select NAME,CREATOR,UNIQUE_ID from SYSIBM.SYSPLAN where CREATOR='MYSCHEMA'"
And then you need to run below command to drop the packages manually:
db2 "drop package MYSCHEMA.MYEMB"
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21592942