EXPLAIN Tables Need to be Converted Prior to DB2 10 Migration

Traditionally with DB2 upgrades, DBAs haven't had to worry about converting the EXPLAIN tables (including, for example, the user PLAN_TABLE). Of course new columns are added to these tables with each new DB2 release, and DBAs could choose to modify their current tables to accommodate these new columns, but it hasn't been necessary. The EXPLAIN tables could function through the upgrade without any changes. And even without the columns and other enhancements delivered with the latest DB2 release, the tables could still be populated with valid data post-upgrade.

Convenient as this has been, it's changed with DB2 10. It's now necessary to convert EXPLAIN tables prior to migration. While this doesn't require a significant amount of extra work, customers do need to plan for it.

The DB2 install provides instructions on converting existing EXPLAIN tables to the current release format.  If you're migrating to DB2 10 from DB2 version 8, you should convert all the existing EXPLAIN tables to version 8.0 format before beginning the migration. Likewise, if you're migrating from DB2 9, convert the EXPLAIN tables to DB2 9 format before starting the migration. If the EXPLAIN tables aren't converted before migrating to DB2 10, issuing an EXPLAIN will result in an error. For instance, DB2 will return SQLCODE -20008 reason code 2 when performing an EXPLAIN and the EXPLAIN tables are in a pre-Version 8 format.

Once you migrate to DB2 10, you must also ensure the tables are in DB2 10 format and encoded in UNICODE. If your tables are still encoded in EBCDIC, an EXPLAIN will generate an SQLCODE -878 error.

This process is covered in the “Converting EXPLAIN Tables (before migration)" section of the DB2 for z/OS Installation and Migration Guide. Note that there are different sets of instructions when migrating from V8 versus V9.1

The install guide lists some jobs that you can run to help with the conversion. One thing I noticed when reviewing this section: Check any applications you have that may join with the EXPLAIN tables. You may receive different results because the EXPLAIN tables being unicode and your user tables are in EBCDIC.

Have you converted EXPLAIN tables as part of a migration to DB2 10? If so, please share your words of wisdom about this process.