CREATE PLAN TABLE

CREATE TABLE XTRNBBA.PLAN_TABLE
(
  QUERYNO          INTEGER NOT NULL,
  QBLOCKNO          SMALLINT NOT NULL,
  APPLNAME          CHAR(8) NOT NULL,
  PROGNAME          CHAR(8)  NOT NULL,
  PLANNO          SMALLINT NOT NULL,
METHOD          SMALLINT NOT NULL,
  CREATOR          CHAR(8)  NOT NULL,
TNAME          CHAR(18)  NOT NULL,
TABNO          SMALLINT NOT NULL,
ACCESSTYPE          CHAR(2)  NOT NULL,
  MATCHCOLS          SMALLINT NOT NULL,
ACCESSCREATOR     CHAR(8)  NOT NULL,
ACCESSNAME          CHAR(18)  NOT NULL,
INDEXONLY          CHAR(1)  NOT NULL,
SORTN_UNIQ          CHAR(1)  NOT NULL,
SORTN_JOIN          CHAR(1)  NOT NULL,
SORTN_ORDERBY     CHAR(1)  NOT NULL,
SORTN_GROUPBY     CHAR(1)  NOT NULL,
SORTC_UNIQ          CHAR(1)  NOT NULL,
SORTC_JOIN          CHAR(1)  NOT NULL,
SORTC_ORDERBY     CHAR(1)  NOT NULL,
SORTC_GROUPBY     CHAR(1)  NOT NULL,
TSLOCKMODE          CHAR(3)  NOT NULL,
TIMESTAMP          CHAR(16)  NOT NULL,
REMARKS          VARCHAR(254)  NOT NULL,
PREFETCH          CHAR(1)  NOT NULL WITH DEFAULT,
COLUMN_FN_EVAL     CHAR(1)  NOT NULL WITH DEFAULT,
MIXOPSEQ          CHAR(1)  NOT NULL WITH DEFAULT,
VERSION          VARCHAR(64)  NOT NULL WITH DEFAULT,
COLLID          CHAR(18)  NOT NULL WITH DEFAULT,
ACCESS_DEGREE     SMALLINT,
ACCESS_PGROUP_ID     SMALLINT,
JOIN_DEGREE          SMALLINT,
JOIN_PGROUP_ID     SMALLINT,
SORTC_PGROUP_ID     SMALLINT,
SORTN_PGROUP_ID     SMALLINT,
PARALLELISM_MODE   CHAR(1),
MERGE_JOIN_COLS     SMALLINT,
CORRELATION_NAME     CHAR(18),
PAGE_RANGE          CHAR(1)  NOT NULL,
JOIN_TYPE          CHAR(1)  NOT NULL,
GROUP_MEMBER     CHAR(8)  NOT NULL,
IBM_SERVICE_DATA     VARCHAR(254) NOT NULL,
WHEN_OPTIMIZE            CHAR(1)  NOT NULL,
QBLOCK_TYPE     CHAR(6)  NOT NULL,
BIND_TIME          TIMESTAMP NOT NULL
)  IN DATABASE.TABLESPACE;


EXEC SQL

     EXPLAIN ALL SET QUERYNO =  n FOR
     SQL statement

end-exec.

SELECT

QUERYNO,  QBLOCKNO, QBLOCK_TYPE,  APPLNAME, PROGNAME,
PLANNO, METHOD,  CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE,
MATCHCOLS, ACCESSNAME,  INDEXONLY, SORTN_PGROUP_ID,
SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,  SORTN_GROUPBY,
SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY,
SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP,
PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, COLLID, VERSION,
ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE, JOIN_PGROUP_ID,
PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,
PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, BIND_TIME

FROM XTRNBBA.PLAN_TABLE

ORDER BY APPLNAME, COLLID, VERSION, PROGNAME,
                     TIMESTAMP DESC, QUERYNO, QBLOCKNO, PLANNO;



Method      -
          0     First table accessed
          1     Nested loop join
          2     Merge scan join
          3     Independent Sort
          4     Hybrid join


Accesstype     I     Indexed access
          I1     One-fetch index scan
          R     Tablespace scan
          N     Index access with an IN predicate
          M     Multiple index scan
          MX     Specification of the index name for multiple index access
          MI     Multiple index access by RID intersection
          MU     Multiple index access by RID union

Matchcols     Contains number of index columns used in an index scan when accesstype is I, I1, N, or MX

Accessname     Indicates the name of the index used

Indexonly     Y - access to index is suffcient to satisfy query
          N - access to tablespace is required

Sortn_uniq     Y - indicates that a sort must be performed on the new table to remove duplicates

Sortn_join     Y - indicates that a sort must be performed on the new table to accomplish a merge scan join or a sort is performed on the RID list and intermediate table of a hybrid join

Sortn_orderby     Y - indicates that a sort must be performed on the new table to order rows

Sortn_groupby     Y - indicates that a sort must be performed on the new table to group rows

Sortc_uniq     Y - indicates that a sort must be performed on the composite table to remove duplicates

Sortc_join     Y - indicates that a sort must be performed on the composite table to accomplish a merge scan join or a sort is performed on the RID list and intermediate table of a hybrid join

Sortc_orderby     Y - indicates that a sort must be performed on the composite table to order rows

Sortc_groupby     Y - indicates that a sort must be performed on the composite table to group rows

Tslockmode     Lock level applied to the new table, its table space or partitions
          If isolation level can be determined at bind time:
          IS     intent share lock
          IX     intent exclusive lock
          S     share lock
          U     update lock
          X     exclusive lock
          SIX     share with intent exclusive lock
          N     no lock

          If isolation level cannot be determined at bind time:

          NS     share lock (CS, RR, RS)
          NIS     Intent share lock (CS, RR, RS)
          NSS     Intent share lock (CS, RS), share lock (RR)
          SS     Intent share lock (CS, RS, UR) share lock (RR)

Prefetch     S     sequential prefetch
          L     list prefetch
          blank     prefetch not used initially

Column_fn_eval     Indicates when the column function is evaluated
          R     data retrieval time
          S     sort time

Mixopseq     Indicates sequence of multiple index operation

Access_degree     Number of parallel tasks utilized by the query

Access_pgroup_id  A sequential number identifying the parallel group accessing the new table

Parallelism_mode     I - i/o parallelism
               C- cpu parallelism
               X - Sysplex parallelism

Merge_join_cols   indicates the number of columns joined during a merge scan

Join_type     F     full outer join
          L     Left outer join
          blank     Inner join (or no join)

When_optimize     blank     at bind time
          B     At bind time, but will be re-optimised at run time
          R     At run time

Qblock_type     Type of SQL operation

          select     select
          selupd     select with for update of
          insert     
          update
          delete
          updcur     update where current of cursor
          delcur     delete where current of cursor
          corsub     correlated sub-query
          ncosub     non-correlated sub-query



Most efficient access path when a limited number of rows are to be retrieved:

Index only direct index look up
Direct index look up with data access
Index-only matching index scan
Index-only non-matching index scan
Matching clustered index access
Matching non-clustered index access
Non-Matching clustered index access
Non-Matching non-clustered index access
Partitioned tablespace scan skipping multiple partitions (partition scan)
Segmented tablespace scan
Simple table scan

The above sequence is reversed if most of the rows in the table are being accessed