zOs/SQL/CATRTSCL

------------------------------------------------------------------------
-- cleanup RTS ==> delete rts rows with bad names
------------------------------------------------------------------------
-- select table rts with bad names
--
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
;
------------------------------------------------------------------------
-- select index rts with bad names or psId
--
select 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;
------------------------------------------------------------------------
-- delete table rts with bad names
--
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;
;
------------------------------------------------------------------------
-- delete index rts with bad names or psid
--
select * from old   table (
  delete
    from sysibm.sysIndexSpaceStats r
    where            strip(r.dbName) || '.' || strip(r.indexSpace)
          || '+' ||  strip(r.creator) || '.' || strip(r.name)
          || '/' ||  strip(char(r.psid))
        <> (select strip(i.dbName) || '.' || strip(i.indexSpace)
          || '+' ||  strip(i.creator) || '.' || strip(i.name)
          || '/' ||  strip(char(s.psid))
        from 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
        )
  )
    order by dbName, name, partition
;
rollback;
;