zOs/SQL/CATRTS

with r as
(
  select r.*, s.pgSize
 ,  case when r.nPages > 0  then 'p'
         when r.nPages = 0  then '0'
         when r.nPages < 0  then '-'
                            else '?' end
 || case when r.nPages < r.nActive then '<'
         when r.nPages = r.nActive then '='
         when r.nPages > r.nActive then '>'
                                   else '?' end
 || case when r.nActive > 0  then 'a'
         when r.nActive = 0  then '0'
         when r.nActive < 0  then '-'
                             else '?' end
 || case when r.nActive < r.space * s.pgSize then '<'
         when r.nActive = r.space * s.pgSize then '='
         when r.nActive > r.space * s.pgSize then '>'
                                             else '?' end
 || case when r.space   > 0  then 's'
         when r.space   = 0  then '0'
         when r.space   < 0  then '-'
                             else '?' end  txt
    from sysibm.sysTableSpaceStats r
         join sysibm.sysTableSpace s
           on r.dbid = s.dbid and r.psid = s.psid
)
select count(*), txt
     , sum(bigint(R.nPages)) nPag
     , sum(bigint(R.nActive)) nACt
     , sum(bigint(r.nPages)*pgSize) kPag
     , sum(bigint(r.nActive)*pgSize) kAct
     , sum(r.space) spc
     , min(strip(dbName) ||'.'|| strip(name) ||'#'|| partition) dbTsMin
     , max(strip(dbName) ||'.'|| strip(name) ||'#'|| partition) dbTsMax
    from r
    group by txt
    order by txt
    with ur
;x;
select count(*), DBID, psid, partition,instance, updatestatstime
     from sysibm.sysTableSpaceStats
     group by DBID, psid, partition,instance, updatestatstime
     order by 1 desc
     fetch first 100 rows only
     with ur
;x;;;;;
select  COPYUPDATEDPAGES ,
        COPYCHANGES ,
        COPYUPDATELRSN, timestamp(copyUpdateLrsn || x'0000') ,
        COPYUPDATETIME,
        r.*
    from sysibm.sysTableSpaceStats r
    where copyUpdateLrsn is not null
       or copyUpdateTime is not null
       or COPYUPDATEDPAGES <> 0
       or COPYCHANGES <> 0
    order by copyUpdateTime asc
    fetch first 1000 rows only
    with ur
;x;
select dbName, name, partition, totalrows, space
    from sysibm.sysTablespaceStats
    where dbName = 'WI02A1T' and name = 'A100H'
select dbName, name, partition, totalrows, space
    from sysibm.sysTablespaceStats
    where dbName = 'WI02A1T' and name = 'A100H'
;x;
select count(*), sum(r.space), count(r.space),
    min(pageSave), max(PageSave)
    from sysibm.sysTableSpaceStats r, sysibm.sysTablePart p
    where r.dbName = 'MF01A1P' and r.Name = 'A150A'
        and r.dbName = p.dbName and r.name = p.tsName
        and r.partition = p.partition
         and r.partition >= 133
    with ur
;
;elect substr(strip(r.dbName) || '.' || strip(r.indexSpace)
   || '+' ||  strip(r.creator) || '.' || strip(r.name)
   || '/' ||  strip(char(r.psid))  , 1, 50) "rts",

       substr(strip(i.dbName) || '.' || strip(i.indexSpace)
   || '+' ||  strip(i.creator) || '.' || strip(i.name)
   || '/' ||  strip(char(s.psid))  , 1, 50) "i",
    r.*
    from sysibm.sysIndexSpaceStats r,
         sysibm.sysIndexes i,
         sysibm.sysTables  t,
         sysibm.sysTableSpace s
    where r.dbId = i.dbid and r.isobid = i.isoBid
        and i.tbCreator = t.creator and i.tbName = t.name
        and t.dbName = s.dbName and t.tsName = s.name
         and (r.dbName <> i.dbName or r.indexSpace <> i.indexSpace
           or r.creator<> i.creator or r.name <> i.name
           or r.psid   <> s.psid)
    order by r.dbName, r.name
    with ur
;x;
select substr(strip(s.dbName) || '.' || strip(s.name), 1, 20) "sDbTs",
       substr(strip(r.dbName) || '.' || strip(r.name), 1, 20) "rtsDbTs",
    r.*
    from sysibm.sysTableSpaceStats r,
         sysibm.sysTableSpace s
    where r.dbId = s.dbid and r.psid = s.psId
         and (r.dbName <> s.dbName or r.name <> s.name)
    order by r.dbName, r.name
    with ur
;x;
select * from old   table (
delete
    from sysibm.sysTableSpaceStats r
    where        (strip(r.dbName) || '*' || strip(r.name))
       <> (select strip(s.dbName) || '*' || strip(s.name)
         from sysibm.sysTableSpace s
          where r.dbId = s.dbid and r.psid = s.psId)
)
    order by   dbName,   name
;
rollback;
;x;
         and (r.dbName <> s.dbName or r.name <> s.name)
    order by r.dbName, r.name
delete from sysibm.sysTableSpaceStats r
       substr(strip(r.dbName) || '.' || strip(r.name), 1, 20) "rtsDbTs",
    r.*
    from sysibm.sysTableSpaceStats r,
         sysibm.sysTableSpace s
    where r.dbId = s.dbid and r.psid = s.psId
         and (r.dbName <> s.dbName or r.name <> s.name)
    order by r.dbName, r.name