zOs/SQL/GBGRCLEA

-- gbGr cleanup
--     check / delete duplicate in tqz006gbgrTsStats
--
-- ==> benutze gbgrCleC, das alle rows zwischen erstem und letzem loadTS
--         der duplicate rausputzt,
--         sonst reproduziert sich der Fehler wieder beim nächsten Load
--
--     duplicates (rz, ..., updateSTatsTime) but with distinct loadTS
--     (1) select count
--     (2) delete
--     switch  zwischen (1) und (2) durch anpassen der mit --??
--                                  markierten stellen
--
delete  --??
--?? select count(*), rz, dbSys, loadTs, state  --??
    from OA1P.TQZ006GBGRTSSTATS where  state = 'a'  and --??
      (rz, dbSys, dbName, name, partition, instance, updatestatstime
       ,loadTs) in
      ( select
         rz, dbSys, dbName, name, partition, instance, updatestatstime
         ,loadTs from
( -- r duplicates with row number
  select s.*
      , row_number() over(partition by s.rz, s.dbSys, s.dbName
                                     , s.name, s.partition, s.instance
                                     , s.updateSTatsTime
                          order by a.loadTs
                          ) rn
      , a.loadTs
    from
( -- s keys with duplicates
  select rz, dbSys, dbName, name, partition, instance, updatestatstime
      , count(*) cnt
    from OA1P.TQZ006GBGRTSSTATS
     where rz = 'RZ2'  and dbSys = 'DVBP'
  group by rz, dbSys, dbName, name, partition, instance, updatestatstime
  having count(*) > 1
) s
      join OA1P.TQZ006GBGRTSSTATS a
        on a.rz = s.rz and a.dbSys = s.dbSys
                and a.dbName = s.dbName and a.name = s.name
                and a.partition = s.partition
                and a.instance = s.instance
                and a.updateStatsTime = s.updateStatsTime
) r
where rn > 1 )
    /*  --??
group by rz, dbSys, loadTs, state
order by 1 desc
     */  --??
;
commit