zOs/SQL/GBGRSPHT

set current path oa1p;
set current path oa1p;
select rz, dbSys, dbName, name
      , substr(case when tsTy <> '' then tsTy else tsType end
          || case when clone in ('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 parts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| parts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , fqzFmtBin7(real(nActive) * pgSize * 1024) used
      , substr(fosFmte7(totalRows), 1, 7) rows
      , substr(tb, 1, 30) "table"
      , updateStatsTime
      , validBegin, validEnd
      , t.*
    from oa1p.tqz006GbGrTsSTats t
    where rz = 'RR2' and dbSys = 'DBOF'
        and  dbName = 'DI05A1P'
          and name = 'A044A'
   --     and partition = 139
    order by rz, dbSys, dbName, name, instance, partition
           , validBegin desc
;x;
with d (d,l) as --- Zeitreihe ----------------------------------------
(
  select timestamp(current date, '12:00:00'), 0
    from sysibm.sysDummy1
  union all select d - 1 month, l+1
    from d
    where l < 1 and d >= current timestamp - 33 months
)
, t as --- ts partitionen selektieren --------------------------------
(
   select dbName db, count(*) parts,
        sum(real(nActive) * nActive * pgSize * 1024)
    from oa1p.tqz006GbGrTsSTats t join d d
      on d >= validBegin and d < validEnd
    where rz = 'RZ2' and dbSys = 'DBOF'
        and  dbName like 'DI05A1P%'  A044A
          and name = 'A044A'
--        and partition = 16
)
select rz, dbSys, dbName, name
      , substr(case when tsTy <> '' then tsTy else tsType end
          || case when clone in ('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 parts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| parts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , date(d)
      , fqzFmtBin7(real(nActive) * pgSize * 1024) used
      , substr(fosFmte7(totalRows), 1, 7) rows
      , substr(tb, 1, 30) "table"
      , updateStatsTime
      , validBegin, validEnd
      , t.*
    from t
    order by rz, dbSys, dbName, name, instance, partition
           , d desc
;x
select rz, dbSys, dbName, name
      , substr(case when tsTy <> '' then tsTy else tsType end
          || case when clone in ('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 parts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| parts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , fqzFmtBin7(real(nActive) * pgSize * 1024) used
      , substr(fosFmte7(totalRows), 1, 7) rows
      , substr(tb, 1, 30) "table"
      , updateStatsTime
      , validBegin, validEnd
      , t.*
    from oa1p.tqz006GbGrTsSTats t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and  dbName = 'KS09A1P'
          and name = 'A841A'
   --     and partition = 139
    order by rz, dbSys, dbName, name, instance, partition
           , validBegin desc
;x;