zOs/SQL/GBGRSPHI

     -- index space und Details History / Zeitreihe
set current path oa1p;
select rz, dbSys, dbName, ts, indexSpace
      , substr(tsTy
          || case when tsClone = 'N' and instance=1 and tsInst=1 then ''
                  else case when instance=tsInst then 'b' else 'c' end
                      || instance end, 1, 3) "yci"
      , substr(case when partition = 0 and tsParts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0  then ' npi'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| tsParts, 4),'----')
            end, 1, 9) "part/ tot"
      , smallInt(partition) part
      , substr(fosFmte7(real(nActive) * ixPgSz * 1024), 1, 7) used
      , substr(fosFmte7(totalEntries), 1, 7) rows
      , substr(name, 1, 30) "index"
      , updateStatsTime, i.*
    from oa1p.tqz007GbGrIxSTats i
    where rz = 'RR2' and dbSys = 'DBOF'
        and dbName = 'DI05A1P' and ts like 'A044%'
    order by rz, dbSys, dbName, ts, indexSpace, instance, partition
        , validEnd desc
; x;
with dR (d,l) as --- Zeitreihe ----------------------------------------
(
  select current date, 0
    from sysibm.sysDummy1
  union all select d - 3 days, l+1
    from dR
    where l < 999999 and d >= current date - 1 months
)
, d as --- optimizer muss wissen dass kleine Zeitreihe ... ------------
(
   select * from dR fetch first 100 rows only
)
, i1 as --- index Partitionen auswählen -------------------------------
(
  select rz, dbSys, dbName, ts, indexSpace, instance, partition
    from oa1p.tqz007GbGrIxSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
        and dbName = 'MF01A1P' and ts like 'A150%'
    group by rz, dbSys, dbName, ts, indexSpace, instance, partition
)
, i2 as --- neueste loadTS für jedes Datut d ---------------------------
(
  select d.d, i1.*, (select max(loadTs) from oa1p.tqz007GbGrIxSTats r
    where i1.rz = r.rz and i1.dbSys = r.dbSys
          and  i1.dbName = r.dbName
          and  i1.ts   = r.ts
          and  i1.instance = r.instance
          and  i1.indexSpace = r.indexSpace
          and  i1.partition = r.partition
          and loadTs < timestamp(d.d + 1 day)
    ) loadTs
    from i1, d
)
, i as --- join ixStats falls nicht geDropT ---------------------------
(
   select d, r.*
     from i2
      join oa1p.tqz007GbGrIxSTats r
        on i2.loadTS is not null and r.state <> 'd'
                and i2.rz        = r.rz
                and i2.dbSys     = r.dbSys
                and i2.dbName    = r.dbName
                and i2.ts        = r.ts
                and i2.indexSpace= r.indexSpace
                and i2.instance  = r.instance
                and i2.partition = r.partition
                and i2.loadTs    = r.loadTS
)
select rz, dbSys, dbName, ts, indexSpace
      , substr(tsTy
          || case when tsClone = 'N' and instance=1 and tsInst=1 then ''
                  else case when instance=tsInst then 'b' else 'c' end
                      || instance end, 1, 3) "yci"
      , substr(case when partition = 0 and tsParts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0  then ' npi'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| tsParts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , d
      , substr(fosFmte7(real(nActive) * ixPgSz * 1024), 1, 7) used
      , substr(fosFmte7(totalEntries), 1, 7) rows
      , substr(name, 1, 30) "index"
      , updateStatsTime, i.*
    from i
    order by rz, dbSys, dbName, ts, indexSpace, instance, partition
        , d desc
;