zOs/SQL/PLANCLES

-- plan table cleanup
-- query um zu behaltende Explains zu bestimmen
--
with p2 as   -- welche Package Version ist wann gültig
             -- von CRE bis pkNx
(
  select collid col, name pkg, version vers, timestamp cre
    , max(timestamp(lastUsed)
      , value(( select min(n.timestamp)
          from sysibm.sysPackage n
          where n.location = p.location
            and n.collid = p.collid
            and n.name   = p.name
            and n.timestamp > p.timestamp
      ), current timestamp)) pkNx
    from sysibm.sysPackage p
    where location = '' and collid = 'NI' -- and name = 'PC5120'
)
, e2 as      -- welche explain_time joinen, rows zählen
(
  select col, pkg, vers, cre, pkNx
      , e.explain_time exTi
      , count(*) exCnt
      , max(case when optHint is not null and optHint<>'' then optHint
                 when hint_Used is not null and hint_used <> ''
                              and hint_used <> 'APREUSE' then hint_used
                 else '' end) optHi
    from p2 p join cmnBatch.plan_table e
      on p.col = e.collid and p.pkg = e.progName
          and p.vers = e.version
    group by col, pkg, vers, cre, pkNx, explain_time
)
, e3 as      -- nächste explain_time suchen
(
  select e2.*
      , ( select min(explain_time)
            from cmnBatch.plan_table n
            where e2.col = n.collid and e2.pkg = n.progName
                and e2.vers = n.version and e2.exTi < n.explain_time
         ) exNx
  from e2
)
, e4 as      -- herausfinden ob behalten oder löschen
(
  select
      case when exTi > pkNx then '0 pNx'
           when exTi < cre then '0 cre'
           when optHi <> '' then '1 optHi'
           when exNx is Null then '1 nxNull'
           when exNx >= pkNx then '1 nx>pNx'
           when days(exNx) - days(exTi) > 30 then '1 >30day'
           when trunc_timestamp(exTi, 'mon')
             <> trunc_timestamp(exNx, 'mon') then '1 mon'
           else '0 else'
      end keep
    , days(exTi) exTID
    , days(exNx) exNxD
    , e3.*
    from e3
)
, eq as      -- queryNo joinen
(
  select exTi, queryNo, count(*) eCnt, keep
    from e4 p join cmnBatch.plan_table e
      on p.col = e.collid and p.pkg = e.progName
          and p.vers = e.version
 -- where keep like '1%'
    group by exTi, queryNo, keep
)
select count(*), sum(eCnt) eCnt, keep
    from eq
    group by keep
;x;
select exTi, exNx, e4.* from e4
    order by col, pkg, vers, cre, pkNx, exTi
;x;
, e (col, pkg, vers, cre, pkNx, exTi, exCnt, optHi, exNxt
    , keep, keLa, l) as
;x;
select count(distinct explain_time), count(*), collid, progName
    from cmnBatch.plan_table
    group by collid, progName
    order by 1 desc
    fetch first 1000 rows only
    with ur
;x;
with p as
(
  select collid col, name pkg, version vers, timestamp cre
    , max(timestamp(lastUsed)
      , value(( select min(n.timestamp)
          from sysibm.sysPackage n
          where n.location = p.location
            and n.collid = p.collid
            and n.name   = p.name
            and n.timestamp > p.timestamp
      ), current timestamp)) pNxt
    from sysibm.sysPackage p
 -- where collid = '$col'
)
, e (col, pkg, vers, cre, pNxt, exTi, exCnt, optHi, exNxt
    , keep, keLa, l) as
(
  select col, pkg, vers, cre, pNxt
      , ( select min(e.explain_time)
             from cmnBatch.plan_table e
             where  p.col = e.collid and p.pkg = e.progName
           and p.vers = e.version )
      , cast( null as int)
      , cast( ''   as char(8))
      , cast( null as timestamp)
      , -9
      , cast( null as timestamp)
      , 0
    from p
  union all select col, pkg, vers, cre, pNxt, exTi
      , ( select count(*)
            from cmnBatch.plan_table t
            where  e.col = t.collid and e.pkg = t.progName
                and e.vers = t.version and e.exTi = t.explain_time )
      , ( select max(case when optHint is not null and optHint<> ''
                              then optHint
                          when hint_Used is not null and hint_used <> ''
                              and hint_used <> 'APREUSE' then hint_used
                               else '' end)
            from cmnBatch.plan_table t
            where  e.col = t.collid and e.pkg = t.progName
                and e.vers = t.version and e.exTi = t.explain_time )
      , ( select min(explain_time)
            from cmnBatch.plan_table t
            where  e.col = t.collid and e.pkg = t.progName
                and e.vers = t.version and e.exTi < t.explain_time )
      , -8
      , keLa
      , l+1
    from e
    where e.l < 9999 and keep = -9
  union all select col, pkg, vers, cre, pNxt, exTi, exCnt, optHi
      , exNxt
      , case when keLa is null and pNxt <= exNxt then -1
             when pNxt <= exTi then -2
             when optHi <> ''              then -1
             when exNxt is null or exNxt > exTi + 10 days then -1
             when keLa is null and cre < exTi - 10 days then -1
             when keLa < exTi - 20 days then -1
             else -2 end
      , keLa
      , l+1
    from e
    where l < 9999 and keep = -8
  union all select col, pkg, vers, cre, pNxt, exTi, exCnt, optHi
      , exNxt
      , keep + 2
      , case when keep = -2 then keLa
             when optHi <> '' then keLa
             else exTi end
      , l+1
    from e
    where e.l < 9999 and keep in (-1, -2)
  union all select col, pkg, vers, cre, pNxt, exNxt
      , cast(null as int), ''
      , cast(null as timestamp)
      , -9
      , keLa
      , l+1
    from e
    where l < 9999 and keep in (1, 0) and exNxt < pNxt
)
select substr(strip(col) || '.' || strip(pkg) || '#' || vers, 1, 30)
      , exTi, keep, l, exCnt, optHi, exNxt, keLa, cre, pNxt
    from e
    where keep >= 0 and exTi is not null
    order by col, pkg, cre, l