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:

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:

Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.