zOs/SQL/CATIXPLV
with dum as
( select * from sysibm.sysDummy1
)
-- copy catIxPlu begin
-- ixPlus: sysibm.sysIndexes plus missing infos
-- copy catTsPlu begin
-- tsPlus: sysIbm.sysTableSpace plus missing infos
, tsPlus2 as
(
select s.*
, case
when type <> ' ' then type
when partitions > 0 and segsize = 0 then 'p' -- classic part
when partitions = 0 and segsize = 0 then 'i' -- simple
when partitions = 0 and segsize > 0 then 's' -- segmented
else raise_error(70101, 'unknown ts type='||type
|| ' partitions=' || strip(char(partitions))
|| ' segsize=' || strip(char(segsize))
|| ' db.ts=' || strip(dbName) ||'.'||name) end tsTy
, case
when dssize <> 0 then real(dssize) / 1048576
when type in ('G', 'O', 'P', 'R', 'L') then 4
when partitions = 0 then 64
when partitions > 254 then pgSize
when partitions > 64 then 4
when partitions > 32 then 1
when partitions > 16 then 2
else 4
end dsGB
from sysibm.sysTablespace s
)
, tsPlus as
( select s.*
, case when tsTy = 'G' or partitions = 0
then 0 else 1 end limPart
, case when tsTy = 'G' then dsGB * maxPartitions
when tsTy = 'O' then dsGB * 254
else dsGb end limGB
from tsPlus2 s
)
-- copy catTsPlu end
-- ixPlus: sysIbm.indexes plus missing infos
, ixPlus2 as
(
select i.*
, s.name ts
, s.tsTy
, s.pgSize tsPgSz
, s.partitions tsParts
, dsSize
, dsGb tsDsGb
, case when i.pgSize = 4096 or i.compress = 'Y' then 4
else i.pgSize
end ixPgSz
, (select max(partition)
from sysibm.sysIndexPart p
where p.ixCreator = i.creator
and p.ixName = i.name
) ixParts
from sysibm.sysIndexes i
join sysibm.sysTables t
on i.dbName = t.dbName
and i.tbCreator = t.creator and i.tbName = t.name
join tsPlus s
on i.dbName = s.dbName and t.tsName = s.name
)
, ixPlus3 as
(
select i.*
, case when piecesize <> 0 then real(pieceSize) / 1048576
when ixParts <> 0 then tsDsGb * ixPgSz / tsPgSz
when tsTy in ('s', 'i') then 2
else 4
end pieceGB
from ixPlus2 i
)
, ixPlus as
(
select i.*
, case when ixParts <> 0 then 1
when tsParts = 0 then 32
when tsTy <> 'L' and dsSize = 0 and tsParts <= 64 then 32
when tsParts > 254 then 4096
else smallInt(min(4096, 4096 / pieceGB * ixPgSz))
end * pieceGB limGb
from ixPlus3 i
)
-- copy catIxPlu end
, t0 as
(
select dbname db, name ts, pgSize
, partitions parts, maxPartitions maxParts, dsSize
, case
when type <> ' ' then type
when partitions > 0 and segsize = 0 then 'p' -- classic part
when partitions = 0 and segsize = 0 then 'i' -- simple
when partitions = 0 and segsize > 0 then 's' -- segmented
else raise_error(70101, 'unknown ts type='||type
|| ' partitions=' || strip(char(partitions))
|| ' segsize=' || strip(char(segsize))
|| ' db.ts=' || strip(dbName) ||'.'||name) end tsTy
, case
when dssize <> 0 then int(dssize / 1048576)
when type in ('G', 'O', 'P', 'R', 'L') then 4
when partitions = 0 then 64
when partitions > 254 then pgSize
when partitions > 64 then 4
when partitions > 32 then 1
when partitions > 16 then 2
else 4
end dsGB
, dbid, psid
from sysibm.sysTablespace
)
, ts as
( select t0.*
, case when tsTy = 'G' or parts = 0 then 0 else 9999 end scope
, case when tsTy = 'G' then dsGB * maxparts
when tsTy = 'O' then dsGB * 254
else dsGb end limGB
from t0
)
, tk1 as
(
select ts.*
, value( ( select max(a.key)
from oa1p.tAdm13Schwelle a
where
validBegin <= current date and validEnd > current date
and a.part = 0
and left(a.db, a.dbLen) = left(ts.db, a.dbLen)
and left(a.ts, a.tsLen) = left(ts.ts, a.tsLen)
and (a.tsTy = ' ' or a.tsTy = ts.tsTy)
and a.dsMin <= ts.limGB
), raise_error(70102, 'schwelle null ' || db ||'.'||ts
|| ' ty='||tsTy)
) keyTS
from ts
)
, tr as
(
select tk1.*
, min(partition, scope) part
, nActive nAct
from tk1 join sysibm.sysTableSpaceStats r
on tk1.db = r.dbName and tk1.ts = r.name
and tk1.dbid = r.dbid and tk1.psid = r.psid
)
, tg (db, ts, part, limGb, keyTS, nAct
, pgSize, parts, maxParts, dsSize, tsTy, dsGB) as
(
select db, ts, part, min(limGb), max(keyTS), sum(bigInt(nAct))
, max(pgSize), max(parts), max(maxParts), max(dsSize)
, max(tsTy), max(dsGB)
from tr
group by db, ts, part
)
, i1 as
(
select i.indexType, i.pieceSize, i.creator ixCr, i.name ix
, case when i.pgSize = 4096 or i.compress = 'Y' then 4
else i.pgSize
end ixPgSz
, (select max(partition)
from sysibm.sysIndexPart p
where p.ixCreator = i.creator
and p.ixName = i.name
) ixPaMax
, i.dbName
, tk1.*
from tk1
join sysibm.sysTables t
on tk1.db = t.dbName and tk1.ts = t.tsName
and t.type not in ('A', 'V')
join sysibm.sysIndexes i
on i.tbCreator = t.creator and i.tbName = t.name
)
, i2 as
(
select case when piecesize <> 0 then real(pieceSize) / 1048576
when parts <> 0 then real(dsGB) * ixPgSz / pgSize
else 2
end pcGb
, i1.*
from i1
)
, i3 as
(
select case when ixPaMax <> 0 then 1
when parts = 0 then 32
when tsTy <> 'L' and dsSize = 0 and parts <= 64 then 32
when parts > 254 then 4096
else smallInt(min(4096, 4096 / pcGb * ixPgSz))
end pcMx
, i2.*
from i2
)
select n.ixParts, o.ixPaMax, n.limGb, o.pcGb * o.pcMx
, n.pieceGB, o.pcGB
, n.ts, n.tsTy, n.creator, o.ixCr, n.name, o.ix
from ixPlus n
full outer join i3 o
on n.dbName = o.dbName and n.name = o.ix and n.creator = o.ixCr
where n.dbName is null or o.db is null
or n.ixParts <> o.ixPaMax or n.limGb <> o.pcGb * o.pcMx