zOs/WK/PKGUSEC3

with pc as
( select  count(*) pkCoVe
        , collid, name
      from sysibm.sysPackage
      group by collid, name
)
, p as
( select  sum(pkCoVe) pkCoVe
        , count(*) pkCo
        , left(collid, 2) co2, name
    from pc
    group by left(collid, 2), name
)
select    sum(pkCoVe) "pkgColVer"
        , sum(pkCo) "pkgCol"
        , count(*) "pkg"
        , substr(co2, 1, 2) "co2"
        , current server
        , current timestamp
    from p
    group by co2
    order by 1 desc
    with ur
;;;
with v as
(
select count(*) all
      , sum(case when pctimestamp > '2011-01-01-00.00.00'
             then 1 else 0 end) "newer2011"
      , sum(case when pctimestamp > '2010-01-01-00.00.00'
             then 1 else 0 end) "newer2010"
      , max(min(count(*), 1),
             sum(case when pctimestamp > '2011-01-01-00.00.00'
                 then 1 else 0 end)) "newer2011max1"
      , max(min(count(*), 2),
             sum(case when pctimestamp > '2011-01-01-00.00.00'
                 then 1 else 0 end)) "newer2011max2"
      , max(min(count(*), 3),
             sum(case when pctimestamp > '2011-01-01-00.00.00'
                 then 1 else 0 end)) "newer2011max3"
      , location, collid, name
    from sysibm.syspackage k
    group by location, collid, name
)
select
    ( select count(*)
          from sysibm.syspackage c
          where c.location = v.location and c.collid = v.collid
                  and c.name = v.name
              and not exists
              ( select 1
                  from sysibm.syspackage r
                  where r.location = c.location and r.collid = c.collid
                      and r.name = c.name
                      and r.timestamp > c.timestamp
                      and r.timestamp < current timestamp - 7 days
    )         ) "1bef7"
    , v.*
    from v
    where collid like 'AV%' and location = ''
    order by 1 desc, location, collid, name
    fetch first 100 rows only
    with ur
;;
select  count(*) pkgs
      , sum(all) pkgVers
      , sum("newer2011max1") "newer2011max1"
      , sum("newer2011max2") "newer2011max2"
      , sum("newer2011max3") "newer2011max3"
      , sum("newer2011") "newer2011"
      , sum("newer2010") "newer2010"
    from v
    with ur