zOs/WK/PLANCNT

with pl as
( select trunc_timestamp(bind_time, 'MON') mon, count(*) plan
      from cmnbatch.plan_table
      group by trunc_Timestamp(bind_time, 'MON')
)
, pr as
( select trunc_timestamp(explain_time, 'MON') mon, count(*) pred
      from cmnbatch.DSN_PREDICAT_TABLE
      group by trunc_Timestamp(explain_time, 'MON')
)
, p2 as
( select value(pl.mon, pr.mon) mon, plan, pred
    from pl full join pr on pl.mon = pr.mon
)
, st as
( select trunc_timestamp(explain_time, 'MON') mon, count(*) stmt
      from cmnbatch.DSN_STATEMNT_TABLE
      group by trunc_Timestamp(explain_time, 'MON')
)
, p3 as
( select value(p2.mon, st.mon) mon, plan, pred , stmt
    from p2 full join st on p2.mon = st.mon
)
, fu as
( select trunc_timestamp(explain_time, 'MON') mon, count(*) func
      from cmnbatch.DSN_FUNCTION_TABLE
      group by trunc_Timestamp(explain_time, 'MON')
)
, p4 as
( select value(p3.mon, fu.mon) mon, plan, pred , stmt, func
    from p3 full join fu on p3.mon = fu.mon
)
, fi as
( select trunc_timestamp(explain_time, 'MON') mon, count(*) filt
      from cmnbatch.DSN_FILTER_TABLE
      group by trunc_Timestamp(explain_time, 'MON')
)
, p5 as
( select value(p4.mon, fi.mon) mon, plan, pred , stmt, func, filt
    from p4 full join fi on p4.mon = fi.mon
)
, co as
( select trunc_timestamp(explain_time, 'MON') mon, count(*) cost
      from cmnbatch.DSN_DETCOST_TABLE
      group by trunc_Timestamp(explain_time, 'MON')
)
, p6 as
( select value(p5.mon, co.mon) mon, plan, pred , stmt, func, filt, cost
    from p5 full join co on p5.mon = co.mon
)
select substr(char(mon), 1, 7), plan, pred , stmt, func, filt, cost
    from p6
    order by mon desc
;
select current timestamp, current server
    from sysibm.sysDummy1
;;;;
select 'cmnbatch', count(*), min(bind_time), max(bind_time)
       ,sum(case when opthint = '' then 0 else 1 end) optHi
       ,sum(case when hint_used = '' then 0 else 1 end) hinUs
    from cmnbatch.plan_table
union all select 'S100447 ', count(*), min(bind_time), max(bind_time)
       ,sum(case when opthint = '' then 0 else 1 end) optHi
       ,sum(case when hint_used = '' then 0 else 1 end) hinUs
    from S100447.plan_table
  with ur
;;;
select count(*),
       SUM(case when 0 < (select count(*)
            from sysibm.sysPackage k
            where k.location = '' and k.collid = p.collid
                and k.name = p.progName and k.version = p.version)
            then 1 else 0 end),
       substr(optHint, 1, 8) hint,  length(optHint),
       substr(hint_used, 1, 8) used,  length(hint_used),
       optHint, hint_Used
    from cmnbatch.plan_table p
    where collid = 'MF'
    group by optHint, hint_Used
    with ur