zOs/SQL/GBGRCLEC

-- 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(*), cnt, aFr, cFr, aBe, cBe, aTo, cTo
from
( -- j
  select s.rz, s.dbSys, s.dbName, s.name, s.partition, s.instance
      , s.loadFr, s.loadTo
      , cnt
      , sum(case when a.loadTs = s.loadFr and a.state = 'a'
                 then 1 else 0 end) aFr
      , sum(case when a.loadTs = s.loadFr and a.state <> 'a'
                 then 1 else 0 end) cFr
      , sum(case when a.loadTs > s.loadFr
                  and a.loadTs < s.loadTo and a.state = 'a'
           then 1 else 0 end) aBe
      , sum(case when a.loadTs > s.loadFr
                  and a.loadTs < s.loadTo and a.state <> 'a'
           then 1 else 0 end) cBe
      , sum(case when a.loadTs = s.loadTo and a.state = 'a'
           then 1 else 0 end) aTo
      , sum(case when a.loadTs = s.loadTo and a.state <> 'a'
           then 1 else 0 end) cTo
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 'MF01A%'
  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
/* ???
)
--der by rz, dbSys, dbName, name, partition, instance, loadTs
group by rz, dbSys, state
???? */
group by s.rz, s.dbSys, s.dbName, s.name, s.partition, s.instance
         , s.loadFr, s.loadTo, s.cnt
) j
group by cnt, aFr, cFr, aBe, cBe, aTo, cTo
;
commit