DB2 Upgrade Detail: Upgrading Explain Tables
Explain tables change in structure from version to version of DB2. If you want to continue to use the same set of explain tables across a DB2 upgrade, you must take special action to upgrade them.
Explain tables are used when generating a human-readable version of the access plan for a query. They may be implicitly created by a tool, or explicitly created using a script or a stored procedure. Each user may have their own set of explain tables, or they may be shared. Ian blogged for me with some suggestions on keeping your explain tables under control.
Identifying Explain Tables
Before migrating explain tables, you have to know which schemas have explain tables in them. Assuming you do not have other tables in your database with ‘EXPLAIN’ in the name, this can be done quickly using:
>db2 list tables for all |grep EXPLAIN EXPLAIN_ACTUALS DB2INST1 T 2012-02-04-188.8.131.529389 EXPLAIN_ARGUMENT DB2INST1 T 2012-02-04-184.108.40.2061378 EXPLAIN_DIAGNOSTIC DB2INST1 T 2012-02-04-220.127.116.112329 EXPLAIN_DIAGNOSTIC_DATA DB2INST1 T 2012-02-04-18.104.22.1682263 EXPLAIN_INSTANCE DB2INST1 T 2012-02-04-22.214.171.1240352 EXPLAIN_OBJECT DB2INST1 T 2012-02-04-126.96.36.1997866 EXPLAIN_OPERATOR DB2INST1 T 2012-02-04-188.8.131.528096 EXPLAIN_PREDICATE DB2INST1 T 2016-12-13-17.42.25.082431 EXPLAIN_STATEMENT DB2INST1 T 2012-02-04-18.42.44.079821 EXPLAIN_STREAM DB2INST1 T 2012-02-04-18.42.47.088970
In this example, I can easily see that the only schema I have explain tables in is DB2INST1.
IBM’s db2exmig tool migrates the explain tables. Once a list of schemas with explain tables has been generated, the following should be run for each schema:
db2exmig -d dbname -e schemaname
db2exmig -d sample -e db2inst1
This tool renames the existing explain tables, creates a new set of tables on the current version, and then copies the contents to the new tables. It then drops the old explain tables. Interestingly enough, it will preserve any custom columns you have added to explain tables.
You will most likely need DBADM to run this tool. If you must run it with lower permissions, examine the IBM DB2 Knowledge Center page on db2exmig for the details of what other permissions might substitute.
If you are an explain-a-holic (or support one), then the amount of data in the explain tables could be significant. Consider pruning the tables before migrating them, or simply dropping them and re-creating them.
Dropping and Recreating
If you do not wish to retain old explain data, you can also simply drop and re-create your explain tables on the new version. To drop them, use:
db2 "call sysproc.sysinstallobjects('EXPLAIN','D',NULL,'DB2INST1')"
Then, to create them:
db2 "call sysproc.sysinstallobjects('EXPLAIN','C',NULL,'DB2INST1')"