Explaining DB2/LUW Packages

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.

Explain Stored Procedures when they are created

There are two ways of doing this:

run the following command.
call SET_ROUTINE_OPTS('EXPLAIN ALL')

This will set the Explain option for the duration of your session.
Set the Explain option as a system-wide option
- open a db2 command window in adminstrator mode
- run the command:
  db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
- you must then stop and restart your DB2 server

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.

this option only applies to Stored Procedures. It does not apply to database programs which you bind to the server
this option only applies to new Stored Procedures and not to existing ones (without dropping / recreating them).

Using SYSPROC.EXPLAIN_FROM_CATALOG

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.

explain_package0

Using AQT Explain Package

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.

explain_package

Explain Timestamp

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.

Advanced Query Tool

https://www.querytool.com