zOs/WK/PKGUSECN

with pc as
( select  count(*) pkCoVe
        , collid, name
      from sysibm.sysPackage
      group by collid, name
)
, p2 as
( select  sum(pkCoVe) pkCoVe
        , count(*) pkCo
        , name
    from pc
    group by name
)
, p as
(
select    sum(pkCoVe) "pkgColVer"
        , sum(pkCo) "pkgCol"
        , count(*) "pkg"
    from p2
)
, dt as
(
select dLOCATION, dCollid, dName, dContoken
     , count(*) pkgDep
     , sum(case when dType = 'P' then 1 else 0 end) prev
     , sum(case when dType = 'O' then 1 else 0 end) orig
    from sysibm.sysPackDep
    group by dLocation, dCollid, dName, dContoken
)
, d as
(
select sum(pkgDep) "pkgDep"
     , sum(case when prev < 1 then 0 else 1 end) "prevPkg"
     , sum(case when orig < 1 then 0 else 1 end) "origPkg"
    from dt
)
select    p.*
        , ( select count(*)
              from sysibm.sysPackStmt) "packStmt"
        , d.*
        , current server
        , current timestamp
    from p, d
    with ur
;;;
with pc as
( select  count(*) pkCoVe
        , collid, name
      from sysibm.sysPackage
      group by collid, name
)
, p as
( select  sum(pkCoVe) pkCoVe
        , count(*) pkCo
        , name
    from pc
    group by name
)
select    sum(pkCoVe) "pkgColVer"
        , sum(pkCo) "pkgCol"
        , count(*) "pkg"
        , ( select count(*)
              from sysibm.sysPackStmt) "packStmt"
        , ( select count(*)
              from sysibm.sysPackDep) "packDep"
        , current server
        , current timestamp
    from p
    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