--Query 1 intro:
--Use this query in QMF or SPUFI to view critical EXPLAIN table information (EXPLAIN tables can be created by the Optimization Service Center). --Replace the COLLID and PROGNAME predicates with the package of interest, or use the query "as is" preceded by an EXPLAIN statement.
--
--Be aware that this query mixes plan step and predicate information so plan step information is repeated.

WITH MAXTIME (COLLID, PROGNAME, QUERYNO, BIND_TIME) AS 
(SELECT COLLID, PROGNAME, QUERYNO, MAX(BIND_TIME) 
 FROM   PLAN_TABLE 
 WHERE  COLLID   = 'DSNESPCS' 
 AND    PROGNAME = 'DSNESM68' 
 GROUP  BY COLLID, PROGNAME, QUERYNO) 
 
SELECT SUBSTR(P.PROGNAME, 1, 8) AS PROGNAME  
      ,SUBSTR(DIGITS(P.QUERYNO), 6) CONCAT '-' 
       CONCAT SUBSTR(DIGITS(P.QBLOCKNO), 4) 
       CONCAT '-' CONCAT 
       SUBSTR(DIGITS(P.PLANNO), 4) AS QQP 
      ,SUBSTR(CHAR(P.METHOD), 1, 3) AS MTH 
      ,SUBSTR(CHAR(F.PREDNO), 1, 3) AS P_NO 
      ,SUBSTR(CHAR(P.MERGE_JOIN_COLS), 1, 3) AS MJC 
      ,SUBSTR(P.CREATOR, 1, 8) AS TBCREATOR 
      ,SUBSTR(P.TNAME, 1, 18) AS TBNAME 
      ,SUBSTR(P.CORRELATION_NAME, 1, 8) AS CORR_NM 
      ,DEC(D.ONECOMPROWS, 10, 1) AS ROWS_POST_FILTER 
      ,P.ACCESSTYPE AS ATYP 
      ,SUBSTR(P.ACCESSNAME, 1, 15) AS A_NM 
      ,P.INDEXONLY AS IXO 
      ,CHAR(P.MIXOPSEQ) AS MIX  
      ,CHAR(P.MATCHCOLS) MCOL 
      ,F.STAGE AS STAGE 
      ,DEC(E.FILTER_FACTOR, 11, 10) AS FF  
      ,E.BOOLEAN_TERM AS BT 
      ,SUBSTR(E.TEXT, 1, 30) AS PRED_TEXT30  
      ,P.SORTN_JOIN CONCAT P.SORTC_UNIQ CONCAT 
       P.SORTC_JOIN CONCAT P.SORTC_ORDERBY CONCAT 
       P.SORTC_GROUPBY AS NJ_CUJOG 
      ,P.PREFETCH AS PF 
      ,P.COLUMN_FN_EVAL AS CFE 
      ,P.PAGE_RANGE AS PGRNG 
      ,P.JOIN_TYPE AS JT 
      ,P.QBLOCK_TYPE AS QB_TYP  
      ,P.PARENT_QBLOCKNO AS P_QB 
      ,P.TABLE_TYPE AS TB_TYP 
      ,P.BIND_TIME AS B_TM 
FROM   PLAN_TABLE P 
INNER JOIN 
       MAXTIME    M  
ON     M.COLLID    = P.COLLID 
AND    M.PROGNAME  = P.PROGNAME 
AND    M.QUERYNO   = P.QUERYNO 
AND    M.BIND_TIME = P.BIND_TIME 
LEFT JOIN 
       DSN_FILTER_TABLE F 
ON     M.COLLID    = F.COLLID  
AND    M.PROGNAME  = F.PROGNAME 
AND    M.QUERYNO   = F.QUERYNO 
AND    P.QBLOCKNO  = F.QBLOCKNO 
AND    P.PLANNO    = F.PLANNO 
AND    M.BIND_TIME = F.EXPLAIN_TIME 
and    P.ACCESSTYPE Not IN ('MX', 'MI', 'MU') 
LEFT JOIN 
       DSN_PREDICAT_TABLE E 
ON     F.PROGNAME  = E.PROGNAME 
AND    F.QUERYNO   = E.QUERYNO 
AND    F.QBLOCKNO  = E.QBLOCKNO 
AND    F.PREDNO    = E.PREDNO 
AND    M.BIND_TIME = E.EXPLAIN_TIME 
LEFT JOIN 
TABLE  (SELECT MIN(X.ONECOMPROWS) AS ONECOMPROWS 
        FROM   DSN_DETCOST_TABLE X 
        WHERE  M.PROGNAME  = X.PROGNAME 
        AND    M.QUERYNO   = X.QUERYNO 
        AND    P.QBLOCKNO  = X.QBLOCKNO 
        AND    P.PLANNO    = X.PLANNO 
        AND    M.BIND_TIME = X.EXPLAIN_TIME) AS D 
