zOs/SQL/PKGCLEAS

set current application compatibility 'V11R1';
with p0 as
( ---  ignore packages created in last two weeks ----------------------
  select case when timestamp < current timestamp - 15 days
              then 0 else 1 end creNew
      , 20 win, p.*
    from sysibm.syspackage p
)
, p1 as
( --- end of first window before creNew=1
  select max(case when creNew = 0 then timestamp end)
             over(partition by location, collid, name) cre1
       , max(case when creNew = 0 then pcTimestamp end)
             over(partition by location, collid, name) pcT1
       , max(case when creNew = 0 then lastUsed    end)
             over(partition by location, collid, name) use1
       , p0.*
    from p0
)
, p2 as
( --- end of second window before begin of first window ---------------
  select max(case when cre1 < '1000-01-01-00.00.00' then null
                  when creNew = 0 and timestamp   < cre1 - win days
                  then timestamp end)
             over(partition by location, collid, name) cre2
       , max(case when pcT1 < '1000-01-01-00.00.00' then null
                  when creNew = 0 and pcTimestamp < pcT1 - win days
                      then pcTimestamp end)
             over(partition by location, collid, name) pcT2
       , max(case when use1 < '01.01.1000' then null
                  when creNew = 0 and lastUsed    < use1 - win days
                      then lastUsed end)
             over(partition by location, collid, name) use2
       , p1.*
    from p1
)
, pS as
( --- seqNumber of each window ----------------------------------------
  select
        case when creNew = 1 then 0
             when cre1 is null or cre1 < '1000-01-01-00.00.00' then 1
             when timestamp >= cre1 - win days then 1
             when cre2 is null or cre2 < '1000-01-01-00.00.00' then 2
             when timestamp >= cre2 - win days then 2
             else 9 end creSeq
      , case when creNew = 1 then 0
             when pcT1 is null or pcT1 < '1000-01-01-00.00.00' then 1
             when pcTimestamp >= pcT1 - win days then 1
             when pcT2 is null or pcT2 < '1000-01-01-00.00.00' then 2
             when pcTimestamp >= pcT2 - win days then 2
             else 9 end pcTSeq
      , case when creNew = 1 then 0
             -- ignore PKGs never used, not after Db2V9, not in 3 years
             when lastUsed < current date - 3 years then 9
             when use1 is null or use1 < '01.01.1000' then 1
             when lastUsed >= use1 - win days then 1
             when use2 is null or use2 < '01.01.1000' then 2
             when lastUsed >= use2 - win days then 2
             else 9 end useSeq
      , p2.*
    from p2
)
, pR as
( --- reason why to keep or deactivate a package ----------------------
  select case
           when sysentries > 0 then 'k1sysEnt>0' -- already deact
           when sysentries < 0 then 'k2sysEnt<0' -- should not exist
           when creNew  = 1    then 'k3cre>15d'  -- newly created
           when pctSeq <= 2    then 'k4pcT<=2'   -- last 2 compile
           when creSeq <= 2    then 'k5cre<=2'   -- last 2 compile
           when useSeq <= 2    then 'k6use<=2'   -- last 2 lastUsed
           when lastUsed > current date - 4 month
                               then 'k7use<4Mo'   -- wait until next zuegelschub
                               else 'd0deact' end dsr
      , pS.*
    from pS
)
, p as
( --- decode dsr ------------------------------------------------------
  select left(dsr, 1) deact, substr(dsr, 3) reason, pR.*
     from pR
)
$** select * from p order by cnt desc fetch first 100 rows only
select '?rz/?dbSys' rzDb
     , deact
     , reason
     , count(*) pkgVers
     , count(distinct strip(collid) || '.' || strip(name)) pkg
     , sum(case when pctSeq=1 then 1 else 0 end) pcT1
     , sum(case when pctSeq=2 then 1 else 0 end) pcT2
     , sum(case when useSeq=1 and lastUsed > current date - 3 year
                then 1 else 0 end) use1
     , sum(case when useSeq=2 and lastUsed > current date - 3 year
                then 1 else 0 end) use2
     , sum(case when valid = 'N' then 1 else 0 end) validNo
     , sum(case when operative = 'N' then 1 else 0 end) operativeNo
  from p
  group by grouping sets((deact, dsr, reason), (deact), ())
  having deact is null or reason is not null
         or (reason is null and count(distinct reason) <> 1)
  order by value(deact, ' '), value(substr(dsr, 2, 1), '')
  with ur
  ;