zOs/SQL/GBGRCOVR

with d (l, d) as
(
  select 0, current timestamp from sysibm.sysDummy1
  union all select l+1, d - 7 days from d where l < 4
)
select d, indexSpace, name, i.*
    from d, oa1p.tqz007GbGrIxStats i
    where rz = 'RZZ' and dbSys = 'DE0G' and dbName = 'MF01A1P'
           and ts = 'A150A'
           and d between validBegin and validEnd
    order by 1 desc, 2, partition
;x;
with l2 as
(
  select rz, dbSys, dbName, ts, indexSpace, partition, instance
         , max(substr(char(validEnd)
              || strip(creator) || '.' || strip(name), 1, 60)) vn
    from oa1p.tqz007GbGrIxStats i
    where rz = 'RZZ' and dbSys = 'DE0G'
    group by rz, dbSys, dbName, ts, indexSpace, partition, instance
)
, l as
, l as
(
  select l2.*
      , timestamp(substr(vn, 1, 26)) validEnd
      , substr(vn, 27) ix
    from l2
)
select validEnd, ix, l.*
    from l
    where validEnd between current timestamp - 10 days
                       and '9000-01-01-00.00.00'
    order by 3,4,5,6,7, 8, 9
;x;
  select *
    from oa1p.tqz007GbGrIxHist  i
    where rz = 'RZZ' and dbSys = 'DE0G' and state = 'd'
        and updateStatsTime > current timestamp - 10 days
    order by rz, dbSys, dbName, ts, indexSpace, partition, instance
;x;
set current application compatibility 'V11R1';
with t as
(
  select rz || '/' || dbSys || ':' || dbName || ':' || name
            || '#' || partition || 'i' || instance
            || '@' || char(validBegin) id
        , validBegin
        , validEnd
        , ( select count(*)
              from oa1p.tqz006GbGrTsStats a
              where t.rz = a.rz and t.dbSys = a.dbSys
                  and t.dbName = a.dbName and t.name = a.name
                  and t.partition = a.partition
                  and t.instance  = a.instance
                  and t.validBegin = a.validEnd
          ) || '<'
        ||( select count(*)
              from oa1p.tqz006GbGrTsStats a
              where t.rz = a.rz and t.dbSys = a.dbSys
                  and t.dbName = a.dbName and t.name = a.name
                  and t.partition = a.partition
                  and t.instance  = a.instance
                  and t.validBegin <= a.validEnd
                  and t.validEnd   >= a.validBegin
          ) || '>'
        ||( select count(*)
              from oa1p.tqz006GbGrTsStats a
              where t.rz = a.rz and t.dbSys = a.dbSys
                  and t.dbName = a.dbName and t.name = a.name
                  and t.partition = a.partition
                  and t.instance  = a.instance
                  and t.validEnd   = a.validBegin
          ) over
    from oa1p.tqz006GbGrTsStats t
    where rz = 'RZ2' and dbSys = 'DVBP'
)
, c as
(
   select id
        , case when validEnd > '9000-01-01-00.00.00'
                     then 'end' else 'act' end valEnd
        , case when validBegin < validEnd then '<'
               when validBegin = validEnd then '='
               else '>' end valBegEnd
        , over
     from t
)
select valBegEnd, substr(over, 1, 10) over, valEnd
      , count(*)
      , min(id), max(id)
    from c
    group by cube(valBegEnd, over, valEnd)
    order by      valBegEnd, over, valEnd