zOs/WK/PKGUSEVE

with k as
(
select min(timestamp) creTst
     , min(pctimestamp) pcTst
     , count(*) cols, name, hex(contoken) conToken, min(version) vers
    from sysibm.syspackage k
    where k.collid like 'AV%'
    group by name, contoken
)
, o as
(
select smallint(row_number()
                over (partition by k.name order by creTst desc)) rnr
    ,  u.lastUsed, k.*
    from k
      left join A540769.tPkgUsd u
        on u.pkg = k.name  and u.conToken = k.contoken
)
select
      days(pr.lastUsed) - days(c.creTst)
    , date((select max(tstTo) from A540769.tPkgUsPdb p
                        where c.name = p.pkg)) lastPDB
    , c.*, pr.*
    from o c
      left join o pr
        on pr.name = c.name and pr.rnr = c.rnr+1
    where days(pr.lastUsed) - days(c.creTst) > 1
       and (exists (select tstTo from A540769.tPkgUsPdb p
                               where c.name = p.pkg)
          or pr.lastUsed is not null)
    order by
            year(c.cretst) desc, 1 desc,
            c.name, c.rnr
    with ur
;;;
select substr(strip(k.collid) || '.' || k.name, 1, 16)
     , substr(k.version, 1, 16) version
     , k.pcTimestamp
     , u.lastUsed, p.*, k.*
    from sysibm.syspackage k
      left join A540769.tPkgUsPdb p
        on p.col = k.collid and p.pkg = k.name
      left join A540769.tPkgUsd u
        on u.pkg = k.name  and u.conToken = hex(k.contoken)
    where k.collid like 'AV01%'
    order by k.collid, k.name, k.pcTimestamp desc
    fetch first 100 rows only
    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
    group by location, collid, name
)
select *
    from v
    order by 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
    where collid like 'AV%' and location = ''
    with ur