IBM Support

Error SQL0478N is returned when running admin_move_table

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]

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP & UDF) - SQL","Platform":[{"code":"PF033","label":"Windows"},{"code":"PF016","label":"Linux"},{"code":"PF010","label":"HP-UX"},{"code":"PF002","label":"AIX"},{"code":"PF027","label":"Solaris"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21640199