Retrieving EXPLAIN table rows for a package

You can retrieve the PLAN_TABLE rows for every explainable statement in a certain package.

About this task

Begin program-specific programming interface information. Several processes can insert rows into the same plan table. To understand access paths, you must retrieve the rows for a particular query in the appropriate order. The rows for a particular package are identified by the values of PROGNAME, COLLID, and VERSION. Those columns correspond to the four-part naming convention for packages:

location.collection.packageID.version

COLLID gives the COLLECTION name, and PROGNAME gives the PACKAGE_ID.

Procedure

To find the rows for all the explainable statements in a package, in their logical order:

Issue the following SQL statement: 
SELECT * FROM userID.PLAN_TABLE
  WHERE PROGNAME = 'program-name' 
    AND COLLID = 'collection-ID' 
    AND VERSION = 'version-name'
  ORDER BY QUERYNO1, QBLOCKNO, PLANNO, MIXOPSEQ;
  1. If the a static SQL package was bound with the EXPLAIN(YES) option and contains more than one statement with the same value for QUERYNO, use the SECTNOI column in place of QUERYNO.
End program-specific programming interface information.