Troubleshooting
Problem
Error SQL0478N is returned when running admin_move_table
Symptom
When running admin_move_table stored procedure, the following error message is displayed:
SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "VIEW or SUMMARY TABLE" cannot be processed because there is an object <object name>, of type "FUNCTION", which depends on it. SQLSTATE=SQL0478N
Cause
Such error can occur when running admin_move_table stored procedure, which contains a function that performs a select in a view that was dropped during swap phase. At the same time AUTO_REVAL is set to disabled.
Environment
If the environment was migrated from DB2 9.5, AUTO_REVAL should be set to disabled. In DB2 9.7, the default value is DEFERRED.
Diagnosing The Problem
Run the following commands to check if AUTO_REVAL is set to disabled:
db2 connect to <Database name>;
db2 get db cfg |grep AUTO_REVAL
Resolving The Problem
To resolve the problem, set AUTO_REVAL to DEFERRED, IMMEDIATE or DEFERRED_FORCE, as follows:
db2 connect to <Database name>;
db2 update db cfg using AUTO_REVAL [ DEFERRED, IMMEDIATE, DEFERRED_FORCE]
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21640199