zOs/SQL/EXPLAIDI

--- 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;
/* -----------------
128 SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
    DI00206, DI00207 INTO :DI00200, :DI00201, :DI00202, :DI00203,
    :DI00204, :DI00205, :DI00206, :DI00207 FROM VDI002A1A A WHERE
    DI00201 <= :DIT002I_GDA AND DI00201 = ( SELECT MAX ( DI00201 ) FROM
     VDI002A1A WHERE DI00201 <= :DIT002I_GDA AND DI00202 = A.DI00202
    AND DI00203 = A.DI00203 AND DI00204 >= :DIT002I_GDA AND DI00205 =
    A.DI00205 ) AND DI00202 = :DIT002I_WIDT AND DI00203 = '  ' AND
    DI00204 >= :DIT002I_GDA AND DI00205 = :DIT002I_WID
189 SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
    DI00206, DI00207 INTO :DI00200, :DI00201, :DI00202, :DI00203,
    :DI00204, :DI00205, :DI00206, :DI00207 FROM VDI002A1A A WHERE
    DI00200 = ( SELECT MIN ( DI00200 ) FROM VDI002A1A WHERE DI00201 <=
    :DIT002I_GDA AND DI00202 = A.DI00202 AND DI00203 = A.DI00203 AND
    DI00204 >= :DIT002I_GDA AND DI00205 = A.DI00205 ) AND DI00201 <=
    :DIT002I_GDA AND DI00201 = ( SELECT MAX ( DI00201 ) FROM VDI002A1A
    WHERE DI00201 <= :DIT002I_GDA AND DI00202 = A.DI00202 AND DI00203 =
     A.DI00203 AND DI00204 >= :DIT002I_GDA AND DI00205 = A.DI00205 )
    AND DI00202 = :DIT002I_WIDT AND DI00203 = '  ' AND DI00204 >=
    :DIT002I_GDA AND DI00205 = :DIT002I_WID
----------------*/
explain plan set queryno = 128 for
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
       DI00206, DI00207   -- INTO :DIT002_OUT.
    from oa1p.VDI002A1A A
    WHERE DI00201 <= ? AND DI00201 =
          ( SELECT MAX ( DI00201 )
              FROM oa1p.VDI002A1A
              WHERE DI00201 <= ?
                  AND DI00202 = A.DI00202 AND DI00203 = A.DI00203
                  AND DI00204 >= ? AND DI00205 = A.DI00205
          )
        AND DI00202 = ? AND DI00203 = '  '
        AND DI00204 >= ? AND DI00205 = ?
;

explain plan set queryno = 1128 for
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
       DI00206, DI00207   -- INTO :DIT002_OUT.
    from oa1p.VDI002A1A A
    WHERE DI00201 <= ?
        AND DI00202 = ? AND DI00203 = '  '
        AND DI00204 >= ? AND DI00205 = ?
    order by di00201 desc
    fetch first 1 row only
;
explain plan set queryno = 2128 for
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
       DI00206, DI00207   -- INTO :DIT002_OUT.
    from oa1p.VDI002A1A A
    WHERE DI00201 <= ?
        AND DI00202 = ? AND DI00203 = '  '
        AND DI00204 >= ? AND DI00205 = ?
    order by di00200 asc, di00201 desc
    fetch first 1 row only
;

select * from  plan_view1
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;
rollback
;;;;
explain plan set queryno = 189 for
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
       DI00206, DI00207   -- INTO :DIT002_OUT.
    from oa1p.VDI002A1A A
    WHERE DI00200 = ( SELECT MIN ( DI00200 )
              from oa1p.VDI002A1A
              WHERE DI00201 <= ?
                  AND DI00202 = A.DI00202 AND DI00203 = A.DI00203
                  AND DI00204 >= ? AND DI00205 = A.DI00205
          )
        AND DI00201 <= ?
        AND DI00201  =
          ( SELECT MAX ( DI00201 )
              FROM oa1p.VDI002A1A
              WHERE DI00201 <= ?
                  AND DI00202 = A.DI00202 AND DI00203 = A.DI00203
                  AND DI00204 >= ? AND DI00205 = A.DI00205
          )
        AND DI00202 = ? AND DI00203 = '  '
        AND DI00204 >= ? AND DI00205 = ?
;
explain plan set queryno = 1189 for
with s as
(
SELECT *
    from oa1p.VDI002A1A A
    where DI00201 <= ?
        AND DI00202 = ? AND DI00203 = '  '
        AND DI00204 >= ? AND DI00205 = ?
    order by di00201 desc
    fetch first 1 row only
)
SELECT DI00200, DI00201, DI00202, DI00203, DI00204, DI00205,
       DI00206, DI00207   -- INTO :DIT002_OUT.
    from s a
    WHERE DI00200 = ( SELECT MIN ( DI00200 )
              from oa1p.VDI002A1A
              WHERE DI00201 <= ?
                  AND DI00202 = A.DI00202 AND DI00203 = A.DI00203
                  AND DI00204 >= ? AND DI00205 = A.DI00205
          )
;
with s as
(
select distinct DI00202, DI00203, DI00205, row_number() over () r
     FROM oa1p.VDI002A1A
     where di00203 = ''
)
select * from s
     order by mod(r, 223)
;x;
--- versionen, compiles und explains eines packages -------------------
select *
    from cmnbatch.plan_view0
    where   collid       = 'DI'
        and prog         = 'YDIT002'
    order by pcEx  desc, explain_time desc
    with ur
;
--- ausgewählte Explains analysieren
select *
 -- from cmnbatch.plan_view1
    from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det
    where     collid     = 'DI'
        and progName     = 'YDIT002'
        and explain_time = '2014-04-20-05.17.56.918285'
    order by collid, progName, version, explain_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
select *
    from cmnbatch.plan_viewPred
    where     collid     = 'DI'
        and progName     = 'YDIT002'
        and explain_time = '2014-04-20-05.17.56.918285'
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;