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

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-18.42.52.569389
EXPLAIN_ARGUMENT                DB2INST1        T     2012-02-04-18.42.44.951378
EXPLAIN_DIAGNOSTIC              DB2INST1        T     2012-02-04-18.42.47.402329
EXPLAIN_DIAGNOSTIC_DATA         DB2INST1        T     2012-02-04-18.42.47.782263
EXPLAIN_INSTANCE                DB2INST1        T     2012-02-04-18.42.41.320352
EXPLAIN_OBJECT                  DB2INST1        T     2012-02-04-18.42.45.477866
EXPLAIN_OPERATOR                DB2INST1        T     2012-02-04-18.42.45.788096
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.

db2exmig

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

For example:

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')"