zOs/SQL/GBGRCLEE

-- gbGr cleanup
--     2 days bad restart and bad data
--         check previous and next row, and delete if bad
--     (1) select ...
--     (2) delete
--     switch  zwischen (1) und (2) durch anpassen der mit --??
--                                  markierten stellen
--
delete --??
    from OA1P.TQZ006GBGRTSSTATS where
      (rz, dbSys, dbName, name, partition, instance, loadTs
       , updateStatsTIme) in
      ( select d.rz, d.dbSys, d.dbName, d.name, d.partition, d.instance
            , d.aftLoad, d.aftUpd
from
( -- d give reason to delete or not to
select case when cnt <= 0 then '-bad0'
            when aftLoad is null then '-aftNull ' || befState
            when aftLoad > '2015-10-22-00.00.00'
                                 then '-aftLoad> ' || befState||aftState
            when befState = 'd' and aftState = 'd' then 'dd'
            when befState <> aftState then '-state '||befState||aftState
            when befUpd = aftUpd then '=='||befState
            when befUpd > aftUpd then '=>'||befState
            else '-else ' ||befState || aftState end del
      , t.*
from
( -- t decode s
  select s.*
      , case when befLUS <> '' then timestamp(substr(befLUS, 1, 26))
             else null end befLoad
      , case when befLUS <> '' then timestamp(substr(befLUS, 27, 26))
             else null end befUpd
      , case when befLUS <> '' then substr(befLUS, 53)
             else null end befState
      , case when aftLUS <> '99' then timestamp(substr(aftLUS, 1, 26))
             else null end aftLoad
      , case when aftLUS <> '99' then timestamp(substr(aftLUS, 27, 26))
             else null end aftUpd
      , case when aftLUS <> '99' then substr(aftLUS, 53)
             else null end aftState
from
( -- s keys with duplicates
  select rz, dbSys, dbName, name, partition, instance
      , count(*) cnt
      , sum(case when loadTs <  '2015-10-14-00.00.00'
                   or loadTs >= '2015-10-16-00.00.00'
                 then 0 else 1 end) cBad
      , max(case when loadTs <  '2015-10-14-00.00.00'
                     then char(loadTs) || char(updateStatsTime) || state
                     else '' end) befLUS
      , min(case when loadTs >= '2015-10-16-00.00.00'
                     then char(loadTs) || char(updateStatsTime) || state
                     else '99' end) aftLUS
      , max(loadTs) loadFr, max(loadTs) loadTo
    from OA1P.TQZ006GBGRTSSTATS
        where rz = 'RQ2' --  and dbSys = 'DBOF'
      -- and dbName like 'MF01A%'
    group by rz, dbSys, dbName, name, partition, instance
) s
) t
) d
where left(del, 1) <> '-'
) -- where in
--?? group by rz, dbSys, state
--?? order by rz, dbSys, state
;
commit
;