zOs/REXX/QZT071C
$#=
$*( --- for test with wsh -------------
$>. fEdit()
$=rz=RZZ
$=dbSys=DPZG
$=nowM=- f('%t S')
$=now =- f('%tSs', $nowM)
$=ab=gbGr
$=ablfP=DSN.ABLF.GBGR.$dbSys
$=ablfRz=DSN.ABLF.GBGR.$dbSys.$rz
--- for test with wsh end ------------- $*)
//QZT0710P JOB (CP00,KE50),'DB2 GBGRENZE',
// MSGCLASS=T,TIME=1440,CLASS=M1,
// REGION=0M,SCHENV=DB2
$@[
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(QZT071) at $now
//*
//* 5. 1.16 nur noch eine Utility fuer TS bzw. IX
//* updateStatsTime wird auf ZürichTime übersetzt
//* origStatsTime enthält originalZeit
$*( history
19.11.15 rename qzt31L --> qzt071
19. 2.15 load Columns aus Punchfile holen, v11 Kolonnen
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 table data for $rz/$dbSys
//* --- load raw data into $tb part 1 mit ?/? (default)
//* --- insert active and drops'd rows if changed with $rz/$dbSys
//LOADTS EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT0710P.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 raw data from unload as ?/? into part 1 ---------------------
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 $]
)
EXEC SQL
--- insert 'a' rows for active tablePartitions ------------------------
insert into $tb
$@ if $rz = 'RR2' then $@=[
with g2 as
( --- pta: date in future: find highest timestamp
select max(updateStatsTime) statsMax
, timestamp('$nowM') loadTs
$** , count(*) cnt
from $tb
where rz = '?' and dbSys = '?'
)
, g3 as
(
select g2.*
, days(statsMax) - days(loadTs) d1
from g2
)
, g as
( --- calculate days Difference to our date
select g3.*
, case when statsMax <= loadTs then 0
when statsMax - (d1-1) days <= loadTs then d1-1
when statsMax - (d1 ) days <= loadTs then d1
else d1+1 end di
from g3
)
, t2 as
$=comG= , g
$=diDy= - di days
$] $@ else $@=[
with t2 as
$=comG= $''
$=diDy= $''
$]
( -- select loaded rows and previous timestamps/state
select t.*
, ( select char(a.updateStatsTime)
|| char(a.origStatsTime) || a.state
from $tb a
where a.rz='$rz' and a.dbSys = '$dbSys'
and t.dbName = a.dbName
and t.name = a.name
and t.partition = a.partition
and t.instance = a.instance
order by updateStatsTime desc
fetch first 1 row only
) uos
from $tb t
where rz = '?' and dbSys = '?'
)
, t as
( --- decode uos and select only rows with changed updateStatsTime
select t2.*
, timestamp(substr(uos, 1, 26)) prUpd
from t2
where uos is null
or timestamp(substr(uos, 27, 26)) <> updateStatsTime
or substr(uos, 53, 1) <> 'a'
)
--- select compute all columns to insert
select 'a' STATE
, '$rz' RZ
, '$dbSys' DBSYS
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, timestamp('$nowM') loadTS
, t.UPDATESTATSTIME origStatsTime --- original statsTime
, value(case --- statsTime in our calendar,
--- must be strictly increasing
--- should be <= loadTs
when ( t.updatestatsTime $diDy > prUpd
or prUpd is null )
and t.updatestatsTime $diDy
<= '$nowM'
then t.updatestatsTime $diDy
when '$nowM' <= prUpd
then trunc_timestamp(prUpd, 'mi') + 2 minutes
end, '$nowM') 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 t $comG
$*(
)
select count(*) cc
, max(cnt) cnt
, sum(case when updateStatsTime < '1911-11-11-11.11.11'
then 1 else 0 end) b11
, sum(case when updateStatsTime <> origStatsTime
then 1 else 0 end) new
from i $comG $*)
ENDEXEC
EXEC SQL
--- insert 'd' rows for dropped tablePartitions -----------------------
insert into $tb
(state, loadTs, origStatsTime, updateStatsTime
, 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
)
with g as
( --- find highest timestamp
select max(updateStatsTime) statsMax
from $tb
where rz = '?' and dbSys = '?'
)
, a as
( --- find key of newest row
select rz, dbSys, dbName, name
, partition, instance, max(updateStatsTime) prStats
from $tb a
where rz='$rz' and dbSys = '$dbSys'
group by rz, dbSys, dbName, name, partition, instance
)
, b as
( --- join newest row
--- select only if missing in new import and not a 'd' row already
select b.*
from a join $tb b
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.updateStatsTime = a.prStats
where b.state <> 'd'
and not exists (select 1
from $tb n
where n.rz = '?' and n.dbSys = '?'
and n.dbName = a.dbName
and n.Name = a.Name
and n.partition = a.partition
and n.instance = a.instance
)
)
--- select new values and non nullable rows
select 'd' state
, '$nowM' loadTs
, g.statsMax origStatsTime
, case when updateStatsTime
< '$nowM'
then timestamp('$nowM')
else trunc_timestamp(updateStatsTime, 'mi') + 2 minutes
end updateStatsTime
, 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
from b, g
ENDEXEC
//***** delete input dsn **********************************************
// IF LOADTS.RUN AND (RC=0 OR RC=4) THEN
//DELTS EXEC PGM=IEFBR14
//DEL DD DISP=(OLD,DELETE),$csm
// DSN=$ablfP.TSSTATS
// ENDIF
// IF LOADTS.RUN AND (RC=0 OR RC=4) THEN
$=tb=OA1P.TQZ007GBGRIXSTATS
//*********************************************************************
//* --- load index data for $rz/$dbSys
//* --- load raw data into $tb part 1 mit ?/? (default)
//* --- insert active and drops'd rows if changed with $rz/$dbSys
//LOADIX EXEC PGM=DSNUTILB,
// PARM=(DP4G,'QZT0710P.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT 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 $]
)
EXEC SQL
--- insert 'a' rows for active indexPartitions ------------------------
insert into $tb
$@ if $rz = 'RR2' then $@=[
with g2 as
( --- pta: date in future: find highest timestamp
select max(updateStatsTime) statsMax
, timestamp('$nowM') loadTs
$** , count(*) cnt
from $tb
where rz = '?' and dbSys = '?'
)
, g3 as
(
select g2.*
, days(statsMax) - days(loadTs) d1
from g2
)
, g as
( --- calculate days Difference to our date
select g3.*
, case when statsMax <= loadTs then 0
when statsMax - (d1-1) days <= loadTs then d1-1
when statsMax - (d1 ) days <= loadTs then d1
else d1+1 end di
from g3
)
, t2 as
$=comG= , g
$=diDy= - di days
$] $@ else $@=[
with t2 as
$=comG= $''
$=diDy= $''
$]
( -- select loaded rows and previous timestamps/state
select t.*
, ( select char(a.updateStatsTime)
|| char(a.origStatsTime) || a.state
from $tb a
where a.rz='$rz' and a.dbSys = '$dbSys'
and t.dbName = a.dbName
and t.ts = a.ts
and t.indexSpace= a.indexSpace
and t.partition = a.partition
and t.instance = a.instance
order by updateStatsTime desc
fetch first 1 row only
) uos
from $tb t
where rz = '?' and dbSys = '?'
)
, t as
( --- decode uos and select only rows with changed updateStatsTime
select t2.*
, timestamp(substr(uos, 1, 26)) prUpd
from t2
where uos is null
or timestamp(substr(uos, 27, 26)) <> updateStatsTime
or substr(uos, 53, 1) <> 'a'
)
--- select compute all columns to insert
select 'a' STATE
, '$rz' RZ
, '$dbSys' DBSYS
, INDEXTYPE
, COMPRESS
, IXPARTS
, IXPGSZ
, PIECESIZE
, PIECEGB
, LIMGB
, TBCREATOR
, TBNAME
, TS
, TSTY
, TSPARTS
, TSCLONE
, TSINST
, TSDSSIZE
, TSDSGB
, TSLIMGB
, TSLIMPART
, TSPGSZ
, timestamp('$nowM') loadTS
, t.UPDATESTATSTIME origStatsTime --- original statsTime
, value(case --- statsTime in our calendar,
--- must be strictly increasing
--- should be <= loadTs
when ( t.updatestatsTime $diDy > prUpd
or prUpd is null )
and t.updatestatsTime $diDy
<= '$nowM'
then t.updatestatsTime $diDy
when '$nowM' <= prUpd
then trunc_timestamp(prUpd, 'mi') + 2 minutes
end, '$nowM') 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 t $comG
ENDEXEC
EXEC SQL
--- insert 'd' rows for dropped indexPartitions -----------------------
insert into $tb
(state, loadTs, origStatsTime, updateStatsTime
, 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
)
with g as
( --- find highest timestamp
select max(updateStatsTime) statsMax
from $tb
where rz = '?' and dbSys = '?'
)
, a as
( --- find key of newest row
select rz, dbSys, dbName, ts, indexSpace
, partition, instance, max(updateStatsTime) prStats
from $tb a
where rz='$rz' and dbSys = '$dbSys'
group by rz, dbSys, dbName, ts, indexSpace, partition, instance
)
, b as
( --- join newest row
--- select only if missing in new import and not a 'd' row already
select b.*
from a join $tb b
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.updateStatsTime = a.prStats
where b.state <> 'd'
and not exists (select 1
from $tb n
where n.rz = '?' and n.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
)
)
--- select new values and non nullable rows
select 'd' state
, '$nowM' loadTs
, g.statsMax origStatsTime
, case when updateStatsTime
< '$nowM'
then timestamp('$nowM')
else trunc_timestamp(updateStatsTime, 'mi') + 2 minutes
end updateStatsTime
, 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
from b, g
ENDEXEC
// ENDIF
// IF LOADTS.RUN AND LOADIX.RUN AND (RC=0 OR RC=4) THEN
//DELIX 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=*.LOADTS.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(QZT071)
--************************************************************
--$'$$' 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.updateStatsTime < '2015-02-20-00.00.00'
order by z.updateStatsTime 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