June 17, 2014

New Stored Procedure Simplifies EXPLAIN Maintenance

Managing EXPLAIN tables can be time-consuming. DB2 developers often create EXPLAIN tables and allow them to exist on the system for years without being upgraded to include the latest columns or support the newer table types that have been introduced in recent DB2 releases. Because these tables aren't current, they're incapable of utilizing key features that are designed to help diagnose the access paths DB2 has chosen and why.

In short, if you're a developer and you rely on EXPLAIN tables that are based on older DB2 releases, you're missing out.

A new stored procedure, SYSPROC.ADMIN_EXPLAIN_MAINT, is designed to simplify the process of upgrading existing EXPLAIN tables. It can also be used to create the new tables that are introduced with each release of DB2. (See, for example, DSN_PREDICATE_SELECTIVITY, a new table in DB2 11.) So if you have an old set of tables, this procedure will make sure they're current. And if you're missing any of the latest tables, it will create them for you.

IBM is releasing this new procedure through maintenance APAR PI05200 (NEW STORED PROCEDURE SYSPROC.ADMIN_EXPLAIN_MAINT) for DB2 11 only. It was closed for customer testing on April 25 and will now generate PTF UI17423.

To learn more about this new stored procedure and view a sample program used to call it, see the DB2 11 Managing Performance Guide (updated April 2014).