Retrieving rows from a statement table

You can use rows in the statement table to determine the cost of executing an SQL statement.

To retrieve rows in a statement table:

• Issue a SELECT statement to retrieve the columns of a statement table.
  The following example statement, which retrieves all rows about the statement that is represented by query number 13:

  SELECT * FROM JOE.DSN_STATEMNT_TABLE
     WHERE QUERYNO = 13;

• Join the rows of the statement table to the corresponding rows in the plan table.
  Certain columns in a statement table contain the same values as corresponding columns of the plan table for a given plan, including:

  • QUERYNO
  • APPLNAME
  • PROGNAME
  • COLLID
  • EXPLAIN_TIME
  • VERSION
  • SECTNOI
  The following example statement retrieves all columns from user-ID.PLAN_TABLE, and cost-related columns from user-ID.DSN_STATEMENTTABLE, for all rows that are related to the 'APPL1' application.

  SELECT A.*, PROCMS, COST_CATEGORY
   FROM JOE.PLAN_TABLE A, JOE.DSN_STATEMNT_TABLE B
     WHERE A.APPLNAME = 'APPL1' AND
     A.APPLNAME = B.APPLNAME AND
     A.QUERYNO = B.QUERYNO AND
     A.SECTNOI = B.SECTNOI AND
     A.PROGNAME = B.PROGNAME AND
     A.COLLID   = B.COLLID AND
     A.EXPLAIN_TIME = B.EXPLAIN_TIME
     A.VERSION = B.VERSION
  ORDER BY A.QUERYNO1, A.QBLOCKNO, A.PLANNO, A.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.