zOs/SQL/GBGRSCRR

--   GBGrenze Schwellen von RZ2 nach RR2 kopieren
--       nach jedem PTA Aufbau|
--
--   1) alte RR2 Schwellen bekommen validEnd = current date
--   2) aktive RZ2 Schwellen werden für RR2 kopiert
--            mit validEnd = max(validEnd, current date + 100 days)
--
--   also: wenn im RR2 Bereinigungsaktionen durchgeführt,
--         müssen dort die Schwellen angepasst werden |
--
--   Achtung: Rollback am Ende auf Commit ändern, wenn getestet
--
select substr(db, 1, 8) "db"
      , substr(ts, 1, 8) "ts"
      , substr(tsTy
          || case when tsClone = 'N' and inst=1 and tsInst=1 then ''
                  else case when inst=tsInst then 'b' else 'c' end
                      || inst end, 1, 3) "yci"
      , substr(ix, max(1, length(ix) - 7), 8) "...index"
      , substr(case when part = 0 and tsParts = 0 then ''
            else case when part is null                 then ' ---'
                      when part = 0 and ix <> ' --ts--' then ' npi'
                      when part = 0 and tsTy = 'G'      then ' pbg'
                      when part = 0                     then ' ???'
                      else right('   ' || part, 4)
                 end
             ||'/'|| value(right('   '|| tsParts, 4),'----')
            end, 1, 9) "part/ tot"
      , substr(right(case when actGB < 1000
                         then '    ' || dec(round(actGb, 2), 6, 2)
                         else '    ' || int(round(actGb, 0))
                     end, 7), 1, 7) "usedGB"
      , substr(right(case when limGb/100*schwelle < 1000
              then '    ' || dec(round(limGb/100*schwelle, 2), 6, 2)
              else '    ' || int(round(limGb/100*schwelle, 0))
                     end, 7), 1, 7) "schwGB"
      , substr(right('     ' || schwelle, 5), 1, 5) "schw%"
      , substr(right('      ' || int(round(limGb)), 6), 1, 6) "limGB"
      , date(updStats) "lastUpdate"
      , substr(schwinfo, 23, 18) "schwellwert key"
      , g.*
    from OA1P.vQZ006GbGrenze g
    where actGb > real(limGb / 100 * schwelle)
   --   and updStats > current timestamp - 21 days
   --       db like 'MF%' -- and ts = 'A115A'
   --       db = 'SN01A1P' and ts =  'A202A'
        and db <> 'DSNDB01'  -- directory ist anders
        and rz = 'RR2' and dbSys = 'DBOF'
    order by db, ts, inst, ix, part
;
--
-- db         = Datenbank
-- ts         = Tablespace
-- yci        = ts type oder s=Segmented,i=Simple p=PartitionedClassic,
--              clone und Instance (falls geKlont)
-- part/ tot  = betroffene PartitionsNummer / Total Partitonen des ts
-- ...index   = index oder --ts--
-- usedGB     = aktuelle benutzter Platz in GB
-- schwGB     = Schwellwert in GB
-- schw%      = Schwellwert in Prozent der Limite
-- limGB      = physische Limite in GB
-- lastUpdate = letzter update aus RealTimeStats
-- Schwellwert key = key des Schwellwerts in oa1p.tqz008GbGrSchwelle

update   OA1P.TQZ008GBGRSCHWELLE
    set validEnd = current date
    where rz = 'RR2'
        and prio >= 0
        and validEnd > current date
;
insert into  OA1P.TQZ008GBGRSCHWELLE
     (  PRIO
     ,  RZ
     ,  DBSYS
     ,  DB
     ,  TS
     ,  PART
     ,  TSTY
     ,  DSMIN
     ,  SCHWELLE
     ,  VALIDBEGIN
     ,  VALIDEND
     ,  ERFASSER
     ,  GRUSE
     ,  GRUND
     )
select  PRIO
     ,  'RR2' RZ
     ,  DBSYS
     ,  DB
     ,  TS
     ,  PART
     ,  TSTY
     ,  DSMIN
     ,  SCHWELLE
     ,  VALIDBEGIN
     ,  max(VALIDEND, current date + 100 days)
     ,  ERFASSER
     ,  GRUSE
     ,  GRUND
    from OA1P.TQZ008GBGRSCHWELLE
    where rz = 'RZ2'
        and prio >= 0
        and validEnd > current date
;
select substr(db, 1, 8) "db"
      , substr(ts, 1, 8) "ts"
      , substr(tsTy
          || case when tsClone = 'N' and inst=1 and tsInst=1 then ''
                  else case when inst=tsInst then 'b' else 'c' end
                      || inst end, 1, 3) "yci"
      , substr(ix, max(1, length(ix) - 7), 8) "...index"
      , substr(case when part = 0 and tsParts = 0 then ''
            else case when part is null                 then ' ---'
                      when part = 0 and ix <> ' --ts--' then ' npi'
                      when part = 0 and tsTy = 'G'      then ' pbg'
                      when part = 0                     then ' ???'
                      else right('   ' || part, 4)
                 end
             ||'/'|| value(right('   '|| tsParts, 4),'----')
            end, 1, 9) "part/ tot"
      , substr(right(case when actGB < 1000
                         then '    ' || dec(round(actGb, 2), 6, 2)
                         else '    ' || int(round(actGb, 0))
                     end, 7), 1, 7) "usedGB"
      , substr(right(case when limGb/100*schwelle < 1000
              then '    ' || dec(round(limGb/100*schwelle, 2), 6, 2)
              else '    ' || int(round(limGb/100*schwelle, 0))
                     end, 7), 1, 7) "schwGB"
      , substr(right('     ' || schwelle, 5), 1, 5) "schw%"
      , substr(right('      ' || int(round(limGb)), 6), 1, 6) "limGB"
      , date(updStats) "lastUpdate"
      , substr(schwinfo, 23, 18) "schwellwert key"
      , g.*
    from OA1P.vQZ006GbGrenze g
    where actGb > real(limGb / 100 * schwelle)
   --   and updStats > current timestamp - 21 days
   --       db like 'MF%' -- and ts = 'A115A'
   --       db = 'SN01A1P' and ts =  'A202A'
        and db <> 'DSNDB01'  -- directory ist anders
        and rz = 'RR2' and dbSys = 'DBOF'
    order by db, ts, inst, ix, part
;
commit
;
rollback
;
select *
    from OA1P.TQZ008GBGRSCHWELLE
    where rz = 'RR2'
        and prio >= 0
        and validEnd > current date