DB2 - Problem description
Problem IT32679 | Status: Closed |
ROUTINE MIGRATION FAILS WITH A SQL0478N WHEN A ROUTINE IS USED AS PART OF A TABLE DEFINITION | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
The migrate database routine steps fails to modify routines when table objects are dependent on the routines being modified. An example is a table object with a column defined with a function. For example the column has the definition of generated as myfunction() and where the user defined function myfunction uses a Db2 supplied function. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.5.5.0 or higher * **************************************************************** | |
Local Fix: | |
If the migration has failed, you can do the following so that the migration completes successfully. 1. Take BACKUP of previous DB if one does not already exist and RESTORE it to a test database at the same version and level as the one you are upgrading from. 2. ALTER the table to drop the column that uses the function. 3. REORG the table 4. Take BACKUP 5. RESTORE the backup from the older version into the new instance. This should also auto migrate the database. 6. Next SET INTEGRITY of the table using the OFF CASCADE DEFERRED options. 7. Then ALTER the table to add the dropped column. 8. The final step is to SET INTEGRITY FOR the table using the IMMEDIATE CHECKED FULL ACCESS FORCE GENERATED options. The following URLs document the BACKUP, RESTORE, ALTER, REORG and SET INTEGRITY commands. BACKUP DATABASE https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ib m.db2.luw.admin.cmd.doc/doc/r0001933.html RESTORE DATABASE https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ib m.db2.luw.admin.cmd.doc/doc/r0001976.html ALTER TABLE https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ib m.db2.luw.sql.ref.doc/doc/r0000888.html REORG TABLE https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ib m.db2.luw.admin.cmd.doc/doc/r0001966.html SET INTEGRITY https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ib m.db2.luw.sql.ref.doc/doc/r0000998.html | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : follow-up : IT32727 | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 28.04.2020 20.11.2020 20.11.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |