zOs/SQL/GBGRCLIE

set current application compatibility 'V11R1';
-- gbGr cleanup  index
--     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.TQZ007GBGRIXSTATS where
      (rz, dbSys, dbName, ts, indexSpace, partition, instance, loadTs
       , updateStatsTIme) in
      ( select d.rz, d.dbSys, d.dbName, d.ts, d.indexSpace
       , d.partition, d.instance, d.aftLoad, d.aftUpd

/* --??
select rz, dbSys, del, sum(cBad) cBad
  , sum(case when left(del, 1) <> '-' then 1 else 0 end) cAft
  , count(*) g, sum(cnt) cnt
*/ --??
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, ts, indexSpace, 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.TQZ007GBGRIxSTATS
        where rz = 'RQ2'  -- and dbSys = 'DBOF'
         -- and dbName like 'MF01A%'
    group by rz, dbSys, dbName, ts, indexSpace, partition, instance
) s
) t
) d
where left(del, 1) <> '-'
) -- where in
--?? group by rollup(rz, dbSys, del)
--?? order by rz, dbSys, del
;
commit
;