Keeping EXPLAIN under control

Have you ever looked at a list of tables in your database, only to see the set of EXPLAIN tables present in two, five or even 10 different developers’ schemas?

I don’t know about you, but this drives me crazy. Very few of the developers that I know pay attention to these tables, and many of them are not even aware that they exist (even the developers that use Visual Explain aren’t always aware of these tables). However, they create something of a management headache for me, especially when it’s time to migrate to a new version of DB2.

IBM does provide a way to migrate the EXPLAIN tables from the structure used in one version to the structure used in another version (using the db2exmig utility), but to be completely honest, I will almost always just remove a developer’s explain tables. This helps me keep the database tidy, and I have yet to have a developer come yell at me looking for their EXPLAIN tables. I realize that stating this may cause any mainframe-turned-LUW DBAs reading this to have a stroke, but the ratio of dynamic SQL to static SQL has been tilted very strongly towards the dynamic side for quite some time now¹.

Creating EXPLAIN Tables

When was the last time you created explain tables? Did you use .../sqllib/misc/EXPLAIN.DDL to do it? Or did they just get created automatically by Command Editor?

Were you aware that IBM provides an integrated way to create the explain tables for you? You can use the handy sysinstallobjects stored procedure to create explain tables:

call sysproc.sysinstallobjects('EXPLAIN','C',NULL,NULL)

This procedure has 4 arguments:

1. The tool name (explain tables, storage management, etc.)
2. The action (create, delete, etc.)
3. The tablespace to create the objects in
4. The schema to create the objects in

If you don’t specify values for the 3rd and 4th arguments (using NULL), the sysinstallobjects procedure will create the explain tables in the SYSTOOLSPACE tablespace, using the SYSTOOLS schema.

Using the sysinstallobjects stored procedure is not only incredibly handy for managing explain tables (you can also use it to drop, verify and even migrate them to a new version, if that’s how you roll).

Stop the Insanity

As mentioned earlier, it’s exceedingly common to create EXPLAIN tables in your own schema — after all, there are many tools that rely on the CURRENT SCHEMA register to decide where to look for explain tables.

However, I’d like to make a case for keeping your explain tables in the SYSTOOLS schema. Consider the following:

• Using EXPLAIN PLAN FOR will use explain tables in SYSTOOLS schema without having to modify the CURRENT SCHEMA register

• The db2exfmt utility will also use the SYSTOOLS schema without any additional command-line options, allowing a very simple syntax:

  db2exfmt -d dbname -1

• These will also enable you to utilize the explain functionality contained within db2top without additional command-line options or .db2toprc settings

• Using the Visual Explain feature in Data Studio will use explain tables in the SYSTOOLS schema²

By doing this, you’ll be able to get away with having only a single copy of the EXPLAIN tables in the database.

One more thing…

Unfortunately, one thing that sysinstallobjects does not do is grant privileges on the objects that it creates. So, if you’re planning to try and keep only a single copy of the explain tables in the database, you will need to grant appropriate privileges on the tables manually, after using sysinstallobjects to create them.

At a minimum, you will need to grant SELECT and INSERT on the explain tables, and EXECUTE on the EXPLAIN_GET_MSGS user-defined function. You may also want to grant the EXPLAIN database authority, which allows users to explain statements even if they do not have authority to query the tables referenced in the queries.

Here is an example of the SQL granting these privileges. It defines a role that you can use to simplify your grants to individual users:

CREATE ROLE explainer;

GRANT EXPLAIN ON DATABASE TO ROLE explainer;

GRANT EXECUTE ON FUNCTION systools.EXPLAIN_GET_MSGS           TO ROLE explainer;

GRANT SELECT,INSERT ON TABLE systools.ADVISE_INDEX            TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.ADVISE_INSTANCE         TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.ADVISE_MQT              TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.ADVISE_PARTITION        TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.ADVISE_TABLE            TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.ADVISE_WORKLOAD         TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_ACTUALS         TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_ARGUMENT        TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_DIAGNOSTIC      TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_DIAGNOSTIC_DATA TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_INSTANCE        TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_OBJECT          TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_OPERATOR        TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_PREDICATE       TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_STATEMENT       TO ROLE explainer;
GRANT SELECT,INSERT ON TABLE systools.EXPLAIN_STREAM          TO ROLE explainer;

-- If you're running DB2 10.1 or higher:
GRANT SELECT,INSERT ON TABLE systools.OBJECT_METRICS          TO ROLE explainer;

Make sure you consider how to deal with growth in these tables (perhaps by setting a policy for when old explain plans will be removed) and also ensuring that you have reasonably up-to-date statistics on these tables.

Additional Reading

Ember wrote a very nice 3-part primer on using EXPLAIN here on the blog. Part 1 provides an overview of what EXPLAIN is, and Part 2 describes how to generate EXPLAIN plans from the command line, and Part 3 will teach you how to use Visual Explain from within Data Studio.

 
Ian Bjorhovde is the principal consultant for DataProxy System Solutions in Phoenix, Arizona. He has worked in the IT industry for over 18 years and has spent the last 15 years as a consultant developing and supporting large DB2 LUW databases with clients in many different industries. He is an IBM Champion for Information Management, produces a DB2-focused podcast called The Whole Package Cache and has presented at RUG, IDUG and IBM conferences.

• Author Details

DataProxy LLC

Ian Bjorhovde

http://www.pkgcache.com/

Ian Bjorhovde is the principal consultant for DataProxy, LLC in Phoenix, Arizona. He has worked in the IT industry for over 20 years and has spent the last 19 years as a consultant developing and supporting large DB2 LUW databases with clients in many different industries. He is an IBM Gold Consultant and IBM Champion, produces a DB2-focused podcast called The Whole Package Cache and has presented at RUG, IDUG and IBM conferences around the world.