zOs/SQL/EXPLAIPK

--- temporary explain --------------------------------------------------
set current path  = 'OA1T';
set current sqlid = 'S100447';
create view A540769.v11 as
    SELECT dmRows,
           rdsRow,
           snRows,
           compCost,
           openCost,
         a.*
      FROM cmnbatch.PLAN_table a
      left JOIN cmnbatch.DSN_DetCost_TABLE d
        on    d.APPLNAME = A.APPLNAME
          AND d.PROGNAME = A.PROGNAME
          AND d.EXPLAIN_TIME = A.BIND_TIME
          AND d.QueryNO = A.QueryNO
          AND d.QBlockNO = A.QBlockNO
          AND d.PlanNo   = A.PlanNo
;
create view A540769.v12 as
    SELECT fosFmtE7(dmRows) dmRows,
           rdsRow,
           snRows,
           compCost,
           openCost,
         a.*
      FROM cmnbatch.PLAN_table a
      left JOIN cmnbatch.DSN_DetCost_TABLE d
        on    d.APPLNAME = A.APPLNAME
          AND d.PROGNAME = A.PROGNAME
          AND d.EXPLAIN_TIME = A.BIND_TIME
          AND d.QueryNO = A.QueryNO
          AND d.QBlockNO = A.QBlockNO
          AND d.PlanNo   = A.PlanNo
;
set current sqlid = 'A540769';
delete from A540769.plan_table;
explain plan set queryno = 1   for
    SELECT fosFmtE7(dmRows),
           rdsRow,
           snRows,
           compCost,
           openCost,
         a.*
      FROM cmnbatch.PLAN_table a
      left JOIN cmnbatch.DSN_DetCost_TABLE d
        on    d.APPLNAME = A.APPLNAME
          AND d.PROGNAME = A.PROGNAME
          AND d.EXPLAIN_TIME = A.BIND_TIME
          AND d.QueryNO   = A.QueryNO
          AND d.QBlockNO  = A.QBlockNO
          AND d.PlanNo    = A.PlanNo
      where   a.collid    = 'KE'
          and a.progName  = 'YKEA028'
          and a.bind_time = '2008-11-13-13.08.55.317001'
      order by collid, a.progName, version, bind_time,
               queryno, qBlockNo, planno, mixopSeq
      with ur
;
explain plan set queryno = 3   for
with w as
(
    SELECT fosFmtE7(dmRows) dmRows,
           rdsRow,
           snRows,
           compCost,
           openCost,
         a.*
      FROM cmnbatch.PLAN_view1 a
      left JOIN cmnbatch.DSN_DetCost_TABLE d
        on    d.APPLNAME = A.APPLNAME
          AND d.PROGNAME = A.PROGNAME
          AND d.EXPLAIN_TIME = A.BIND_TIME
          AND d.QueryNO = A.QueryNO
          AND d.QBlockNO = A.QBlockNO
          AND d.PlanNo   = A.PlanNo
)
    select *
      from w
      where     collid    = 'KE'
          and   progName  = 'YKEA028'
          and   bind_time = '2008-11-13-13.08.55.317001'
      order by collid,  progName, version, bind_time,
               queryno, qBlockNo, planno, mixopSeq
      with ur
;
explain plan set queryno = 11  for
    select *
      from A540769.v11
      where     collid    = 'KE'
          and   progName  = 'YKEA028'
          and   bind_time = '2008-11-13-13.08.55.317001'
      order by collid,  progName, version, bind_time,
               queryno, qBlockNo, planno, mixopSeq
      with ur
;
explain plan set queryno = 12  for
    select *
      from A540769.v12
      where     collid    = 'KE'
          and   progName  = 'YKEA028'
          and   bind_time = '2008-11-13-13.08.55.317001'
      order by collid,  progName, version, bind_time,
               queryno, qBlockNo, planno, mixopSeq
      with ur
;
explain plan set queryno = 13  for
    select fosFmtE7(dmRows) dmRows, v11.*
      from A540769.v11 v11
      where     collid    = 'KE'
          and   progName  = 'YKEA028'
          and   bind_time = '2008-11-13-13.08.55.317001'
      order by collid,  progName, version, bind_time,
               queryno, qBlockNo, planno, mixopSeq
      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
;;;;