ON 1=1 
ORDER  BY PROGNAME, B_TM, QQP, MIX, F.PREDNO  
 ;   


--Query Script 2:
--You can use this query script in SPUFI to get EXPLAIN information for a single statement or set of statements. This script separates the --information from the EXPLAIN tables so you see PLAN_TABLE information separate from predicate information (no duplicate PLAN_TABLE rows). Make sure --the four EXPLAIN tables are emptied before running your EXPLAIN followed by these statements:

SELECT SUBSTR(P.PROGNAME, 1, 8) AS PROGNAME  
      ,SUBSTR(DIGITS(P.QUERYNO), 6) CONCAT '-' 
       CONCAT SUBSTR(DIGITS(P.QBLOCKNO), 4) 
       CONCAT '-' CONCAT 
       SUBSTR(DIGITS(P.PLANNO), 4) AS QQP 
      ,SUBSTR(CHAR(P.METHOD), 1, 3) AS MTH 
      ,SUBSTR(CHAR(P.MERGE_JOIN_COLS), 1, 3) AS MJC 
      ,SUBSTR(P.CREATOR, 1, 8) AS TBCREATOR 
      ,SUBSTR(P.TNAME, 1, 18) AS TBNAME 
      ,SUBSTR(P.CORRELATION_NAME, 1, 8) AS CORR_NM 
      ,DEC(D.ONECOMPROWS, 10, 1) AS ROWS_POST_FILTER 
      ,P.ACCESSTYPE AS ATYP 
      ,SUBSTR(P.ACCESSNAME, 1, 15) AS A_NM 
      ,P.INDEXONLY AS IXO 
      ,CHAR(P.MIXOPSEQ) AS MIX  
      ,CHAR(P.MATCHCOLS) MCOL 
      ,P.SORTN_JOIN CONCAT P.SORTC_UNIQ CONCAT 
       P.SORTC_JOIN CONCAT P.SORTC_ORDERBY CONCAT 
       P.SORTC_GROUPBY AS NJ_CUJOG 
      ,P.PREFETCH AS PF 
      ,P.COLUMN_FN_EVAL AS CFE 
      ,P.PAGE_RANGE AS PGRNG 
      ,P.JOIN_TYPE AS JT 
      ,P.QBLOCK_TYPE AS QB_TYP  
      ,P.PARENT_QBLOCKNO AS P_QB 
      ,P.TABLE_TYPE AS TB_TYP 
      ,P.BIND_TIME AS B_TM 
FROM   PLAN_TABLE P 
LEFT JOIN 
TABLE  (SELECT MIN(X.ONECOMPROWS) AS ONECOMPROWS 
        from   DSN_DETCOST_TABLE X 
        where  P.PROGNAME  = x.PROGNAME 
        and    P.QUERYNO   = x.QUERYNO 
        and    P.QBLOCKNO  = x.QBLOCKNO 
        and    P.PLANNO    = x.PLANNO 
        and    P.BIND_TIME = x.EXPLAIN_TIME) AS D 
ON 1=1 
ORDER  BY PROGNAME, B_TM, QQP, MIX;

SELECT SUBSTR(F.PROGNAME, 1, 8) AS PROGNAME  
      ,SUBSTR(DIGITS(F.QUERYNO), 6) CONCAT '-' 
       CONCAT SUBSTR(DIGITS(F.QBLOCKNO), 4) 
       CONCAT '-' CONCAT 
       SUBSTR(DIGITS(F.PLANNO), 4) AS QQP,  
       PREDNO, STAGE, EXPLAIN_TIME AS B_TM 
FROM   DSN_FILTER_TABLE F
ORDER  BY PROGNAME, B_TM, QQP, PREDNO;

SELECT SUBSTR(P.PROGNAME, 1, 8) AS PROGNAME  
      ,SUBSTR(DIGITS(P.QUERYNO), 6) CONCAT '-' 
       CONCAT SUBSTR(DIGITS(P.QBLOCKNO), 4) AS QQ,
       PREDNO, FILTER_FACTOR, BOOLEAN_TERM AS BT, 
       JOIN, AFTER_JOIN AS AJ, ADDED_PRED AS AP, 
       REDUNDANT_PRED AS RP, 
       KEYFIELD, TEXT, EXPLAIN_TIME AS B_TM 
FROM   DSN_PREDICAT_TABLE P
ORDER  BY PROGNAME, B_TM, QQ, PREDNO;