zOs/JCL/QZT31L
$#=
$******************?????????
$>. fSub()
$=rz=RZX
$=dbSys=DPXG
$=nowM=- f('%t S')
$=now =- f('%tSs', $nowM)
$=ab=gbGr
$=ablfP=DSN.ABLF.GBGR.$dbSys
$=ablfRz=DSN.ABLF.GBGR.$dbSys.$rz
$************?????????????)
//QZT3100P JOB (CP00,KE50),'DB2 MVEXT',
// MSGCLASS=E,TIME=1440,CLASS=M1,
// REGION=0M,SCHENV=DB2
//*MAIN CLASS=P2
$@[
if $rz = sysvar(sysnode) then $@[
$= csm = $''
$= rzAblf = $ablfP
$] else $@[
$= csm = SUBSYS=(CSM,'SYSTEM=$rz'),
$= rzAblf = $rz/$ablfP
$]
$=tb=OA1P.TQZ006GBGRTSSTATS
$]
//*
//* db2 gbGrenze ablauf $ab from $rz/$dbSys
//* load into $tb
//* generated by abub skels(QZT31L) at $now
//*
//* 19. 2.15 load Columns aus Punchfile holen, v11 Kolonnen
$*( history
//* 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 part 1 mit ?/? (default)
//LOADTSS EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT3100P.LOAD')
//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)
(
$@[ call loadCols $<$rzAblf.TSPUNCH $]
)
//* ------ insert state='d' rows for newly deleted partitions
//LOADTSD EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT3100P.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
//* ------ insert state='a' rows for updated new partitions
//LOADTSI EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT3100P.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
, UPDATESIZE
, LASTDATACHANGE
from $tb n
where n.rz = '?' and n.dbSys = '?'
and updatestatsTime <> value( ( select updateStatsTime
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
order by loadTs desc
fetch first 1 row only
)
, '1111-11-11-11.11.11')
ENDEXEC
// IF LOADTSS.RUN AND LOADTSD.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,'QZT3100P.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)
(
$@[ call loadCols $<$rzAblf.IXPUNCH $]
)
//LOADIXD EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT3100P.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,'QZT3100P.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(QZT31L)
--************************************************************
--$'$$' 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' then $@=[
-- elar xb: nur partition die seit 1.12.14 noch wachsen
and ( db not like 'XB%'
or (updStats >= '2015-02-20-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 < '2015-02-20-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