DB2 - Problem description
Problem IT37236 | Status: Closed |
EXPLAIN_FROM_SECTION RETURNS SQL0220N FOLLOWING UPGRADE TO DB2 V11.5.5 DESPITE RUNNING DB2EXMIG | |
product: | |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 | |
Problem description: | |
After migrating or upgrading a database to Db2 Version 11.5.5 or later and after running db2exmig to migrate the explain tables, running the EXPLAIN_FROM_SECTION (or another EXPLAIN_FROM...) stored procedure returns the following error: SQL0220N The Explain table ".OBJECT_METRICS", column "MEMBER" does not have the proper definition or is missing. SQLSTATE=55002 This is because the db2exmig explain table migration tool is not migrating the OBJECT_METRICS table correctly in v11.5.5 onwards. This problem does not affect the regular EXPLAIN facility or the db2exfmt explain formatting tool. It is specific to the EXPLAIN_FROM ... stored procedures. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5 FP7 or later * **************************************************************** | |
Local Fix: | |
If the OBJECT_METRICS table is empty, simply drop the table and then recreate it using the CREATE TABLE statement found in ~/sqllib/misc/EXPLAIN.DDL. Be sure to create the new table using the same schema name as the original. If the OBJECT_METRICS table is not empty, you can follow these steps: 1) rename the OBJECT_METRICS table to some other name of your choosing using the RENAME TABLE statement 2) create a new OBJECT_METRICS table with the same schema name as the original using the CREATE TABLE statement found in ~/sqllib/misc/EXPLAIN.DDL 3) copy the rows from the old table into the new table (e.g. using an INSERT ... SELECT statement), paying careful attention to the order of the columns. The columns in the old and new tables are in a different order. For example, if using an INSERT ... SELECT statement, list all the columns in the SELECT list in the order that they appear in the new table. 4) You can drop the old table once you are comfortable that the data has been copied over correctly. | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.5 FP7 or later * **************************************************************** | |
Comment | |
Upgrade to 11.5 FP7 or later | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 11.06.2021 21.11.2021 21.11.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |