zOs/REXX/QZT071
$#=
$*( --- for test with wsh -------------
$>. fEdit()
$=rz=RR2
$=dbSys=DBOF
$=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',
// REGION=0M,TIME=1440,CLASS=M1,SCHENV=DB2ALL,
// MSGCLASS=E
$@[
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
//*
//* 7. 1.16 validBegin validEnd ZuerichTime), kein state mehr|
//* updateStatsTime bleibt original
$*( history
5. 1.16 nur noch eine Utility fuer TS bzw. IX
updateStatsTime wird auf ZürichTime übersetzt
origStatsTime enthält originalZeit
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
--- copy changed tablePart rows to rz/dbSys ---------------------------
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.validBegin)
|| char(a.updateStatsTime)
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 validBegin desc
fetch first 1 row only
) beSt
from $tb t
where rz = '?' and dbSys = '?'
)
, t as
( --- decode beSt and select only rows with changed updateStatsTime
select t2.*
, timestamp(substr(beSt, 1, 26)) prBeg
from t2
where beSt is null
or timestamp(substr(beSt, 27, 26)) <> updateStatsTime
)
--- select compute all columns to insert
select '$rz' RZ
, '$dbSys' DBSYS
, value(case --- statsTime in our calendar,
--- must be strictly increasing
--- should be <= loadTs
when ( t.updatestatsTime $diDy > prBeg
or prBeg is null )
and t.updatestatsTime $diDy
<= '$nowM'
then t.updatestatsTime $diDy
when '$nowM' <= prBeg
then trunc_timestamp(prBeg, 'mi') + 2 minutes
end, '$nowM') validBegin
, timestamp('9999-12-30-00.00.00') validEnd
, timestamp('$nowM') loadTs
, TSTYPE
, TSTY
, PGSIZE
, SEGSIZE
, PARTS
, MAXPARTS
, DSSIZE
, DSGB
, LIMGB
, LIMPART
, OBID
, CLONE
, TSINST
, TBCR
, TB
, TBTY
, TBOBID
, t.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
ENDEXEC
EXEC SQL
--- update validEnd of previous rows -----------------------------------
update $tb u
set validEnd =
( select n.validBegin
from $tb n
where u.rz = n.rz and u.dbSys = n.dbSys
and u.dbName = n.dbName and u.name = n.name
and u.partition = n.partition
and u.instance = n.instance
and u.validBegin < n.validBegin
and u.validEnd > n.validBegin
order by n.validBegin asc
fetch first 1 row only
)
where rz = '$rz' and dbSys = '$dbSys'
and validEnd > '9000-01-01-00.00.00'
and exists
( select 1
from $tb n
where u.rz = n.rz and u.dbSys = n.dbSys
and u.dbName = n.dbName and u.name = n.name
and u.partition = n.partition
and u.instance = n.instance
and u.validBegin < n.validBegin
and u.validEnd > n.validBegin
)
ENDEXEC
EXEC SQL
--- update validEnd for rows of dropped tablePartitions ---------------
update $tb u
set validEnd = '$nowM'
where rz = '$rz' and dbSys = '$dbSys'
and validBegin <= '$nowM'
and validEnd > '$nowM'
and not exists
( select 1 from $tb q
where q.rz = '?' and q.dbSys = '?'
and u.dbName = q.dbName and u.name = q.name
and u.partition = q.partition
and u.instance = q.instance
)
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
--- copy changed indexPart rows to rz/dbSys ---------------------------
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.validBegin)
|| char(a.updateStatsTime)
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 validBegin desc
fetch first 1 row only
) beSt
from $tb t
where rz = '?' and dbSys = '?'
)
, t as
( --- decode beSt and select only rows with changed updateStatsTime
select t2.*
, timestamp(substr(beSt, 1, 26)) prBeg
from t2
where beSt is null
or timestamp(substr(beSt, 27, 26)) <> updateStatsTime
)
--- select compute all columns to insert
select '$rz' RZ
, '$dbSys' DBSYS
, value(case --- statsTime in our calendar,
--- must be strictly increasing
--- should be <= loadTs
when ( t.updatestatsTime $diDy > prBeg
or prBeg is null )
and t.updatestatsTime $diDy
<= '$nowM'
then t.updatestatsTime $diDy
when '$nowM' <= prBeg
then trunc_timestamp(prBeg, 'mi') + 2 minutes
end, '$nowM') validBegin
, timestamp('9999-12-30-00.00.00') validEnd
, timestamp('$nowM') loadTs
, INDEXTYPE
, COMPRESS
, IXPARTS
, IXPGSZ
, PIECESIZE
, PIECEGB
, LIMGB
, TBCREATOR
, TBNAME
, TS
, TSTY
, TSPARTS
, TSCLONE
, TSINST
, TSDSSIZE
, TSDSGB
, TSLIMGB
, TSLIMPART
, TSPGSZ
, t.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
--- update validEnd of previous rows -----------------------------------
update $tb u
set validEnd =
( select n.validBegin
from $tb n
where u.rz = n.rz and u.dbSys = n.dbSys
and u.dbName = n.dbName and u.ts = n.ts
and u.indexSpace = n.indexSpace
and u.partition = n.partition
and u.instance = n.instance
and u.validBegin < n.validBegin
and u.validEnd > n.validBegin
order by n.validBegin asc
fetch first 1 row only
)
where rz = '$rz' and dbSys = '$dbSys'
and validEnd > '9000-01-01-00.00.00'
and exists
( select 1
from $tb n
where u.rz = n.rz and u.dbSys = n.dbSys
and u.dbName = n.dbName and u.ts = n.ts
and u.indexSpace = n.indexSpace
and u.partition = n.partition
and u.partition = n.partition
and u.instance = n.instance
and u.validBegin < n.validBegin
and u.validEnd > n.validBegin
)
ENDEXEC
EXEC SQL
--- update validEnd for rows of dropped indexPartitions ---------------
update $tb u
set validEnd = '$nowM'
where rz = '$rz' and dbSys = '$dbSys'
and validBegin <= '$nowM'
and validEnd > '$nowM'
and not exists
( select 1 from $tb q
where q.rz = '?' and q.dbSys = '?'
and u.dbName = q.dbName and u.ts = q.ts
and u.indexSpace = q.indexSpace
and u.partition = q.partition
and u.instance = q.instance
)
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(updateStatsTime) "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 (validBegin >= '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.validBegin < '2015-02-20-00.00.00'
order by z.validBegin 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