zOs/SQL/EXPLAIPP

select *
    from cmnbatch.plan_view0 v
    where collid = 'MF' and prog = 'MF5010'
;x;
with v as
(
select row_number() over(partition by collid, prog
                         order by pcBi desc) rn
       , v.*
    from cmnbatch.plan_view0 v
    where collid       = 'MF'
   --   and prog         = 'MF5210'
)
select (select count(*)
            from sysibm.sysPackage a
            where a.location = ''
              and a.collid = v.collid and a.name = v.prog
       ) vrs
       , v.*
    from v where rn = 1
    order by collid, prog, pcBi  desc, bind_Time desc
    with ur
;  x;
--- ausgewählte Explains analysieren
select *
--- versionen, compiles und explains eines packages -------------------
select *
    from cmnbatch.plan_view0
    where   collid       = 'MF'
        and prog         = 'MF5210'
    order by pcBi  desc, bind_Time desc
    with ur
;  x;
--- ausgewählte Explains analysieren
select *
 -- from cmnbatch.plan_view1
    from cmnbatch.plan_view2
 -- from cmnbatch.plan_view2Det
    where     collid     = 'MF'
        and progName     = 'MF7010'
        and bind_time    = '2011-08-30-14.14.59.234601'
    order by collid, progName, version, bind_time,
             queryno, qBlockNo, planno, mixopSeq
    with ur
;
select *
    from cmnbatch.plan_viewPred
    where     collid     = 'MF'
        and progName     = 'MF7010'
        and bind_time    = '2011-08-30-14.14.59.234601'
    order by collid, progName, applName, bind_time,
             queryNo, qBlockNo, planno,
             stage, predNo
    with ur
;