zOs/SQL/CATUSED

with ts as
(
  select dbName db, name ts, createdTS cre , alteredTS alt
        ,dbid, psid
    from sysibm.sysTablespace
    where dbName = 'MF01A1P'
)
, tb as
(
  select case when type = 'A' then 'al'
              when type = 'V' then 'vw'
                              else 'tb' end ty
        , creator cr, name tb, createdTS cre , alteredTS alt
    from sysIbm.sysTables
    where (dbName, tsName) in (select db, ts from ts)
)
, r0 as
(
   select *
     from sysibm.sysTableSpaceStats
     where (dbName, name, dbid, psid) in
           (select db, ts, dbid, psid  from ts)
)
, rt (ty, cnt, alt) as
(
  select 'rt', count(*), max(updateStatsTime)
    from r0
    where updateStatsTime is not null
  union all select 'rt copyUpd', count(*), max(COPYUPDATETIME)
    from r0
    where copyUpdateTime is not null
  union all select 'rt loadRep', count(*), max(LOADRLASTTIME)
    from r0
    where LOADRLASTTIME is not null
  union all select 'rt reoUpd', count(*)
         , max(max(value(LOADRLASTTIME, REORGLASTTIME)
             ,value(REORGLASTTIME, LOADRLASTTIME)))
    from r0
    where REORGINSERTS > 0 or REORGDELETES >0
       or REORGUPDATES > 0 or REORGMASSDELETE > 0
)
, ix as
( select creator cr, name ix, createdTS cre , alteredTS alt
    from sysibm.sysIndexes
    where (tbCreator, tbName) in
          (select cr, tb from tb where ty = 'tb')
)
, i0 as
(
   select *
     from sysibm.sysIndexSpaceStats
     where (creator, name)
         in (select cr, ix from ix)
)
,ri (ty, cnt, alt) as
(
   select 'ri', count(*), max(updateStatsTime)
     from i0
   union all select 'ri lastUse', count(*), timestamp(max(lastUsed))
     from i0
     where lastUsed is not null
)
, pk as
(
  select lastUsed, Timestamp, bindTime, pcTimestamp, collid, name
    from sysIbm.sysPackage pk
    where  (location, collid, name, conToken)
        in ( select dLocation, dCollid, dName, dConToken
               from sysIbm.sysPackDep
               where bType in ('T', 'A', 'G', 'M', 'V')
                   and (bQualifier, bName)
                   in (select cr, tb from tb)
             union all select dLocation, dCollid, dName, dConToken
               from sysIbm.sysPackDep
               where bType in ('P', 'R')
                   and (bQualifier, bName)
                   in (select db, ts from ts)
           )
)
, u (type, count, altered, created) as
(
  select 'ts', count(*), max(alt), max(cre)
    from ts
  union all select ty, count(*), max(alt), max(cre)
    from tb
    group by ty
  union all select ty, cnt, alt, cast(null as timestamp)
    from rt
  union all select 'ix', count(*), max(alt), max(cre)
    from ix
  union all select ty, cnt, alt, cast(null as timestamp)
    from ri
  union all select 'pk bind/cr', count(*), max(bindTime), max(timestamp)
    from pk
  union all select 'pk preComp', count(*), max(pcTimestamp)
       , cast(null as timestamp)
    from pk
    where pcTimestamp > '1000-01-01-00.00.00'
  union all select 'pk lastUse', count(*), timestamp(max(lastUsed))
       , cast(null as timestamp)
    from pk
    where lastUsed > '01.01.1000'
)
, v (type, count, altered, created) as
(
  select type
         , substr(right('           ' || count, 8), 1, 8)
         , altered, created
    from u
  union all ( select 'pk newest', substr(name, 1, 8)
           , timestamp(max(lastUsed)), max(pcTimestamp)
    from pk
    where lastUsed > current date - 90 days
    group by name
    order by 3 desc, 2
    fetch first 15 rows only )
)
select * from v