zOs/SQL/EXPLAIPL

CREATE VIEW $cr.PLAN_VIEW5 AS
   with l as
   ( select collid, progName, max(r.bind_time) bind_time
             FROM $cr.PLAN_TABLE r
             group by collid, progName
   )
   select CASE WHEN METHOD = 'SORT  ' THEN '  '
               ELSE substr(right('         '
                    || strip(CHAR(S.PROCMS)),9), 1, 9) END AS MSEC
         , a.*
         ,    S.COST_CATEGORY,
              S.PROCMS, S.PROCSU, S.REASON
     FROM l
       join $cr.PLAN_VIEW1 a
       on      A.collid    = l.collid
           AND A.PROGNAME  = l.PROGNAME
           AND A.bind_time = l.bind_time
           LEFT OUTER JOIN
                $cr.DSN_STATEMNT_TABLE S
             ON    S."COLLID"     = A."COLLID"
               AND S.APPLNAME     = A.APPLNAME
               AND S.PROGNAME     = A.PROGNAME
               AND S.EXPLAIN_TIME = A.BIND_TIME
               AND S.QUERYNO      = A.QUERYNO
;
commit;
          ???????????????????????
  select *
      from cmnbatch.plan_view0
      where   collid       = 'AV01'
          and prog         = 'YAVX009'
      order by pcBi desc, bind_Time desc
      with ur
  ;;;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3   for
  select *
      from cmnbatch.plan_view0
      where   col          = 'AV01'
          and prg          = 'YAVX009'
      order by pcOrd desc, biTi desc
      with ur
  ;
select * from  plan_view1
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, bind_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by --collid, progName, applName, explain_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;
rollback
;;;;