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 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:
This procedure has 4 arguments:
- The tool name (explain tables, storage management, etc.)
- The action (create, delete, etc.)
- The tablespace to create the objects in
- The schema to create the objects in
If you don’t specify values for the 3rd and 4th arguments (using
sysinstallobjects procedure will create the explain tables in the
SYSTOOLSPACE tablespace, 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:
EXPLAIN PLAN FORwill use explain tables in
SYSTOOLSschema without having to modify the
db2exfmtutility will also use the
SYSTOOLSschema 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
db2topwithout additional command-line options or
Using the Visual Explain feature in Data Studio will use explain tables in the
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
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.
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.
- 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. ↩
While Visual Explain in Data Studio will utilize the EXPLAIN tables created in the
SYSTOOLSschema, 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
SYSTOOLSschema but do not have EXPLAIN tables in your own schema. When the EXPLAIN tables in the
SYSTOOLSschema 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 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.