If you are a regular reader of my tips you will know I am a huge fan of EXPLAIN STMTCACHE ALL. But STMTCACHE ALL is a fairly new invention. Thus I was very surprised when I two years ago discovered the EXPLAIN PACKAGE version of EXPLAIN was added in DB2 10. I did not take notice of this new kid on the block before I heard about plan management on IDUG. Then I had to revisit EXPLAIN PACKAGE for a closer study.

In short EXPLAIN PACKAGE populates your PLAN_TABLE with the current access path for a specific package. In other words: if you have a package for which you do not know the actual access path, then you can get access to it using EXPLAIN PACKAGE. This is a neat feature. You do not have to run a REBIND with EXPLAIN(YES) or EXPLAIN(ONLY) or need access to the PLAN_TABLE where REBIND will store the access path.

Before you get too excited about this feature you can only use it if you have SYSADM, SYSOPR, SYSCTRL or at least SQLADM authorisation. SYSADM, SYSOPR and SYSCTRL are for the very few, while SQLADM in my view is a pretty harmless authorisation which can be authorised to anybody having interest in access path evaluation in DB2 even in a production environment.

The access to use EXPLAIN PACKAGE is definitely the main prohibitor for a more widespread usage of the command. Another minor detail is that if a package is bound i DB2 version 8 or earlier EXPLAIN PACKAGE will fail. This will not stand in your way as most installations run a REBIND of almost all packages when they migrate to a new version of DB2 especially considering all the new possibilities of keeping the existing access path when migrating.