You can explain DB2/LUW packages using the following methods.
In all cases, you must have created the Explain tables prior to using these methods.
There are two ways of doing this:
call SET_ROUTINE_OPTS('EXPLAIN ALL')
This will set the Explain option for the duration of your session.
db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
Once this option has been set, any Stored Procedure which are created will be Explained, and the explain info will be written to your explain tables.
DB2 supplies a Stored Procedure that can be used to create Explain information for an existing Package.
To use, run this Stored Procedure specifying your package / statement as input parms. You need to run this for every section in your package - if your package has a large number of statements this can be quite a big job.
AQT has a tool to explain all the statements within a package. This can be invoked as shown below.
This tool uses Stored Procedure SYSPROC.EXPLAIN_FROM_CATALOG. This procedure must be present on your system, and you must have the authority to run it.
AQT will run SYSPROC.EXPLAIN_FROM_CATALOG for every statement in your package. This is a great time saver compared to running this procedure manually.
When the DB2_SQLROUTINE_PREPOPTS method is used, all sections in the package will be given the same explain timestamp. It will appear as a single entry in the explain tables.
When the SYSPROC.EXPLAIN_FROM_CATALOG method is used, each package section is explained separately. Each section will have a different timestamp and will appear as a separate entry in the explain tables. This can make it more difficult to manage these entries in the explain tables.