zOs/SQL/XCREDO

set current path oa1p;
set current application compatibility 'V11R1';
-- statistics of partition recovery state ----------------------------
with r as
(
  select  max(value(pSpc, 0), value(rSpc, 0), 0) * 1024.0 spc
        , case when posStr(lgRnRecLoad, 'stillUnl') > 0 then 'cleanup'
               when posStr(lgRnRecLoad, 'punNotSo') > 0 then 'redoUnl'
               when stage = 'UL' and substr(lgRnRecLoad, 1, 1) <> 'l'
                       and lgRnRecLoad not in ('r ok:F=fulCopy ok'
                                ,'r ok:F=fulCopy ok lgRnNone'
                                ,'r ok:F=fulCopy dataChangeV11>unl'
                                )
                   then 'redoUnl' else '' end  redo
        , r.*
      from oa1p.vQz005RecovDeltaLoadLgRn r
      where (db like 'XC%' or db like 'XR%')
           and (ts like 'A2%' or ts like 'A5%')
        -- and (db like 'XC%' and ts like 'A5%')
)
select substr(fosFmtE7(sum(spc))
                 !! right('       ' !! count(*), 8), 1, 15)
                   "spaceBy   count"
        , redo
        , stage
        , substr(lgRnRecLoad, 1, 70) recoveryState
        , min(unlTst) unlFrom
        , max(unlTst) unlTo
        , min(staUpd) staFrom
        , max(staUpd) staTo
    from r
    group by grouping sets ((), (redo), (redo, stage, lgRnRecLoad) )
       order by 2, 3, 4
;
-- list of partitions with recovery errors ----------------------------
with r as
(
  select  max(value(pSpc, 0), value(rSpc, 0), 0) * 1024.0 spc
        , case when posStr(lgRnRecLoad, 'stillUnl') > 0 then 'cleanup'
               when posStr(lgRnRecLoad, 'punNotSo') > 0 then 'redoUnl'
               when stage = 'UL' and substr(lgRnRecLoad, 1, 1) <> 'l'
                       and lgRnRecLoad not in ('r ok:F=fulCopy ok'
                                ,'r ok:F=fulCopy ok lgRnNone'
                                ,'r ok:F=fulCopy dataChangeV11>unl'
                                )
                   then 'redoUnl' else '' end  redo
        , r.*
      from oa1p.vQz005RecovDeltaLoadLgRn r
      where (db like 'XC%' or db like 'XR%')
           and (ts like 'A2%' or ts like 'A5%')
        -- and (db like 'XC%' and ts like 'A5%')
)
select substr(db, 1, 8) db
      , substr(ts, 1, 8) ts
      , smallint(pa) pa
      , redo
      , stage
      , substr(fosFmtE7(spc), 1, 7) spcBy
      , substr(lgRnRecLoad, 1, 60) recoveryState
      , staUpd
      , unlTst
      , lastEq
    from r
    where redo <> ''
    order by 1, 2, 3
;
-- further cleanup: duplicate unloads etc ... -------------------------
select db, ts, pa, stage, staUpd
      , strip(strip(err) || ' ' || info) "error llq ..."
    from oa1p.tQz005TecSvUnload
    where stage <> '-r' and (err <> '' or info <> '')
    order by db, ts, pa
;x;