zOs/JCL/QZT84
$#=
$*(
$>. 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=E,TIME=1440,
// 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
//*
//* 12.12.14 elar xb: nur partition die seit 1.12.14 noch wachsen
$*(
//* 25. 9.14 rz2/dvbp XB% bis ZS nov14 excluded
$*)
//* ------ load $rz/$dbSys into $tb
//LOADTSS EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//*DSSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SQL DD DSN=$ablfRz.SQL,
// DISP=(MOD,DELETE)
//SYSOUT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//TSSTATS DD DISP=SHR,$csm
// DSN=$ablfP.TSSTATS
//SYSIN DD *
LOAD DATA LOG NO
WORKDDN(TSYUTS,TSOUTS)
SORTKEYS
SORTDEVT DISK
MAPDDN TMAPD ERRDDN TERRD
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE $tb
PART 1 INDDN TSSTATS RESUME NO REPLACE COPYDDN(TCOPYD)
(
TSTYPE POSITION( 1 )
CHAR( 1) ,
TSTY POSITION( 2 )
CHAR( 1) ,
PGSIZE POSITION( 3 )
SMALLINT ,
SEGSIZE POSITION( 5 )
SMALLINT ,
PARTS POSITION( 7 )
SMALLINT ,
MAXPARTS POSITION( 9 )
SMALLINT ,
DSSIZE POSITION( 11 )
INTEGER ,
DSGB POSITION( 15 )
FLOAT( 21) ,
LIMGB POSITION( 19 )
FLOAT( 21) ,
LIMPART POSITION( 23 )
SMALLINT ,
OBID POSITION( 25 )
SMALLINT ,
CLONE POSITION( 27 )
CHAR( 1) ,
TSINST POSITION( 28 )
SMALLINT ,
TBCR POSITION( 30 )
VARCHAR ,
TB POSITION( 160 )
VARCHAR ,
TBTY POSITION( 290 )
VARCHAR ,
TBOBID POSITION( 293 )
SMALLINT ,
UPDATESTATSTIME POSITION( 295 )
TIMESTAMP EXTERNAL( 26) ,
NACTIVE POSITION( 321 )
INTEGER
NULLIF( 325)='?',
NPAGES POSITION( 326 )
INTEGER
NULLIF( 330)='?',
EXTENTS POSITION( 331 )
SMALLINT
NULLIF( 333)='?',
LOADRLASTTIME POSITION( 334 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 360)='?',
REORGLASTTIME POSITION( 361 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 387)='?',
REORGINSERTS POSITION( 388 )
INTEGER
NULLIF( 392)='?',
REORGDELETES POSITION( 393 )
INTEGER
NULLIF( 397)='?',
REORGUPDATES POSITION( 398 )
INTEGER
NULLIF( 402)='?',
REORGUNCLUSTINS POSITION( 403 )
INTEGER
NULLIF( 407)='?',
REORGDISORGLOB POSITION( 408 )
INTEGER
NULLIF( 412)='?',
REORGMASSDELETE POSITION( 413 )
INTEGER
NULLIF( 417)='?',
REORGNEARINDREF POSITION( 418 )
INTEGER
NULLIF( 422)='?',
REORGFARINDREF POSITION( 423 )
INTEGER
NULLIF( 427)='?',
STATSLASTTIME POSITION( 428 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 454)='?',
STATSINSERTS POSITION( 455 )
INTEGER
NULLIF( 459)='?',
STATSDELETES POSITION( 460 )
INTEGER
NULLIF( 464)='?',
STATSUPDATES POSITION( 465 )
INTEGER
NULLIF( 469)='?',
STATSMASSDELETE POSITION( 470 )
INTEGER
NULLIF( 474)='?',
COPYLASTTIME POSITION( 475 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 501)='?',
COPYUPDATEDPAGES POSITION( 502 )
INTEGER
NULLIF( 506)='?',
COPYCHANGES POSITION( 507 )
INTEGER
NULLIF( 511)='?',
COPYUPDATELRSN POSITION( 512 )
CHAR( 6)
NULLIF( 518)='?',
COPYUPDATETIME POSITION( 519 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 545)='?',
IBMREQD POSITION( 546 )
CHAR( 1) ,
DBID POSITION( 547 )
SMALLINT ,
PSID POSITION( 549 )
SMALLINT ,
PARTITION POSITION( 551 )
SMALLINT ,
INSTANCE POSITION( 553 )
SMALLINT ,
SPACE POSITION( 555 )
BIGINT
NULLIF( 563)='?',
TOTALROWS POSITION( 564 )
BIGINT
NULLIF( 572)='?',
DATASIZE POSITION( 573 )
BIGINT
NULLIF( 581)='?',
UNCOMPRESSEDDATASIZE POSITION( 582 )
BIGINT
NULLIF( 590)='?',
DBNAME POSITION( 591 )
VARCHAR ,
NAME POSITION( 617 )
VARCHAR ,
REORGCLUSTERSENS POSITION( 643 )
BIGINT
NULLIF( 651)='?',
REORGSCANACCESS POSITION( 652 )
BIGINT
NULLIF( 660)='?',
REORGHASHACCESS POSITION( 661 )
BIGINT
NULLIF( 669)='?',
HASHLASTUSED POSITION( 670 )
DATE EXTERNAL( 10)
NULLIF( 680)='?',
DRIVETYPE POSITION( 681 )
CHAR( 3) ,
LPFACILITY POSITION( 684 )
CHAR( 1)
NULLIF( 685)='?',
STATS01 POSITION( 686 )
BIGINT
NULLIF( 694)='?'
)
//LOADTSD EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
insert into $tb
(state, loadTs, rz, dbSys, dbName, name, partition, instance
, tsType, tsTy, pgSize, segSize
, parts, maxParts, dsSize, dsGb, limGb, limPart
, obid, clone, tsInst, tbCr, tb, tbTy, tbObId
, dbid, psid, ibmReqD
, updateStatsTime)
with a as
(
select rz, dbSys, dbName, name
, partition, instance, max(loadTs) loadTs
from $tb a
where rz='$rz' and dbSys = '$dbSys'
group by rz, dbSys, dbName, name, partition, instance
)
, b as
(
select b.*
from $tb b join a
on b.rz = a.rz
and b.dbSys = a.dbSys
and b.dbName = a.dbName
and b.Name = a.Name
and b.partition = a.partition
and b.instance = a.instance
and b.loadTS = a.loadTs
)
select 'd', '$nowM'
, rz, dbSys, dbName, name, partition, instance
, tsType, tsTy, pgSize, segSize
, parts, maxParts, dsSize, dsGb, limGb, limPart
, obid, clone, tsInst, tbCr, tb, tbTy, tbObId
, dbid, psid, ibmReqD
, (select max(updateStatsTime)
from $tb n
where n.rz = '?' and n.dbSys = '?'
)
from b
where state <> 'd'
and not exists (select 1
from $tb n
where n.rz = '?' and n.dbSys = '?'
and n.dbName = b.dbName
and n.Name = b.Name
and n.partition = b.partition
and n.instance = b.instance
)
ENDEXEC
//LOADTSI EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
insert into $tb
select 'a' STATE
, '$rz' RZ
, '$dbSys' DBSYS
, '$nowM' LOADTS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, UPDATESTATSTIME
, NACTIVE
, NPAGES
, EXTENTS
, LOADRLASTTIME
, REORGLASTTIME
, REORGINSERTS
, REORGDELETES
, REORGUPDATES
, REORGUNCLUSTINS
, REORGDISORGLOB
, REORGMASSDELETE
, REORGNEARINDREF
, REORGFARINDREF
, STATSLASTTIME
, STATSINSERTS
, STATSDELETES
, STATSUPDATES
, STATSMASSDELETE
, COPYLASTTIME
, COPYUPDATEDPAGES
, COPYCHANGES
, COPYUPDATELRSN
, COPYUPDATETIME
, IBMREQD
, DBID
, PSID
, PARTITION
, INSTANCE
, SPACE
, TOTALROWS
, DATASIZE
, UNCOMPRESSEDDATASIZE
, DBNAME
, NAME
, REORGCLUSTERSENS
, REORGSCANACCESS
, REORGHASHACCESS
, HASHLASTUSED
, DRIVETYPE
, LPFACILITY
, STATS01
from $tb n
where n.rz = '?' and n.dbSys = '?'
and updatestatsTime <> value(( select timestamp(substr(max(
char(loadTs) || char(updateStatsTime)), 27))
from $tb a
where a.rz='$rz' and a.dbSys = '$dbSys'
and n.dbName = a.dbName
and n.name = a.name
and n.partition = a.partition
and n.instance = a.instance
)
, '1111-11-11-11.11.11')
ENDEXEC
// IF LOADTSS.RUN AND LOADTSI.RUN AND (RC=0 OR RC=4) THEN
//DELTSS EXEC PGM=IEFBR14
//DEL DD DISP=(OLD,DELETE),$csm
// DSN=$ablfP.TSSTATS
// ENDIF
$=tb=OA1P.TQZ007GBGRIXSTATS
//* ------ load $rz/$dbSys into $tb
//LOADIXS EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//*DSSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//*SYSOUT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//IXSTATS DD DISP=SHR,$csm
// DSN=$ablfP.IXSTATS
//SYSIN DD *
LOAD DATA LOG NO
WORKDDN(TSYUTS,TSOUTS)
SORTKEYS
SORTDEVT DISK
MAPDDN TMAPD ERRDDN TERRD
STATISTICS INDEX(ALL) REPORT NO UPDATE ALL
INTO TABLE $tb
PART 1 INDDN IXSTATS RESUME NO REPLACE COPYDDN(TCOPYD)
(
INDEXTYPE POSITION( 1 )
CHAR( 1) ,
COMPRESS POSITION( 2 )
CHAR( 1) ,
IXPARTS POSITION( 3 )
SMALLINT ,
IXPGSZ POSITION( 5 )
SMALLINT ,
PIECESIZE POSITION( 7 )
INTEGER ,
PIECEGB POSITION( 11 )
FLOAT( 21) ,
LIMGB POSITION( 15 )
FLOAT( 21) ,
TBCREATOR POSITION( 19 )
VARCHAR ,
TBNAME POSITION( 149 )
VARCHAR ,
TS POSITION( 279 )
VARCHAR ,
TSTY POSITION( 305 )
CHAR( 1) ,
TSPARTS POSITION( 306 )
SMALLINT ,
TSCLONE POSITION( 308 )
CHAR( 1) ,
TSINST POSITION( 309 )
SMALLINT ,
TSDSSIZE POSITION( 311 )
INTEGER ,
TSDSGB POSITION( 315 )
FLOAT( 21) ,
TSLIMGB POSITION( 319 )
FLOAT( 21) ,
TSLIMPART POSITION( 323 )
SMALLINT ,
TSPGSZ POSITION( 325 )
SMALLINT ,
UPDATESTATSTIME POSITION( 327 )
TIMESTAMP EXTERNAL( 26) ,
NLEVELS POSITION( 353 )
SMALLINT
NULLIF( 355)='?',
NPAGES POSITION( 356 )
INTEGER
NULLIF( 360)='?',
NLEAF POSITION( 361 )
INTEGER
NULLIF( 365)='?',
NACTIVE POSITION( 366 )
INTEGER
NULLIF( 370)='?',
SPACE POSITION( 371 )
INTEGER
NULLIF( 375)='?',
EXTENTS POSITION( 376 )
SMALLINT
NULLIF( 378)='?',
LOADRLASTTIME POSITION( 379 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 405)='?',
REBUILDLASTTIME POSITION( 406 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 432)='?',
REORGLASTTIME POSITION( 433 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 459)='?',
REORGINSERTS POSITION( 460 )
INTEGER
NULLIF( 464)='?',
REORGDELETES POSITION( 465 )
INTEGER
NULLIF( 469)='?',
REORGAPPENDINSERT POSITION( 470 )
INTEGER
NULLIF( 474)='?',
REORGPSEUDODELETES POSITION( 475 )
INTEGER
NULLIF( 479)='?',
REORGMASSDELETE POSITION( 480 )
INTEGER
NULLIF( 484)='?',
REORGLEAFNEAR POSITION( 485 )
INTEGER
NULLIF( 489)='?',
REORGLEAFFAR POSITION( 490 )
INTEGER
NULLIF( 494)='?',
REORGNUMLEVELS POSITION( 495 )
INTEGER
NULLIF( 499)='?',
STATSLASTTIME POSITION( 500 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 526)='?',
STATSINSERTS POSITION( 527 )
INTEGER
NULLIF( 531)='?',
STATSDELETES POSITION( 532 )
INTEGER
NULLIF( 536)='?',
STATSMASSDELETE POSITION( 537 )
INTEGER
NULLIF( 541)='?',
COPYLASTTIME POSITION( 542 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 568)='?',
COPYUPDATEDPAGES POSITION( 569 )
INTEGER
NULLIF( 573)='?',
COPYCHANGES POSITION( 574 )
INTEGER
NULLIF( 578)='?',
COPYUPDATELRSN POSITION( 579 )
CHAR( 6)
NULLIF( 585)='?',
COPYUPDATETIME POSITION( 586 )
TIMESTAMP EXTERNAL( 26)
NULLIF( 612)='?',
LASTUSED POSITION( 613 )
DATE EXTERNAL( 10)
NULLIF( 623)='?',
IBMREQD POSITION( 624 )
CHAR( 1) ,
DBID POSITION( 625 )
SMALLINT ,
ISOBID POSITION( 627 )
SMALLINT ,
PSID POSITION( 629 )
SMALLINT ,
PARTITION POSITION( 631 )
SMALLINT ,
INSTANCE POSITION( 633 )
SMALLINT ,
TOTALENTRIES POSITION( 635 )
BIGINT
NULLIF( 643)='?',
DBNAME POSITION( 644 )
VARCHAR ,
NAME POSITION( 670 )
VARCHAR ,
CREATOR POSITION( 800 )
VARCHAR ,
INDEXSPACE POSITION( 930 )
VARCHAR ,
REORGINDEXACCESS POSITION( 956 )
BIGINT
NULLIF( 964)='?',
DRIVETYPE POSITION( 965 )
CHAR( 3) ,
STATS101 POSITION( 968 )
BIGINT
NULLIF( 976)='?'
)
//LOADIXD EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
insert into $tb
( state, loadTs, rz, dbSys
, indexType, compress, ixParts, ixPgSz
, pieceSize, pieceGB, limGB
, tbCreator, tbName
, ts, tsTy, tsParts, tsClone, tsInst, tsDsSize, tsDsGb
, tsLimGb, tsLimPart, tsPgSz
, dbName, indexSpace, creator, name, partition, instance
, ibmReqD, dbid, isobid, psid
, updateStatsTime)
with a as
(
select rz, dbSys, dbName, ts, indexSpace
, partition, instance, max(loadTs) loadTs
from $tb a
where rz='$rz' and dbSys = '$dbSys'
group by rz, dbSys, dbName, ts, indexSpace, partition, instance
)
, b as
(
select b.*
from $tb b join a
on b.rz = a.rz
and b.dbSys = a.dbSys
and b.dbName = a.dbName
and b.ts = a.ts
and b.indexSpace= a.indexSpace
and b.partition = a.partition
and b.instance = a.instance
and b.loadTS = a.loadTs
)
select 'd', '$nowM', rz, dbSys
, indexType, compress, ixParts, ixPgSz
, pieceSize, pieceGB, limGB
, tbCreator, tbName
, ts, tsTy, tsParts, tsClone, tsInst, tsDsSize, tsDsGb
, tsLimGb, tsLimPart, tsPgSz
, dbName, indexSpace, creator, name, partition, instance
, ibmReqD, dbid, isobid, psid
, (select max(updateStatsTime)
from $tb n
where n.rz = '?' and n.dbSys = '?'
)
from b
where state <> 'd'
and not exists (select 1
from $tb n
where n.rz = '?' and n.dbSys = '?'
and n.dbName = b.dbName
and n.ts = b.ts
and n.indexSpace= b.indexSpace
and n.partition = b.partition
and n.instance = b.instance
)
ENDEXEC
//LOADIXI EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT8400P.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=DP4G.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
insert into $tb
select 'a' STATE
, '$rz' RZ
, '$dbSys' DBSYS
, '$nowM' LOADTS
, INDEXTYPE
, COMPRESS
, IXPARTS
, IXPGSZ
, PIECESIZE
, PIECEGB
, LIMGB
, TBCREATOR
, TBNAME
, TS
, TSTY
, TSPARTS
, TSCLONE
, TSINST
, TSDSSIZE
, TSDSGB
, TSLimGb
, TSLimPart
, TSPGSZ
, UPDATESTATSTIME
, NLEVELS
, NPAGES
, NLEAF
, NACTIVE
, SPACE
, EXTENTS
, LOADRLASTTIME
, REBUILDLASTTIME
, REORGLASTTIME
, REORGINSERTS
, REORGDELETES
, REORGAPPENDINSERT
, REORGPSEUDODELETES
, REORGMASSDELETE
, REORGLEAFNEAR
, REORGLEAFFAR
, REORGNUMLEVELS
, STATSLASTTIME
, STATSINSERTS
, STATSDELETES
, STATSMASSDELETE
, COPYLASTTIME
, COPYUPDATEDPAGES
, COPYCHANGES
, COPYUPDATELRSN
, COPYUPDATETIME
, LASTUSED
, IBMREQD
, DBID
, ISOBID
, PSID
, PARTITION
, INSTANCE
, TOTALENTRIES
, DBNAME
, NAME
, CREATOR
, INDEXSPACE
, REORGINDEXACCESS
, DRIVETYPE
, STATS101
from $tb n
where n.rz = '?' and n.dbSys = '?'
and updatestatsTime <> value(( select timestamp(substr(max(
char(loadTs) || char(updateStatsTime)), 27))
from $tb a
where a.rz='$rz' and a.dbSys = '$dbSys'
and n.dbName = a.dbName
and n.ts = a.ts
and n.indexSpace= a.indexSpace
and n.partition = a.partition
and n.instance = a.instance
)
, '1111-11-11-11.11.11')
ENDEXEC
// IF LOADIXS.RUN AND LOADIXI.RUN AND (RC=0 OR RC=4) THEN
//DELIXS EXEC PGM=IEFBR14
//DEL DD DISP=(OLD,DELETE),$csm
// DSN=$ablfP.IXSTATS
// ENDIF
// IF RC = 0 OR RC = 4 THEN
//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=*.LOADTSS.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 $@=[
-- elar xb: nur partition die seit 1.12.14 noch wachsen
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
// ENDIF