Keeping EXPLAIN under control

Ian_Bjorhovde

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 now1.

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 schema2

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.

  1. Maintaining plan stability may be possible with dynamic SQL, but there are a lot of variables, and the sheer volume of dynamic queries makes tracking EXPLAIN plans all but impossible for all but the most important queries. 
  2. While Visual Explain in Data Studio will utilize the EXPLAIN tables created in the SYSTOOLS schema, the Visual Explain tool that is part of DB2 Command Editor (which was available as part of the GUI tools included with DB2 V9.7 and earlier) will not. DB2 Command Editor’s Visual Explain tool (which has been deprecated) will actually produce an error if you have EXPLAIN tables in the SYSTOOLS schema but do not have EXPLAIN tables in your own schema. When the EXPLAIN tables in the SYSTOOLS schema do not exist, DB2 Command Editor’s Visual Explain will create them in the user’s current schema. Unfortunately there is probably not much hope that this bug will be fixed, so if you are still using Visual Explain with DB2 Command Editor, this article may not be particularly useful. 

 
Ian_Bjorhovde 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.

 


You may also like...

2 Responses

  1. March 3, 2015

    […] Keeping EXPLAIN under control […]

  2. December 14, 2016

    […] 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. […]

Leave a Reply

Your email address will not be published. Required fields are marked *