zOs/SQL/GBGRCLEY

-- gbGr cleanup
--     check / delete duplicate in tqz006gbgrTsStats
--     duplicates (rz, ..., updateSTatsTime) but with distinct loadTS
--     (1) select ...
--     (2) delete
--     switch  zwischen (1) und (2) durch anpassen der mit --??
--                                  markierten stellen
--
--     bei jedem duplicate: all rows > min(loadTs) und <= max(loadTS)
--         rausputzen, die sind nämlich alle falsch
--         z.B. wenn duplicate durch irrtümliches stage=d entstand
--
--?? delete  --??
/*select rz, dbSys, state, count(*) --??
--lect rz, dbSys, dbName, name, partition, instance, state
--     , loadTs, updateStatstime
    from OA1P.TQZ006GBGRTSSTATS where
      (rz, dbSys, dbName, name, partition, instance, updatestatstime
       ,loadTs) in
      ( select s.rz, s.dbSys, s.dbName, s.name, s.partition
            , s.instance, a.updatestatstime, a.loadTs
          from */
select count(*), cFr, cBe, cTo, cnt
from
( -- g
  select rz, dbSys, dbName, name, partition, instance
      , loadFr, loadTo
      , cnt
      , sum(case when a.loadTs = s.loadFr then 1 else 0 end) cFr
      , sum(case when a.loadTs > s.loadFr
                  and a.loadTs < s.loadTo then 1 else 0 end) cBe
      , sum(case when a.loadTs = s.loadTo then 1 else 0 end) cTo
from
( -- j
  select s.rz, s.dbSys, s.dbName, s.name, s.partition, s.instance
  , a.updatestatstime, a.state, s.loadFr, s.loadTo
      , count(*) cnt, min(loadTs) loadFr, max(loadTs) loadTo
from
( -- s keys with duplicates
  select rz, dbSys, dbName, name, partition, instance, updatestatstime
      , count(*) cnt, min(loadTs) loadFr, max(loadTs) loadTo
    from OA1P.TQZ006GBGRTSSTATS
     where rz = 'RR2' --  and dbSys = 'DBOF'
     and dbName like 'MF01%'
  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.loadTs >= s.loadFr
                and a.loadTs <= s.loadTo
group by s.rz, s.dbSys, s.dbName, s.name, s.partition, s.instance
          , s.loadFr, s.loadTo
) j
group by rz, dbSys, cFr, cBe, cTo, cnt
) g
order by rz, dbSys, cFr, cBe, cTo, cnt
;
commit