zOs/SQL/REOCRVW
------------------------------------------------------------------------
-- create views for reo
-- 28.9.10 W.keller v5.7
------------------------------------------------------------------------
set current sqlId = 's100447';
--drop view S100447.vReoTSSchwelle ;
--drop view S100447.vReoIxSchwelle ;
--drop view S100447.vReoJobParms ;
--drop view S100447.vReoRunRng;
--drop view S100447.vReoRunTSStats;
--drop view S100447.vReoRunIXStats;
--drop view S100447.vReoTSStatsPlus;
--
commit;
------------------------------------------------------------------------
-- view vReoTSSchwelle: Schwellwerte pro Catalog Objekt
-- und alle später benötigen Werte aus Catalog Tabellen
--
CREATE VIEW S100447.vReoTSSchwelle AS
with sw as
(
SELECT
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
char(STRIP(CHAR(PRIO)), 2) prC2,
e.*
FROM s100447.TReoTSParms e
)
select p.dbName db, p.tsName ts, p.partition part,
min(s.dbId) dbid, min(s.psId) psId,
min(s.pgSize) pgSize, min(s.partitions) partitions,
min(p.createdTS) createdTS, min(p.compress) compress,
min(p.pageSave) pageSave ,
min(smallint(round(
case when p.freePage > 0 then p.freePage/real(1+p.freePage)
else 1 end
* min(100 - p.pctFree,
case when s.maxrows > 0 and p.avgRowLen > 0
then real(s.maxrows) * p.avgRowLen / pgSize/10.24
else 100 end)
,0))) pctLoad,
substr(max(prC2 || char(reorg )), 3) swReorg ,
int(substr(max(prC2 || char(unclust )), 3)) swUnclust ,
int(substr(max(prC2 || char(sw.FARINDREF )), 3)) swFARINDREF ,
int(substr(max(prC2 || char(sw.NEARINDREF)), 3)) swNEARINDREF,
int(substr(max(prC2 || char(sw.EXTENTS )), 3)) swEXTENTS ,
int(substr(max(prC2 || char(REORGDAYS )), 3)) swREORGDAYS ,
int(substr(max(prC2 || char(INSERTS )), 3)) swINSERTS ,
int(substr(max(prC2 || char(UPDATES )), 3)) swUPDATES ,
int(substr(max(prC2 || char(DELETES )), 3)) swDELETES ,
substr(max(prC2 || advisory ), 3) swAdvisory ,
int(substr(max(prC2 || char(datasize )), 3)) swDatasize ,
int(substr(max(prC2 || char(sw.pageSave )), 3)) swPageSave ,
int(substr(max(prC2 || char(rangeI0 )), 3)) swRangeI0 ,
smallint(substr(max(prC2 || char(parallel )), 3)) parallel
from
SYSIBM.SYSTABLEPart p
left join SYSIBM.SYSTABLESPACE S
on s.dbName = p.dbName and s.name = p.tsName
left join sw
on left(p.dbname, dbLen) = left(db, dbLen)
and left(p.tsName, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by p.dbName, p.tsName, p.partition
;
------------------------------------------------------------------------
-- RTS-Stats Werte pro TS-Partition zu Schwellen joinen
-- Kolonnen Namen: sw*: Schwellen, ix* selbe IndexPartion,
-- i0* nicht part. Indexes für part. TS
--
create view S100447.vReoTsStats as
with ix as -- join ix und i0
(
select s.db, s.ts, s.part,
min(s.dbId) ixDbid, min(s.psId) ixPsId,
min(s.pgSize) pgSize, min(s.partitions) partitions,
min(s.createdTS) createdTS, min(s.compress) compress,
min(s.pageSave ) pageSave ,
min(s.pctLoad ) pctLoad ,
min(swReorg ) swReorg ,
min(swUnclust ) swUnclust ,
min(swFARINDREF ) swFARINDREF ,
min(swNEARINDREF) swNEARINDREF,
min(swEXTENTS ) swEXTENTS ,
min(swREORGDAYS ) swREORGDAYS ,
min(swINSERTS ) swINSERTS ,
min(swUPDATES ) swUPDATES ,
min(swDELETES ) swDELETES ,
min(swAdvisory ) swAdvisory ,
min(swDatasize ) swDatasize ,
min(swPageSave ) swPageSave ,
min(swRangeI0 ) swRangeI0 ,
min(parallel ) swParallel ,
min(substr(strip(t.creator) || '.' || strip(t.name), 1,30)) crTb,
sum(case when j.partition= s.part then 1 else 0 end)
ixParts,
sum(case when j.partition= s.part
then totalEntries else 0 end) ixEnt,
sum(case when j.partition= s.part
then real(j.totalEntries)
* log10(max(1e0, j.totalEntries)) else 0 end) ixEntLog,
sum(case when j.partition= s.part
then real(j.space) * 1024 else 0 end) ixSpc,
sum(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) ixSpcLog,
sum(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) ixSpcLogEnt,
max(case when j.partition= s.part
then j.totalEntries else 0 end) ixEntMax,
max(case when j.partition= s.part
then real(j.totalEntries) * log10(
max(1e0, j.totalEntries)) else 0 end) ixEntLogMax,
max(case when j.partition= s.part
then real(j.space) * 1024 else 0 end) ixSpcMax,
max(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) ixSpcLogMax,
max(case when j.partition= s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) ixSpcLogEntMax,
sum(case when j.partition<>s.part then 1 else 0 end)
i0Parts,
sum(case when j.partition<>s.part
then totalEntries else 0 end) i0Ent,
sum(case when j.partition<>s.part
then real(j.totalEntries)
* log10(max(1e0, j.totalEntries)) else 0 end) i0EntLog,
sum(case when j.partition<>s.part
then real(j.space) * 1024 else 0 end) i0Spc,
sum(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) i0SpcLog,
sum(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) i0SpcLogEnt,
max(case when j.partition<>s.part
then j.totalEntries else 0 end) i0EntMax,
max(case when j.partition<>s.part
then real(j.totalEntries) * log10(
max(1e0, j.totalEntries)) else 0 end) i0EntLogMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 else 0 end) i0SpcMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, real(j.space) * 1024)) else 0 end) i0SpcLogMax,
max(case when j.partition<>s.part
then real(j.space) * 1024 * log10(
max(1e0, j.totalEntries)) else 0 end) i0SpcLogEntMax,
max(case when i.clustering <> 'Y' then -9
when i.statstime < '2008-01-01-00.00.00' then -8
when i.fullKeyCardf < 100 then -7
when i.clusterratioF <= 0.0 then -6
else clusterRatio end) clusterRatio
from
S100447.vReoTSSchwelle s
left join sysIbm.sysTables t
on t.dbName = s.db and t.tsName = s.ts
-- and t.dbid = s.dbid
and t.type = 'T'
left join sysIbm.sysIndexes i
on i.tbCreator = t.creator and i.tbName = t.name
-- and s.db = i.dbName and s.dbId = i.dbId
left join SYSIBM.SYSINDEXSPACESTATS j
on j.creator = i.creator and j.Name = i.name
and j.dbName = i.dbName and j.indexSpace = i.indexSpace
and j.dbId = i.dbId and j.isoBid = i.isoBid
-- and j.dbId = s.dbId and j.dbName = s.db
and j.creator = i.creator and j.Name = i.name
and j.partition in (0, s.part)
and (j.partition = 0
or j.partition = s.part)
group by s.db, s.ts, s.part
) , tsPa as
( -- join tsPart Stats and calculate first formulas
select i0PARTS * 2.8568338 + i0SpcMax * 1.4917387E-07
+ i0SpcLogMax * 3.4002310E-09 i0Time,
i.*,
max(CAST(r.totalRows as REAL), 100) rRows,
max(coalesce(r.reorgLasttime, i.createdTs)
,coalesce(r.loadRLasttime, i.createdTs)) lastBuilt,
coalesce(real(r.datasize),
real(r.nActive) * i.pgSize * 1024 ) dataSz,
case when r.uncompressedDatasize > 0
and real(r.nActive) * i.pgSize * 102400
/ min(max(100-i.pageSave, 3), 100)
between real(r.uncompressedDatasize) / 7
and real(r.uncompressedDatasize) * 7
then real(r.uncompressedDatasize)
else
real(r.nActive) * i.pgSize * 102400
/ min(max(100-i.pageSave, 3), 100)
end uncompSz,
r.*
from ix i
left join sysIbm.sysTableSpaceStats r
on i.db = r.dbName and i.ts = r.name
and i.ixDbId = r.dbId and i.ixPsId = r.psId
and i.part = r.partition
) -- remaining formulas
select real(2.7397410 + 1 * 2.2918106) -- const + tsParts *
+ rRows * 1.1719944E-05
+ real(space) * (1024 * 4.1178398E-08)
+ uncompSz * 4.7357392E-09
+ ixPARTS * 5.9228624
+ ixSPC * 1.1593550E-08
+ i0Time reorgTime, tsPa.*
from tsPa
;
------------------------------------------------------------------------
-- vReoTS: SchwellwertUeberschreitungen herausfinden
-- in riesigem case statement
--
create view S100447.vReoTS as
with ts1 as
(
select
case
when swReorg = 'NEVER' then 'no - reorgNever'
when staDb.sta like 'RO%' or staDb.sta like 'STOP%'
then 'no - db status ' || strip(staDb.sta)
when staTs.sta like 'RO%' or staTs.sta like 'STOP%'
then 'no - ts status ' || strip(staTs.sta)
when swReorg = 'ALWAYS' then 'reorgAlways'
when swAdvisory = '1'
and posStr(',' || strip(staTs.sta) || ',', ',AREO*,') > 0
then 'advisory status ' || strip(staTs.sta)
when dbName is null then 'rtsMissing'
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
then 'rtsNull'
when rRows is null then 'rtsRowsNull'
when totalRows < 0 then 'rtsRows ' || strip(char(totalRows))
|| ' < 0'
when REORGUNCLUSTINS > rRows / 100 * swUnclust
then 'unclust ' || strip(char(REORGUNCLUSTINS)) || ' > '
|| strip(char(swUnclust)) || '% of ' || char(totalRows)
when clusterRatio >= 0 and totalRows > 1000
and clusterRatio < 100 -swUnclust
then 'clusterRatio ' || strip(char(clusterRatio))
|| ' < 100-' || strip(char(swUnclust))
when REORGFARINDREF > rRows / 100 * swFarindref
then 'farIndRef ' || strip(char(reorgFarIndRef)) || ' > '
|| strip(char(swFarIndRef))||'% of '
|| strip(char(totalRows))
when REORGNEARINDREF > rRows / 100 * swNearindref
then 'nearIndRef '||strip(char(reorgnearIndRef)) || ' > '
|| strip(char(swNearIndRef))||'% of '
|| strip(char(totalRows))
when r.extents > swExtents
then 'extents ' || strip(char(r.extents)) || ' > '
|| strip(char(swExtents))
when r.dataSize >= 0 and r.dataSize
< real(nActive) * r.pgSize
* 0.1024 * pctLoad * swdatasize - 1e7
then 'datasize ' || strip(char(real(r.datasize)))
|| ' < ' || strip(char(swdatasize)) || '% activeSz '
|| strip(char(real(nActive) * r.pgSize * 1024))
when compress = 'Y' and pageSave < swpageSave
and reorgInserts > rRows * .9 and rRows > 1e4
then 'pagesave ' || strip(char(pagesave))
|| ' < ' || strip(char(swPageSave)) || ' % '
when reorgInserts > rRows / 100 * swInserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(swInserts)) || '% of '
|| strip(char(totalRows))
when reorgUpdates > rRows / 100 * swUpdates
then 'updates ' || strip(char(reorgUpdates)) || ' > '
|| strip(char(swUpdates)) || '% of '
|| strip(char(totalRows))
when reorgDeletes > rRows / 100 * swDeletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(swDeletes)) || '% of '
|| strip(char(totalRows))
when CURRENT TIMESTAMP - swReorgDays days > lastBuilt
then 'lastBuilt ' || char(date(lastBuilt)) || ' older '
|| strip(char(swReorgDays)) ||' reorgDays'
else 'no - reorg not required'
end reason,
staTs.paFr, staTs.paTo, staTs.sta staTs, staDb.sta staDb,
1 parts,
r.*
from S100447.vReoTsStats r
left join s100447.tDbState staTs
ON staTs.Ty = 'T'
and staTs.db = r.db
AND staTs.sp = r.ts
AND staTs.paFr <= r.part
AND staTs.paTo >= r.part
left join s100447.tDbState staDb
ON staDb.Ty = 'D'
and staDb.db = r.db
)
-- importance anfügen
select smallint(case
when reason = 'no - reorg not required' then 0
when reason like 'no %' then -1
when reason like 'lastBuilt %' then 3
when reason like 'advisory %' then 7
when reason like 'reorgAlway%' then 9
else 5
end) importance ,
ts1.*
from ts1
;
------------------------------------------------------------------------
-- view vReoIXSchwelle: Schwellwerte pro Catalog Objekt
-- und alle später benötigen Werte aus Catalog Tabellen
--
create view S100447.vReoIXSchwelle as
with sw as
( select
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
CASE WHEN POSSTR(IX, '*') > 0
THEN POSSTR(IX, '*') - 1 ELSE 20 END IXLEN,
char(STRIP(CHAR(PRIO)), 2) prC2,
e.*
from s100447.tReoIXParms e
)
select i.creator cr, i.name ix, p.partition part,
substr(max(prC2 || char(reorg )), 3) swReorg ,
int(substr(max(prC2 || char(pageSplits )), 3)) swPageSplits,
int(substr(max(prC2 || char(sw.EXTENTS )), 3)) swEXTENTS ,
int(substr(max(prC2 || char(REORGDAYS )), 3)) swREORGDAYS ,
int(substr(max(prC2 || char(INSERTS )), 3)) swINSERTS ,
int(substr(max(prC2 || char(DELETES )), 3)) swDELETES ,
int(substr(max(prC2 || char(pseudoDel )), 3)) swPseudoDel ,
min(i.dbName) db, min(i.indexSpace) is, min(t.tsName) ts,
min(i.dbId) dbId, min(i.isoBid) isoBid,
min(p.createdTS) createdTS,
min(t.creator) tbCr, min(t.name) tb
from
SYSIBM.sysIndexes I
join SYSIBM.SYSTABLES T
on i.tbCreator = t.creator and i.tbName = t.name
and i.dbName = t.dbName
join SYSIBM.SYSIndexPart p
on p.ixCreator = i.creator and p.ixName = i.name
, sw
where left(i.dbName, dbLen) = left(db, dbLen)
and left(i.name, ixLen) = left(ix, ixLen)
and left(t.tsName, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by i.creator, i.name, p.partition
;
------------------------------------------------------------------------
-- RTS-Stats Werte pro ix-Partition zu Schwellen joinen
-- Kolonnen Namen: sw*: Schwellen
-- ?????
create view S100447.vReoIxStats as
select cr, ix, part, db, is, ts, tbCr, tb, createdTS,
swReorg , swPageSplits, swEXTENTS , swREORGDAYS ,
swINSERTS , swDELETES , swPseudoDel ,
max(CAST(TOTALEntries AS REAL), 100) rEntrs,
cast(nActive AS REAL) rActive,
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS),
coalesce(rebuildLastTime, createdTS)) lastBuilt ,
coalesce(real(max(r.space, 0)) * 1024 * 1.3558420E-07 , 0)
+ 1.8626988 reorgTime ,
r.*
from S100447.vReoIXSchwelle s
left join sysibm.sysIndexSpaceStats r
on r.DBID = S.DBID
AND r.ISOBID = S.ISOBID
AND r.DBNAME = S.DB
AND r.indexSpace = S.is
AND r.partition = s.part
;
------------------------------------------------------------------------
-- vReoIx: SchwellwertUeberschreitungen herausfinden
-- in riesigem case statement
--
create view S100447.vReoIX as
with ix1 as
( select
case
when swReorg = 'NEVER' then 'no - reorgNever'
when staDb.sta like 'RO%' or staDb.sta like 'STOP%'
then 'no - db status ' || strip(staDb.sta)
when staTs.sta like 'STOP%'
then 'no - ts status ' || strip(staTs.sta)
when staIx.sta like 'RO%' or staIx.sta like 'STOP%'
then 'no - ix status ' || strip(staIx.sta)
when swReorg = 'ALWAYS' then 'reorgAlways'
when indexSpace is null then 'rtsMissing'
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
and REBUILDLASTTIME IS NULL then 'rtsNull'
when rEntrs is null then 'rtsRowsNull'
when totalEntries < 0 then 'rtsEntries '
|| strip(char(totalEntries)) || ' < 0'
when REORGLEAFFAR > rActive / 100 * swPageSplits
then 'pageSplits ' || strip(char(reorgLeafFar)) || ' > '
|| strip(char(swPageSplits))
|| '% of ' || strip(char(nActive))
when extents > swExtents
then 'extents ' || strip(char(extents)) || ' > '
|| strip(char(swExtents))
when reorgInserts > rEntrs / 100 * swInserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(swInserts)) || '% of '
|| strip(char(totalEntries))
when reorgDeletes > rEntrs / 100 * swDeletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(swDeletes)) || '% of '
|| strip(char(totalEntries))
when reorgPseudoDeletes > rEntrs / 100 * swPseudoDel
then 'pseudoDel ' || strip(char(reorgPseudoDeletes))||' > '
|| strip(char(swPseudoDel)) || '% of '
|| strip(char(totalEntries))
when CURRENT TIMESTAMP - swReorgDays days > lastBuilt
then 'lastBuilt ' || char(date(lastBuilt)) || ' older '
|| strip(char(swReorgDays)) ||' reorgDays'
else 'no - reorg not required'
end reason, s.*
from S100447.vReoIXStats s
left join s100447.tDbState staDb
ON staDb.Ty = 'D'
and staDb.db = s.db
left join s100447.tDbState staTs
ON staTs.Ty = 'T'
and staTs.db = s.db
AND staTs.sp = s.ts
AND staTs.paFr <= s.part
AND staTs.paTo >= s.part
left join s100447.tDbState staIx
ON staIx.Ty = 'I'
and staIx.db = s.db
AND staIx.sp = s.is
AND staIx.paFr <= s.part
AND staIx.paTo >= s.part
)
-- importance voranstellen
select smallint(case
when reason = 'no - reorg not required' then 0
when reason like 'no %' then -1
when reason like 'lastBuilt %' then 3
when reason like 'advisory %' then 7
when reason like 'reorgAlway%' then 9
else 5
end) importance,
ix1.*
from ix1
;
------------------------------------------------------------------------
-- vReoJobParms: jobLen und prC2 anfügen
--
CREATE VIEW S100447.vReoJobParms AS
SELECT e.*,
CASE WHEN POSSTR(job, '*') > 0
THEN POSSTR(job, '*') - 1 ELSE 8 END jobLEN,
char(STRIP(CHAR(PRIO)), 2) prC2
FROM s100447.TReoJobParms e
;
select
int(substr(max(prC2 || char(tsTime)), 3)) tsTime ,
int(substr(max(prC2 || char(ixTime)), 3)) ixTime ,
real(substr(max(prC2 || char(uncompDef)), 3)) uncompDef,
real(substr(max(prC2 || char(uncompI0)), 3)) uncompI0 ,
substr(max(prC2 || char(ixSpae)), 3) ixSpae ,
substr(max(prC2 || char(stats )), 3) stats ,
count(*) cnt, min(job) minJ, max(job) maxJ
from S100447.vReoJobParms
where left(job, jobLen) = left('QR01234P', jobLen)
-- group by p.dbName, p.tsName, p.partition
;
create view S100447.vReoRunRng as
with r1 as
( select p.tst, p.rng, max(paVon) paVon, max(paBis) paBis,
count(*) cnt
, sum(case when reoTst is null then 0 else 1 end) cntReoTst
, max(case when reoTst is null then tst
else reoTst end) reoEnd
from s100447.tReoRunPart p
group by tst, rng
) , r2 as
( select r.*,
( select coalesce(max(coalesce(o.reoEnd, o.tst))
, r.tst)
from r1 o
where o.tst = r.tst
and o.rng <> r.rng and o.reoEnd < r.reoEnd
) reoBeg
from r1 r
)
select
(days(reoEnd) - days(reoBeg)) * 86400
+ midnight_seconds(reoEnd) - midnight_seconds(reoBeg) rngTime,
r2.*
from r2
;
CREATE VIEW S100447.vReoRunTsStats AS
with tp as
( select
real(totalRows) rows
, log10(max(real(totalRows),1)) logRows
, real(space) * 1024 spc
, log10(max(real(space), 1)*1024) logSpc
, real(uncompressedDatasize) uds
, log10(max(real(uncompressedDatasize), 1)) logUds
, r.*
from s100447.tReoRunTsStats r
)
, t as
( select tst, rng
, min(partition) partMin
, max(partition) partMax
, count(*) parts
, sum(rows) rows
, max(rows) rowsMax
, sum(rows * logrows) rowsLog
, sum(rows) * max(logRows) rowsLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logRows) spcLogRows
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
, sum(Uds) Uds
, sum(Uds * logUds) UdsLog
, sum(Uds * logRows) UdsLogRows
, max(Uds) UdsMax
, sum(Uds) * max(logUds) UdsLogMax
from tp
group by tst, rng
)
, ip as
( select
real(totalEntries) ent,
log10(max(real(totalEntries),1)) logEnt,
real(space) * 1024 spc,
log10(max(real(space), 1)*1024) logSpc,
r.*
from s100447.tReoRunIxStats r
)
, i as
( select tst, rng, count(*) parts
, sum(ent) ent
, max(ent) entMax
, sum(ent * logEnt) entLog
, sum(ent) * max(logEnt) entLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logEnt) spcLogEnt
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
from ip
group by tst, rng
)
select j.job, r.*
, partMin tsPartMin
, partMax tsPartMax
, value(t .parts , 0) tsParts
, value(t .rows , 0) tsRows
, value(t .rowsMax , 0) tsRowsMax
, value(t .rowsLog , 0) tsRowsLog
, value(t .rowsLogMax , 0) tsRowsLogMax
, value(t .spc , 0) tsSpc
, value(t .spcLog , 0) tsSpcLog
, value(t .spcLogRows , 0) tsSpcLogRows
, value(t .spcMax , 0) tsSpcMax
, value(t .spcLogMax , 0) tsSpcLogMax
, value(t .Uds , 0) tsUds
, value(t .UdsLog , 0) tsUdsLog
, value(t .UdsLogRows , 0) tsUdsLogRows
, value(t .UdsMax , 0) tsUdsMax
, value(t .UdsLogMax , 0) tsUdsLogMax
, value(ix.parts , 0) ixparts
, value(ix.ent , 0) ixent
, value(ix.entMax , 0) ixentMax
, value(ix.entLog , 0) ixentLog
, value(ix.entLogMax , 0) ixentLogMax
, value(ix.spc , 0) ixspc
, value(ix.spcLog , 0) ixspcLog
, value(ix.spcLogEnt , 0) ixspcLogEnt
, value(ix.spcMax , 0) ixspcMax
, value(ix.spcLogMax , 0) ixspcLogMax
, value(i0.parts , 0) i0parts
, value(i0.ent , 0) i0ent
, value(i0.entMax , 0) i0entMax
, value(i0.entLog , 0) i0entLog
, value(i0.entLogMax , 0) i0entLogMax
, value(i0.spc , 0) i0spc
, value(i0.spcLog , 0) i0spcLog
, value(i0.spcLogEnt , 0) i0spcLogEnt
, value(i0.spcMax , 0) i0spcMax
, value(i0.spcLogMax , 0) i0spcLogMax
from s100447.tReoRunJob j, s100447.tReoRunPart r
left join t on t.tst = r.tst and t.rng = r.rng
left join i ix on ix.tst = r.tst and ix.rng = r.rng
left join i i0 on i0.tst = r.tst and i0.rng = r.rngI0
and r.rngI0 > 0
where j.ty = 'TS' and j.sta <> '0' and r.ty = 't' and r.sta = 'r'
and r.part = r.pavon and r.reoTime is not null
and r.tst = j.tst
;
commit;
CREATE VIEW S100447.vReoRunIxStats AS
with ip as
( select
real(totalEntries) ent,
log10(max(1, real(totalEntries))) logEnt,
real(space) * 1024 spc,
log10(max(1, real(space)*1024)) logSpc,
r.*
from s100447.tReoRunIxStats r
)
, i as
( select tst, rng, count(*) parts
, min(partition) partMin
, max(partition) partMax
, sum(ent) ent
, max(ent) entMax
, sum(ent * logEnt) entLog
, sum(ent) * max(logEnt) entLogMax
, sum(spc) spc
, sum(spc * logSpc) spcLog
, sum(spc * logEnt) spcLogEnt
, max(spc) spcMax
, sum(spc) * max(logSpc) spcLogMax
from ip
group by tst, rng
)
select j.job, r.*
, partMin partMin
, partMax partMax
, value(ix.parts , 0) parts
, value(ix.ent , 0) ent
, value(ix.entMax , 0) entMax
, value(ix.entLog , 0) entLog
, value(ix.entLogMax , 0) entLogMax
, value(ix.spc , 0) spc
, value(ix.spcLog , 0) spcLog
, value(ix.spcLogEnt , 0) spcLogEnt
, value(ix.spcMax , 0) spcMax
, value(ix.spcLogMax , 0) spcLogMax
from s100447.tReoRunJob j, s100447.tReoRunPart r
left join i ix on ix.tst = r.tst and ix.rng = r.rng
where j.ty = 'IX' and j.sta <> '0' and r.ty = 'i' and r.sta = 'r'
and r.part = r.pavon and r.reoTime is not null
and r.tst = j.tst
;
select * from S100447.vReoRunIxStats
order by tst desc, rng
fetch first 100 rows only
with ur
;
create view S100447.vReoTSStatsPlus as
select r.*, s.pgSize, p.pageSave,
case when r.uncompressedDatasize > 0
and real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100)
between real(r.uncompressedDatasize) / 7
and real(r.uncompressedDatasize) * 7
then bigInt(r.uncompressedDatasize)
else
bigInt(real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100))
end fixUncomp
from sysibm.sysTableSpaceStats r,
sysibm.sysTableSpace s, sysibm.sysTablePart p
where s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbId and s.psid = r.psid
and p.dbName = r.dbName and p.tsName = r.name
and p.partition = r.partition
;
create view S100447.vReoTSStatsFix as
select 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 ,
fixUncomp UNCOMPRESSEDDATASIZE,
DBNAME ,
NAME
from S100447.vReoTSStatsPlus
;
commit
;
--- end of install v5.7 -------------------------------------
;x;
?????? intermediate and helpers
-- select *
-- from s100447.tReoRunTsStats
-- where uncompressedDatasize is not null
-- order by uncompressedDatasize desc
-- fetch first 1000 rows only
-- with ur;
-- update s100447.tReoRunTsStats u
-- set uncompressedDatasize = (select
-- bigInt(real(u.nActive) * f.pgSize * 102400
-- / min(max(100-f.pageSave, 3), 100))
-- from S100447.vReoTsStatsPlus f
-- where f.dbName = u.dbName and f.name = u.name
-- and f.dbid = u.dbId and f.psid = u.psid
-- and f.partition = u.partition
-- )
-- where u.tst < '2010-08-27-18.00.00.00'
-- or u.uncompressedDatasize < 1
-- ;
-- --commit;
-- select *
-- from s100447.tReoRunTsStats
-- where uncompressedDatasize is not null
-- order by uncompressedDatasize desc
-- fetch first 1000 rows only
-- with ur;
-- commit ;
--;
create view S100447.vReoTSStatsPlus as
select r.*, s.pgSize, p.pageSave,
case when r.uncompressedDatasize > 0
and real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100)
between real(r.uncompressedDatasize) / 7
and real(r.uncompressedDatasize) * 7
then bigInt(r.uncompressedDatasize)
else
bigInt(real(r.nActive) * s.pgSize * 102400
/ min(max(100-p.pageSave, 3), 100))
end fixUncomp
from sysibm.sysTableSpaceStats r,
sysibm.sysTableSpace s, sysibm.sysTablePart p
where s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbId and s.psid = r.psid
and p.dbName = r.dbName and p.tsName = r.name
and p.partition = r.partition
;
create view S100447.vReoTSStatsFix as
select 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 ,
fixUncomp UNCOMPRESSEDDATASIZE,
DBNAME ,
NAME
from S100447.vReoTSStatsPlus
;
select real(space)*1024, real(fixuncomp), pageSave, r.*
from S100447.vReoTSStatsPlus r
where dbName like 'MF01%'
-- order by dbName, name, partition
order by abs(real(space)*1024 - real(fixuncomp)) desc
with ur;
select count(*), sum(real(space)*1024), sum(real(datasize)),
sum(real(fixuncomp))
from S100447.vReoTSStatsPlus r
with ur;
select count(*), sum(real(space)*1024), sum(real(datasize)),
sum(real(uncompressedDatasize))
from S100447.vReoTSStatsFix
with ur;
select count(*), sum(real(space)*1024), sum(real(datasize)),
sum(real(uncompressedDatasize))
from sysibm.sysTableSpaceStats
with ur;
select r.*
from sysibm.sysTableSpaceStats r
left join sysibm.sysTableSpace s
on s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbId and s.psid = r.psid
where s.dbName is null
;
commit
;x;
-----------------------------------------------------------------------
version 2009
-- views erstellen für rts reorg Abfragen für ix und ts
-- tableIxStats: tableSpaceStats ergänzt mit w.keller 18.11.09
-- Index Daten (selbe Part + part 0)
-- vReoIx.is für IndexSpace w.keller 08.9.08
-- (nicht null bei fehlenden RTS Daten)
-- V9 eNFM (SYStablespacestats usw.) w.keller 21.8.08
-- vorbereitet für V9 (cr statt creator) w.keller 21.8.08
-- neu w.keller 30.4.08
set current sqlId = 'S100447';
-- drop view S100447.vReoTSException;
-- drop VIEW S100447.vReoIXException;
-- drop view S100447.tableIxStats;
-- drop view S100447.tableIxStatsDet;
-- tableIxStats: tableSpaceStats ergänzt mit Index Daten
-- für dieselbe Partition und Partition 0 (falls ts.part <> 0)
create view S100447.tableIxStats as
with tsIx as -- Daten aus IndexStats aggregieren pro TS part
( select
max(substr(strip(t.creator) || '.' || strip(t.name), 1,30)) crTb,
t.dbName db, t.tsName ts,
j.partition,
count(*) cnt,
sum(j.totalEntries) ent,
sum(real(j.totalEntries)
* log10(max(1e0, j.totalEntries))) entLog,
sum(real(j.space) * 1024) spc,
sum(real(j.space) * 1024
* log10(max(1e0, real(j.space) * 1024))) spcLog,
sum(real(j.space) * 1024
* log10(max(1e0, j.totalEntries))) spcLogEnt,
max(j.totalEntries) entMax,
max(real(j.totalEntries)
* log10(max(1e0, j.totalEntries))) entLogMax,
max(real(j.space) * 1024) spcMax,
max(real(j.space) * 1024
* log10(max(1e0, real(j.space) * 1024))) spcLogMax,
max(real(j.space) * 1024
* log10(max(1e0, j.totalEntries))) spcLogEntMax
from sysIbm.sysTables t
join sysIbm.sysIndexes i
on i.tbCreator = t.creator and i.tbName = t.name
join SYSIBM.SYSINDEXSPACESTATS j
on i.creator = j.creator and i.Name = j.name
and i.dbName = j.dbName and i.indexSpace = j.indexSpace
and i.dbId = j.dbId and i.isoBid = j.isoBid
where t.type = 'T'
group by t.dbName, t.tsName, t.dbId, j.partition
) , det as
(
select r.*,
1 rParts,
max(coalesce(real(space), 0), 0) * 1024 spc,
max(coalesce(real(totalRows), 0), 0) rows,
coalesce(i.crtb, '') crTb,
-- selbe Partition von partit. Indexen
coalesce(i.cnt, 0) ixParts,
coalesce(i.ent, 0) ixEnt,
coalesce(i.entLog, 0) ixEntLog,
coalesce(i.spc, 0) ixSpc,
coalesce(i.spcLog, 0) ixSpcLog,
coalesce(i.spcLogEnt, 0) ixSpcLogEnt,
coalesce(i.entMax, 0) ixEntMax,
coalesce(i.entLogMax, 0) ixEntLogMax,
coalesce(i.spcMax, 0) ixSpcMax,
coalesce(i.spcLogMax, 0) ixSpcLogMax,
coalesce(i.spcLogEntMax, 0) ixSpcLogEntMax,
-- Part 0 von nicht Partitionierten Indexen in part. TS
coalesce(j.cnt, 0) i0Parts,
coalesce(j.ent, 0) i0Ent,
coalesce(j.entLog, 0) i0EntLog,
coalesce(j.spc, 0) i0Spc,
coalesce(j.spcLog, 0) i0SpcLog,
coalesce(j.spcLogEnt, 0) i0SpcLogEnt,
coalesce(j.entMax, 0) i0EntMax,
coalesce(j.entLogMax, 0) i0EntLogMax,
coalesce(j.spcMax, 0) i0SpcMax,
coalesce(j.spcLogMax, 0) i0SpcLogMax,
coalesce(j.spcLogEntMax, 0) i0SpcLogEntMax
from sysibm.sysTablespaceStats r
left join tsix i
on r.dbName = i.db and r.name = i.ts
and r.partition = i.partition
left join tsix j
on r.dbName = j.db and r.name = j.ts
and r.partition <> 0 and j.partition = 0
)
select det.*,
real( 2.22E+01 * 1 -- real to avoid exponent overflow in sum
+ coalesce(2.23E-08 * space * 1024 + 1.14E-07 * totalRows
* log10(max(1e0, real(totalRows))), 0)
+ 2.66E-07 * ixEntLog
+ 4.94E+00 * i0Parts + 3.84E-08 * i0Spc
+ 3.42E-06 * i0EntMax) reorgTime
from det
;
-- create view S100447.tableIxStats as
-- select dbName db, name ts, partition, crTb,
-- 1 parts, real(space)*1024 spc, real(totalRows) rows,
-- ixParts, ixSpc, ixEnt, ixEntMax,
-- i0Parts, i0Spc, i0Ent, i0EntMax
-- from S100447.tableIxStatsDet
--
-- view vReoTSExceptions: Längen und key anfügen --------------------
CREATE VIEW S100447.vReoTSException AS
SELECT PRIO, db, ts,
PARTVON, PARTBIS, GUVON, GUBIS,
reorg, unclust, farindref, nearindref, extents, reorgdays,
inserts, updates, deletes,
inserttimestamp, remark,
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
char(RIGHT('0' || STRIP(CHAR(PRIO)), 2)
|| strip(DB) || case when POSSTR(DB, '*') > 0
then '' else '=' end
|| strip(ts) || case when POSSTR(ts, '*') > 0
then '' else '=' end
|| right('0000' || strip(char(partVon)), 4)
|| '.' || translate('34679A', char(GUVON, ISO),
'123456789A')
, 31) KEY
FROM S100447.TReoTSException ;
COMMENT ON table S100447.vReoTSException
IS 'diese Tabelle enthaelt die Default-Schwellwerte und die TS mit s
peziellen Schwellwerten für RTS-Reorgs.'
;
LABEL ON S100447.vReoTSException
( PRIO is 'Prioritaet: tiefste=0=Default' ,
DB is 'Datenbank Name' ,
TS is 'TableSpace Name' ,
TSLEN is 'Laenge Datenbank Name' ,
PARTVON is 'Partition von' ,
PARTBIS is 'Partition bis' ,
GUVON is 'Gueltig von' ,
GUBIS is 'Gueltig bis' ,
REORG is 'ALWAYS,NEVER,THRESHOLD' ,
UNCLUST is 'Schwellwert unclustered %' ,
FARINDREF is 'SchwWe overflow far (>16 pg) %',
NEARINDREF is 'SchwWe overflow near(<16 pg) %',
EXTENTS is 'SchwWe Extents pro TS/Part' ,
REORGDAYS is 'Schwellwert Anzahl Tage' ,
INSERTS is 'Schwellwert inserts %' ,
UPDATES is 'Schwellwert updates %' ,
DELETES is 'Schwellwert deletes %' ,
INSERTTIMESTAMP is 'Erstellungs Zeitpunkt' ,
REMARK is 'Begründung (Freitext)' ,
DBLEN is 'Laenge Datenbank Name' ,
TSLEN is 'Laenge Datenbank Name' ,
KEY is 'eindeutiger Schluessel'
) ;
-- view vReoTSSchwellen: Schwellwerte pro Catalog Objekt -----------
create view S100447.vReoTSSchwelle as
select s.dbName db, s.name ts, p.partition part,
substr(max(key || char(reorg )), 32) reorg ,
int(substr(max(key || char(unclust )), 32)) unclust ,
int(substr(max(key || char(INSERTS )), 32)) INSERTS ,
int(substr(max(key || char(UPDATES )), 32)) UPDATES ,
int(substr(max(key || char(DELETES )), 32)) DELETES ,
int(substr(max(key || char(e.FARINDREF )), 32)) FARINDREF ,
int(substr(max(key || char(e.NEARINDREF)), 32)) NEARINDREF,
int(substr(max(key || char(e.EXTENTS )), 32)) EXTENTS ,
int(substr(max(key || char(REORGDAYS )), 32)) REORGDAYS ,
s.pgSize, s.partitions, s.dbId, s.psId,
p.createdTS, p.compress, p.pageSave
from
SYSIBM.SYSTABLESPACE S
join SYSIBM.SYSTABLEPart p
on s.dbName = p.dbName and s.name = p.tsName
, S100447.vReoTSException e
where left(s.dbname, dbLen) = left(db, dbLen)
and left(s.name, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by s.dbName, s.name, p.partition, s.pgSize, s.partitions,
s.dbId, s.psId, p.createdTS, p.compress, p.pageSave
;
-- view vReoTS: SchwellwerteUeberschreitungen pro RTS Obj -----------
create view S100447.vReoTS as
with ts1 as
(
select
case
when reorg = 'NEVER' then 'no - reorgNever'
when staDb.sta like 'RO%' or staDb.sta like 'STOP%'
then 'no - db status ' || strip(staDb.sta)
when staTs.sta like 'RO%' or staTs.sta like 'STOP%'
then 'no - ts status ' || strip(staTs.sta)
when reorg = 'ALWAYS' then 'reorgAlways'
when dbName is null then 'rtsMissing'
when posStr(',' || strip(staTs.sta) || ',', ',AREO*,') > 0
then 'advisory status ' || strip(staTs.sta)
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
then 'rtsNull'
when rRows is null then 'rtsRowsNull'
when REORGUNCLUSTINS > rRows / 100 * unclust
then 'unclust ' || strip(char(REORGUNCLUSTINS)) || ' > '
|| strip(char(unclust)) || '% of ' || char(totalRows)
when REORGFARINDREF > rRows / 100 * farindref
then 'farIndRef ' || strip(char(reorgFarIndRef)) || ' > '
|| strip(char(farIndRef)) || '% of ' || char(totalRows)
when REORGNEARINDREF > rRows / 100 * nearindref
then 'nearIndRef '||strip(char(reorgnearIndRef)) || ' > '
|| strip(char(nearIndRef)) ||'% of ' || char(totalRows)
when r.extents > s.Extents
then 'extents ' || strip(char(r.extents)) || ' > '
|| strip(char(s.Extents))
when dataSize >= 0
and dataSize < real(nActive) * pgSize * 256 - 1e7
then 'datasize ' || strip(char(real(datasize)))
|| ' << activeSz '
|| strip(char(real(nActive) * pgSize * 1024))
when compress = 'Y' and pageSave < 5
and reorgInserts > rRows * .9 and space > 1e4
then 'pagesave ' || strip(char(pagesave))
|| '%, inserts ' || strip(char(reorgInserts))
|| ', rows ' || strip(char(totalRows))
when reorgInserts > rRows / 100 * inserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(inserts)) || '% of ' || char(totalRows)
when reorgUpdates > rRows / 100 * updates
then 'updates ' || strip(char(reorgUpdates)) || ' > '
|| strip(char(updates)) || '% of ' || char(totalRows)
when reorgDeletes > rRows / 100 * deletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(deletes)) || '% of ' || char(totalRows)
when DAYS(CURRENT TIMESTAMP) - DAYS(
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS))) > reorgDays
then 'reorgDays ' || char(date(
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS))))
|| ' > ' || strip(char(reorgDays)) ||' days'
else 'no - reorg not required'
end reason,
s.db, s.ts, s.part, s.pgSize, s.partitions, s.compress, s.pageSave,
staTs.paFr, staTs.paTo, staTs.sta staTs, staDb.sta staDb,
1 parts, r.*
from
S100447.vReoTSSchwelle s
left join
( select max(CAST(TOTALROWS AS REAL), 100) rRows, t.*
from S100447.tableIxStats T
) r
ON r.DBID = S.DBID
AND r.PSID = S.PSID
AND r.DBNAME = S.DB
AND r.NAME = S.ts
AND r.partition = s.part
left join oa1a.TADM36A1 staTs
ON staTs.Ty = 'T'
and staTs.db = s.db
AND staTs.sp = s.ts
AND staTs.paFr <= s.part
AND staTs.paTo >= s.part
left join oa1a.TADM36A1 staDb
ON staDb.Ty = 'D'
and staDb.db = s.db
)
select case when reason like 'no %' then 0
when reason like 'reorgDays %' then 3
when reason like 'advisory %' then 7
when reason like 'reorgAlway%' then 9
else 5 end class ,
ts1.*
from ts1
;
-- view vReoIXExceptions: Längen und key anfügen --------------------
CREATE VIEW S100447.vReoIXException AS
SELECT PRIO, db, ts, ix,
PARTVON, PARTBIS, GUVON, GUBIS,
reorg, pageSplits, extents, reorgdays,
inserts, deletes, pseudoDel,
inserttimestamp, remark,
CASE WHEN POSSTR(DB, '*') > 0
THEN POSSTR(DB, '*') - 1 ELSE 8 END DBLEN,
CASE WHEN POSSTR(TS, '*') > 0
THEN POSSTR(TS, '*') - 1 ELSE 8 END TSLEN,
CASE WHEN POSSTR(IX, '*') > 0
THEN POSSTR(IX, '*') - 1 ELSE 20 END IXLEN,
char(RIGHT('0' || STRIP(CHAR(PRIO)), 2)
|| strip(DB) || case when POSSTR(DB, '*') > 0
then '' else '=' end
|| strip(ts) || case when POSSTR(ts, '*') > 0
then '' else '=' end
|| strip(ix) || case when POSSTR(ix, '*') > 0
then '' else '=' end
|| right('0000' || strip(char(partVon)), 4)
|| '.' || translate('34679A', char(GUVON, ISO),
'123456789A')
, 52) KEY
FROM S100447.TReoIXException ;
COMMENT ON table S100447.vReoIXException
IS 'diese Tabelle enthaelt die Default-Schwellwerte und die Indexe m
it speziellen Schwellwerten für RTS-Reorgs.'
;
LABEL ON S100447.vReoIXException
( PRIO is 'Prioritaet: tiefste=0=Default' ,
DB is 'Datenbank Name' ,
TS is 'TableSpace Name' ,
IX is 'Index Name' ,
PARTVON is 'Partition von' ,
PARTBIS is 'Partition bis' ,
GUVON is 'Gueltig von' ,
GUBIS is 'Gueltig bis' ,
REORG is 'ALWAYS,NEVER,THRESHOLD' ,
PAGESPLITS is 'Schwellwert ReorgLeafFar %' ,
EXTENTS is 'SchwWe Extents pro TS/Part' ,
REORGDAYS is 'Schwellwert Anzahl Tage' ,
INSERTS is 'Schwellwert inserts %' ,
DELETES is 'Schwellwert deletes %' ,
PseudoDel is 'Schwellwert PseudoDeletes %' ,
INSERTTIMESTAMP is 'Erstellungs Zeitpunkt' ,
REMARK is 'Begründung (Freitext)' ,
DBLEN is 'Laenge Datenbank Name' ,
TSLEN is 'Laenge Datenbank Name' ,
IXLEN is 'Laenge Index Name' ,
KEY is 'eindeutiger Schluessel'
) ;
-- view vReoIXSchwelle: Schwellwerte pro Catalog Objekt -----------
create view S100447.vReoIXSchwelle as
select i.creator cr, i.name ix, p.partition part,
substr(max(key || char(reorg )), 53) reorg ,
int(substr(max(key || char(pageSplits )), 53)) pageSplits,
int(substr(max(key || char(e.EXTENTS )), 53)) EXTENTS ,
int(substr(max(key || char(REORGDAYS )), 53)) REORGDAYS ,
int(substr(max(key || char(INSERTS )), 53)) INSERTS ,
int(substr(max(key || char(DELETES )), 53)) DELETES ,
int(substr(max(key || char(pseudoDel )), 53)) pseudoDel ,
i.dbName db, i.indexSpace is, t.tsName ts,
i.dbId, i.isoBid, p.createdTS
from
SYSIBM.sysIndexes I
join SYSIBM.SYSTABLES T
on i.tbCreator = t.creator and i.tbName = t.name
join SYSIBM.SYSIndexPart p
on p.ixCreator = i.creator and p.ixName = i.name
, S100447.vReoIXException e
where left(i.dbName, dbLen) = left(db, dbLen)
and left(i.name, ixLen) = left(ix, ixLen)
and left(t.tsName, tsLen) = left(ts, tsLen)
and p.partition between partVon and partBis
and current date between guVon and guBis
group by i.creator, i.name, p.partition,
i.dbName, i.indexSpace, t.tsName,
i.dbId, i.isoBid, p.createdTS
;
-- view vReoIX: SchwellwerteUeberschreitungen pro RTS Obj -----------
create view S100447.vReoIX as
select
case
when reorg = 'NEVER' then 'no - reorgNever'
when reorg = 'ALWAYS' then 'reorgAlways'
when r.indexSpace is null then 'rtsMissing'
when REORGLASTTIME IS NULL and LOADRLASTTIME IS NULL
and REBUILDLASTTIME IS NULL then 'rtsNull'
when rEntrs is null then 'rtsRowsNull'
when REORGLEAFFAR > rActive / 100 * pageSplits
then 'pageSplits ' || strip(char(reorgLeafFar)) || ' > '
|| strip(char(pageSplits))
|| '% of ' || strip(char(nActive))
when r.extents > s.Extents
then 'extents ' || strip(char(r.extents)) || ' > '
|| strip(char(s.Extents))
when reorgInserts > rEntrs / 100 * inserts
then 'inserts ' || strip(char(reorgInserts)) || ' > '
|| strip(char(inserts)) || '% of ' || char(totalEntries)
when reorgDeletes > rEntrs / 100 * deletes
then 'deletes ' || strip(char(reorgDeletes)) || ' > '
|| strip(char(deletes)) || '% of ' || char(totalEntries)
when reorgPseudoDeletes > rEntrs / 100 * pseudoDel
then 'pseudoDel ' || strip(char(reorgPseudoDeletes))||' > '
|| strip(char(pseudoDel)) ||'% of '|| char(totalEntries)
when DAYS(CURRENT TIMESTAMP) - DAYS(
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS),
coalesce(rebuildLastTime, createdTS))) > reorgDays
then 'reorgDays ' || char(date(
max(coalesce(reorgLastTime, createdTS),
coalesce(loadrLastTime, createdTS),
coalesce(rebuildLastTime, createdTS))))
|| ' > ' || strip(char(reorgDays)) ||' days'
else 'no - reorg not required'
end reason, s.cr, s.ix, s.db, s.is, s.ts, s.part, r.*
from
S100447.vReoIXSchwelle s
left join
( select max(CAST(TOTALEntries AS REAL), 100) rEntrs,
CAST(nActive AS REAL) rActive, t.*
from SYSIBM.SYSINDEXSPACESTATS T
) r
ON r.DBID = S.DBID
AND r.ISOBID = S.ISOBID
AND r.DBNAME = S.DB
AND r.indexSpace = S.is
AND r.partition = s.part
;
commit
;