zOs/JCL/QZT84SQL

$#=
$>. fEdit()
$=rz=RZ2
$=dbSys=DBOF
$=nowM=- f('%t S')
$=now =- f('%tSs', $nowM)
$=ab=gbGr
$=ablfP=DSN.ABLF.GBGR.$dbSys
$=ablfRz=DSN.ABLF.GBGR.$dbSys.$rz
//QZT8400P JOB (CP00,KE50),'DB2 MVEXT',
//         MSGCLASS=T,TIME=1440,NOTIFY=&SYSUID,
//         REGION=0M,SCHENV=DB2
//*MAIN CLASS=P2
$@[
if $rz = sysvar(sysnode) then
    $= csm = $''
else
    $= csm = SUBSYS=(CSM,'SYSTEM=$rz'),
$=tb=OA1P.TQZ006GBGRTSSTATS
$]
//*
//* db2 gbGrenze ablauf $ab from $rz/$dbSys
//*              load into $tb
//* generated by abub at $now
//DEL      EXEC PGM=IEFBR14
//D1         DD DISP=(MOD,DELETE),DSN=A540769.TST.$rz.$dbSys.SQL
//SQL      EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN  DD *
    DSN SYSTEM(DP4G)
   RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT   DD DSN=A540769.TST.$rz.$dbSys.SQL,
//             DISP=(,CATLG),
//             MGMTCLAS=BAT#AT,
//             SPACE=(CYL,(15,75),RLSE)
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD DUMMY
//SYSIN    DD *
-- GigaByte Grenze
--     for    $rz/$dbSys
--     at     $now
--     source DSN.ABUB.A.SKELS(QZT84)
--************************************************************
--$'$$' GigaByte Grenze überschrittene Schwellwerte:
--************************************************************
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"
    from OA1P.vQZ006GbGrenze g
    where rz = '$rz' and dbSys = '$dbSys'
        and db <> 'DSNDB01'  -- directory ist anders
        and actGb > real(limGb / 100 * schwelle)
$@  if $dbSys = 'DVBP' | $dbSys = 'DEVG' then $@=[
        and ( db not like 'XB%'
          or (updStats >= '2014-12-01-00.00.00'
            and (nActive, nPages, REORGINSERTS

                , space, totalRows, dataSize)
              not in ( select z.nActive, z.nPages, z.REORGINSERTS
                     , z.space, z.totalRows, z.dataSize
                   from oa1p.tqz006GBGRTSSTATS z
                   where    g.rz         = z.rz
                        and g.dbSys      = z.dbSys
                        and g.DB         = z.DBNAME
                        and g.ts         = z.NAME
                        and g.PART       = z.PARTITION
                        and g.INST       = z.INSTANCE
                        and z.loadTs     < '2014-12-01-00.00.00'
                   order by z.loadTs desc
                   fetch first 1 row only
         )  )  )
$]
    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