EXPLAIN PLAN Command

This section introduces the EXPLAIN PLAN command.

When you bind a plan, you might decide to include a package list that contains wildcard characters, for example COLLID3.* or even *.*. This could result in a plan pointing at thousands of packages with an even greater number of explainable SQL statements. To control the volume of output produced, the PACKLIMIT option is provided.

If a particular plan consists of more packages than specified in PACKLIMIT, a report with all the package names is produced, but no SQL statements in these packages are explained. This report shows the collection IDs, the creator and owner names, the version IDs, and the dates of precompilation. However, if any DBRMs belong to the plan, SQL statements in these DBRMs are explained.

For details on how to specify EXPLAIN PLAN commands refer to the Report Command Reference.

Explain PLAN Report - Package List Block

The following report example results of an EXPLAIN PLAN statement with PACKLIMIT(10) if a plan named LARGPLAN was bound with a package list of COLLECT.*, which includes 80 packages:

ACTUAL AT: 01/30/16 13:10:38 IBM OMEGAMON FOR DB2 PERFORMANCE EXPERT (V5.5.0)PAGE       : 1-50      
                                    EXPLAIN PLAN          DB2 VERSION: V10       
LOCATION : DSNAPC1                   LARGEPLAN            USER AUTHID: XRK      
SUBSYSTEM: APC1                        DETAIL             CURR.SQLID : XRK      


           USE PACKAGE=COLLID.NAME.(VERSION) TO GET A DETAILED LISTING


PACKAGE  COLLECTION ID.     CREATOR  OWNER    EXP  PC-DATE    VERSION
-------- ------------------ -------- -------- ---  ---------- ----------------
DRDAUPDT APC5COL3           XXASP16  XXASP16  NO   2010-11-30 VERSION_1.0.0_27/1
DRDAUPDT APC5COL3           XXASP16  XXASP16  NO   2010-11-27 VERSION_1.0.0_20/1
ABINDCS1 APC5COL9           XXASP09  XXASP09  NO   2010-07-24 NAMIK_PRIVATE_VER1
RUW      APC5COL9           XXASP09  XXASP09  YES  2010-12-01 NAMIK_PRIVATE_VER1
RUWCURHL APC5COL9           XXASP09  XXASP09  NO   2010-08-07 NAMIK_PRIVATE_VER1
CHOLZ    CHOLZCOL           USR1     USR1     YES  2015-08-18 VER3
CHOLZ    CHOLZCOL           USR1     USR1     YES  2015-08-18 VER2
CHOLZ    CHOLZCOL           USR1     USR1     YES  2015-08-18 VER1
DB0C4    DB0C4COL           USR1     USR1     YES  2015-08-18 VER3
DB0C4    DB0C4COL           USR1     USR1     YES  2015-08-18 VER2
DB0C4    DB0C4COL           USR1     USR1     YES  2015-08-18 VER1
HVAR2    HVAR2COL           USR1     USR1     YES  2015-08-18 VER3
HVAR2    HVAR2COL           USR1     USR1     YES  2015-08-18 VER2
HVAR2    HVAR2COL           USR1     USR1     YES  2015-08-18 VER1
KEYT     KEYTCOL            USR1     USR1     YES  2015-08-18 VER3
KEYT     KEYTCOL            USR1     USR1     YES  2015-08-18 VER2
KEYT     KEYTCOL            USR1     USR1     YES  2015-08-18 VER1
MIX      MIXCOL             USR1     USR1     YES  2015-08-18 VER3
MIX      MIXCOL             USR1     USR1     YES  2015-08-18 VER2
MIX      MIXCOL             USR1     USR1     YES  2015-08-18 VER1
MX       MXCOL              USR1     USR1     YES  2015-08-23 VER3
MX       MXCOL              USR1     USR1     YES  2015-08-23 VER2
MX       MXCOL              USR1     USR1     YES  2015-08-23 VER1