Retrieving EXPLAIN table rows for a plan

You can find the EXPLAIN table rows for all explainable statements of a particular plan in their logical order.

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 plan are identified by the value of the APPLNAME column.

Procedure

To retrieve all the rows for all the explainable statements in a plan, in their logical order:

Issue the following SQL statement: 
SELECT * FROM user-ID.PLAN_TABLE
  WHERE APPLNAME = 'application-name'
  ORDER BY EXPLAIN_TIME, 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.

Results

The result of the ORDER BY clause shows whether any of the following conditions exist:

  • Multiple QBLOCKNO values within a QUERYNO or SECTNOI value
  • Multiple PLANNO values within a QBLOCKNO value
  • Multiple MIXOPSEQ values within a PLANNO value

All rows with the same non-zero value for QBLOCKNO and the same value for QUERYNO or SECTNOI relate to a step within the query. QBLOCKNO values are not necessarily executed in the order shown in PLAN_TABLE. But within a QBLOCKNO, the PLANNO column gives the sub-steps in the order they execute.

For each sub-step, the TNAME column identifies the table accessed. Sorts can be shown as part of a table access or as a separate step.

For entries that contain QUERYNO=0, use the EXPLAIN_TIME or SECTNOI column to distinguish individual statements.End program-specific programming interface information.