zOs/SQL/CATPKGCN

set current application compatibility 'V11R1';
with p2 as
(
  select p.*
      , dense_rank() over(partition by location, collid, name
                          order by lastUsed desc) usSeq
      , row_number() over(partition by location, collid, name
                          order by pcTimestamp desc) pcSeq
    from sysibm.syspackage p
)
, p  as
(
  select case
           when sysentries <> 0 then '0sysEnt<>0'
           when timestamp > current timestamp - 1 month then '0creM'
           when lastUsed > current date - 1 month then '0lastM'
           when valid = 'N' and lastUsed < current date -  1 year
                          then '1validN'
           when pcSeq <= 2 then '0pc2'
           when lastUsed >= current date - 1 year and usSeq <= 2
                          then '0us2'
                          else '1else' end delRea
      , p2.*
    from p2
)
select left(delRea, 1) del, substr(delRea, 2) reason,  count(*) pkgVers
    , sum(case when pcSeq = 1 then 1 else 0 end) pkg
    , sum(case when pcSeq <= 2 then 1 else 0 end) pkg2V
    , sum(case when pcSeq <= 3 then 1 else 0 end) pkg3V
    , sum(case when usSeq <= 1 then 1 else 0 end) use1
    , sum(case when usSeq <= 2 then 1 else 0 end) use2
    , sum(case when usSeq <= 3 then 1 else 0 end) use3
    , sum(case when lastUsed > current date - 1 year
                   then 1 else 0 end) usedLastY
    from p
    group by rollup(left(delRea, 1), substr(delRea, 2))
;x;
, p as
(

select valid, operative, count(*) pkgVers
    , sum(case when pcSeq = 1 then 1 else 0 end) pkg
    , sum(case when pcSeq <= 2 then 1 else 0 end) pkg2V
    , sum(case when pcSeq <= 3 then 1 else 0 end) pkg3V
    , sum(case when usSeq <= 1 then 1 else 0 end) use1
    , sum(case when usSeq <= 2 then 1 else 0 end) use2
    , sum(case when usSeq <= 3 then 1 else 0 end) use3
    , sum(case when lastUsed > current date - 1 year
                   then 1 else 0 end) usedLastY
    , sum(case when pcSeq <= 3 then 1
               when lastUsed >= current date - 1 year
                   then 1 else 0 end) pkg3VorLaY
    , sum(case when pcSeq <= 2 then 1
               when lastUsed < current date - 1 year then 0
               when usSeq <= 2 then 1
               else 0 end) pkg2V2ULaY
    , sum(case when sysEntries  = 0 then 0 else 1 end) sysEntNot0
    from p
    group by rollup(valid, operative)
;x;
select *
    from sysibm.syspackage p
    where sysEntries <> 0
;x;
    group by rollup(pcSeq)
    order by pcSeq
    ;x;   -----------
select substr(creator, 1, 8) cre, substr(owner, 1, 8) owner, count(*)
    from sysibm.syspackage
    where timestamp > '2012-08-01-00.00.00'
    group by creator, owner
    with ur
    ;x;
with m (m, x) as
(  select trunc_timestamp(current timestamp, 'mon') , 0
       from sysibm.sysDummy1
   union all select m - 1  month, x+1
       from m where x < 30
)
, t (m, cTst) as
(
select trunc_timestamp(timestamp, 'mon'), count(*)
    from sysibm.syspackage
    group by trunc_timestamp(timestamp, 'mon')
)
, p (m, cPC) as
(
select trunc_timestamp(pctimestamp, 'mon'), count(*)
    from sysibm.syspackage
    group by trunc_timestamp(pctimestamp, 'mon')
)
select substr(char(m.m), 1, 7) mon, cTst, cPc
    from m left join p on m.m = p.m
           left join t on m.m = t.m
    order by m.m desc
    with ur
;X;
select trunc_timestamp(timestamp, 'mon'), count(*)
    from sysibm.syspackage
    group by trunc_timestamp(timestamp, 'mon')
    order by trunc_timestamp(timestamp, 'mon') desc
    with ur