zOs/SQL/CATIRDUP

select count(*), dbName, indexSpace, partition, instance
    from sysibm.sysIndexSpaceStats
    group by dbName, indexSpace, partition, instance
    order by 1 desc, dbName, indexSpace, partition, instance
    fetch first 100 rows only
    with ur
;X;
delete from  sysibm.sysIndexSpaceStats q
  where dbName like 'DSN8D9%'
    and exists (select 1 from sysibm.sysIndexSpaceStats a
      where     a.dbName = q.dbName
            and a.indexSpace = q.indexSpace
            and a.partition  = q.partition
            and a.instance   = q.instance
            and a.updatestatsTime > q.updateStatstime
        )
;
with d as
(
  select count(*) cnt, dbName, indexSpace, partition, instance
    from sysibm.sysIndexSpaceStats
    group by dbName, indexSpace, partition, instance
    having count(*) > 1
)
, s as
(
   select row_number() over (partition by
                 d.dbName, d.indexSpace, d.partition, d.instance
                 order by r.updateSTatsTime desc) rn
      , cnt, r.*
    from sysibm.sysIndexSpaceStats r
      join d on r.dbName = d.dbName
            and r.indexSpace = d.indexSpace
            and r.partition  = d.partition
            and r.instance   = d.instance
    order by cnt, d.dbName, d.indexSpace, d.partition, d.instance
             , r.updateStatsTime desc
)
select * from s where rn > 1
    with ur
;
rollback
;x;
select *
   from OA1P.TQZ007GBGRIXSTATS n
   where rz = '?' and dbSys = '?' and dbName = 'DSN8D91A'
         and ts = 'DSN8S91E'
   order by indexSpace
;c;
        /*
          2 DSN8D91A DSN8S91E XEMP1    1 1
          2 DSN8D91A DSN8S91D XDEPT3   0 1
          2 DSN8D91A DSN8S91D XDEPT2   0 1
          2 DSN8D91A DSN8S91E XEMP1    4 1
          2 DSN8D91A DSN8S91E XEMP1    3 1
          2 DSN8D91A DSN8S91E XEMP1    2 1
          2 DSN8D91A DSN8S91S XPARTS   0 1
          2 DSN8D91A DSN8S91P XPROJAC1 0 1
          2 DSN8D91A DSN8S91P XPROJ1   0 1
          2 DSN8D91A DSN8S91P XEMPPROJ 0 1
          2 DSN8D91A DSN8S91P XACT2    0 1
          2 DSN8D91A DSN8S91E XEMP1    5 1
          2 DSN8D91X XPRO0000 IRNODEID 0 1
          2 DSN8D91X DSN8S91X SUPPLIER 0 1
          2 DSN8D91X DSN8S91X PRODUCTS 0 1
          2 DSN8D91X DSN8S91X IRDOCIDS 0 1
          2 DSN8D91X DSN8S91X IRDOCIDP 0 1
          2 DSN8D91X DSN8S91X IRDOCIDC 0 1
          2 DSN8D91X DSN8S91X INVENTOR 0 1
          2 DSN8D91X DSN8S91X CUSTOMER 0 1
          2 DSN8D91X DSN8S91X CATALOGR 0 1
          2 DSN8D91P DSN8S91Q XMAPRTBL 0 1
          2 DSN8D91P DSN8S91C XOPTVAL1 0 1
          2 DSN8D91P DSN8S91C XDSPTXT1 0 1
          2 DSN8D91A DSN8S91D XDEPT1   0 1
        */
with s as
(
select  'a' STATE
      , 'RZX' RZ
      , 'DE0G' DBSYS
      , '2014-07-15-14.39.25.139972' LOADTS
      , INDEXTYPE
      , COMPRESS
      , IXPARTS
      , IXPGSZ
      , PIECESIZE
      , PIECEGB
      , LIMGB
      , TBCREATOR
      , TBNAME
      , TS
      , TSTY
      , TSPARTS
      , TSCLONE
      , TSINST
      , TSDSSIZE
      , TSDSGB
      , TSLimGb
      , TSLimPart
      , TSPGSZ
      , UPDATESTATSTIME
      , NLEVELS
      , NPAGES
      , NLEAF
      , NACTIVE
      , SPACE
      , EXTENTS
      , LOADRLASTTIME
      , REBUILDLASTTIME
      , REORGLASTTIME
      , REORGINSERTS
      , REORGDELETES
      , REORGAPPENDINSERT
      , REORGPSEUDODELETES
      , REORGMASSDELETE
      , REORGLEAFNEAR
      , REORGLEAFFAR
      , REORGNUMLEVELS
      , STATSLASTTIME
      , STATSINSERTS
      , STATSDELETES
      , STATSMASSDELETE
      , COPYLASTTIME
      , COPYUPDATEDPAGES
      , COPYCHANGES
      , COPYUPDATELRSN
      , COPYUPDATETIME
      , LASTUSED
      , IBMREQD
      , DBID
      , ISOBID
      , PSID
      , PARTITION
      , INSTANCE