zOs/SQL/GBGRSEL
set current path oa1p;
select substr(fqzfmtBin7(1024.0 * pgSize * nPages), 1, 7) used
, substr(fqzfmtBin7(1024.0 * pgSize * nActive), 1, 7) spc
, dbName, name, partition
, a.*
from OA1P.TQZ006GBGRTsSTATS a
where rz = 'RZ2' and dbSys = 'DVBP'
-- and name = 'SFJ30011'
-- and dbname = 'XBFJ3001'
-- and partition = 24
and validBegin <= current timestamp
and validEnd > current timestamp
order by real(pgSize) * max(value(abs(nActive), 0)
, value(abs(nPages), 0)) desc
-- order by validBegin desc
-- fetch first 100 rows only
with ur
;x;
where dbname = 'ND01A1P' and tsName = 'IND003A1'
and dsNum in (0,4)
;x;
select rz, dbSys, dbName, indexSpace, creator, name, partition
, min(validBegin) validBegin, min(updatestatsTime) updateStats
, LOADRLASTTIME, REBUILDLASTTIME, REORGLASTTIME
from OA1P.TQZ007GBGRIxSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and name = 'VTXINSTRFLATDATA1'
and dbname = 'VV21A1P'
-- and real(pgSize) * nActive > 20000000
-- and partition = 41
-- and validBegin >= current timestamp - 1000 days
-- and validEnd > current timestamp
group by rz, dbSys, dbName, indexSpace, creator, name, partition
, LOADRLASTTIME, REBUILDLASTTIME, REORGLASTTIME
order by 7, 8 desc
-- fetch first 5 rows only
; xx
set current path oa1p;
select -- rz, dbSys, dbName, count(*) parts--, sum(real(nActive)) pages
partition, updateStatsTime
, substr(fqzfmtBin7(1024.0 * pgSize * nPages), 1, 7) used
, substr(fqzfmtBin7(1024.0 * pgSize * nActive), 1, 7) spc
, substr(fqzfmtE7(reorgDeletes), 1, 7) deletes
, reorgLastTime
, substr(fqzfmtE7(totalRows), 1, 7) rows
, 1024.0 * pgSize * nPages / max(1, totalRows) rowBy
, a.*
from OA1P.TQZ006GBGRTsSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
-- and name = 'VTXINSTRFLATDATA1'
and dbname = 'VV21A1P'
-- and real(pgSize) * nActive > 20000000
-- and partition = 41
-- and validBegin >= current timestamp - 1000 days
-- and validEnd > current timestamp
-- group by rz, dbSys, rollup(dbName)
order by rz,dbSYs, dbName, name, partition, validBegin desc
fetch first 5 rows only
; xx
select * from plan_view1
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by collid, progName, explain_time,
queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
with ur
;
rollback
;;;;
order by dbName, name, partition, validBegin desc ;x;
and current timestamp between validBegin and validEnd
group by rz, dbSys
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
set current application compatibility 'V11R1';
select *
from OA1P.TQZ007GBGRIXSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName = 'FI04A1P' and ts = 'A060A'
order by dbName, ts, name, partition, validBegin desc ;x;
select *
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName = 'FI04A1P' and name = 'A060A'
order by dbName, name, partition, validBegin desc ;x;
and current timestamp between validBegin and validEnd
group by rz, dbSys
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table ;
delete from A540769.dsn_predicat_table;
explain plan set queryno = 3 for
with s as
(
select sum(real(nActive) * pgSize * 1024) actB
, count(*) parts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name) ts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ) db
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
and current timestamp between validBegin and validEnd
group by rz, dbSys
)
select * from s
;
explain plan set queryno = 7 for
with d (d, l) as
(
select current timestamp, 0 from sysibm.sysDummy1
union all select d- 7 days, l+1 from d where l < 1
)
, s as
(
select sum(real(nActive) * pgSize * 1024) actB
, count(*) parts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name) ts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ) db
from OA1P.TQZ006GBGRTSSTATS, d
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
and d >= validBegin
and d < validEnd
group by d, rz, dbSys
)
select * from s
;
explain plan set queryno = 9 for
with d (d, l) as
(
select current timestamp, 0 from sysibm.sysDummy1
union all select d- 7 days, l+1 from d where l < 1
)
, s as
(
select sum(real(nActive) * pgSize * 1024) actB
, count(*) parts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name) ts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ) db
from OA1P.TQZ006GBGRTSSTATS a, d
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
and d + 0 seconds between validBegin and validEnd
group by d, rz, dbSys
)
select * from s
;
explain plan set queryno = 13 for
with d (d, l) as
(
select current timestamp, 0 from sysibm.sysDummy1
union all select d- 7 days, l+1 from d where l < 1
)
, s as
(
select 0 l
, sum(real(nActive) * pgSize * 1024) actB
, count(*) parts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name) ts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ) db
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
and validBegin <= (select d from d where l = 0)
and validEnd > (select d from d where l = 0)
group by rz, dbSys
)
select * from s
;
explain plan set queryno = 22 for
with s ( l,d, actB, parts, tss, dbs) as
(
select 0, current timestamp
, sum(real(nActive) * pgSize * 1024)
, count(*)
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name)
, count(distinct rz ||'/'|| dbSys ||':'|| dbName )
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
-- and dbName like 'MF%'
and validBegin <= current timestamp
and validEnd > current timestamp
group by rz, dbSys
union all select 1, current timestamp - 7 days
, sum(real(nActive) * pgSize * 1024)
, count(*)
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name)
, count(distinct rz ||'/'|| dbSys ||':'|| dbName )
from OA1P.TQZ006GBGRTSSTATS
where rz = 'RZ2' and dbSys = 'DBOF'
-- and dbName like 'MF%'
and validBegin <= current timestamp - 7 days
and validEnd > current timestamp - 7 days
group by rz, dbSys
)
select * from s
;
select * from plan_view1
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, explain_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by collid, progName, explain_time,
queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
with ur
;
select * from dsn_predicat_table
order by collid, progName, explain_time,
queryNo, qBlockNo, predNo -- , orderNo, mixOpSeqNo
with ur
;
rollback
;;;;
with s as
(
select sum(real(nActive) * pgSize * 1024) actB
, count(*) parts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ||'.'|| name) ts
, count(distinct rz ||'/'|| dbSys ||':'|| dbName ) db
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
and current timestamp between validBegin and validEnd
group by rz, dbSys
)
select * from s
;
select lastDataChange
, oa1p.fQzFmtBin2(actB) act
, dbName, name, partition
, s.*
from s
where actB > 12e9
order by value(lastDataChange, '2015-04-15-00.00.00') desc
, dbName, name, partition
fetch first 1000 rows only
with ur
;x;
with s as
(
select rz, dbSys, date(validBegin) val, date(updateSTatsTime) updStats
from OA1P.TQZ007GBGRIXSTATS a
where rz = 'RR2' and validBegin > current timestamp - 35 days
)
select rz, dbSys, val, updStats, count(*)
from s
group by rz, dbSys, val, updStats
order by 1,2,3 desc, 4 desc
;x;
select rz, dbSys, loadTs, count(*)
, min(updateStatsTime) updateStats, max(updateStatsTime)
, min(validBegin) validBegin, max(validBegin)
, min(validEnd) validEnd, max(validEnd)
, min(dbName || '.' || name || '#' || partition)
from OA1P.TQZ007GBGRIXSTATS a
where rz = 'RZ2' and validBegin > current timestamp - 15 days
group by rz, dbSys, loadTs
order by 1,2,3 desc
;x;
select count(*), state
from OA1P.TQZ006GBGRTSHJJJ
group by state
;x
select rz, dbSys, loadTs, count(*)
, min(dbName || '.' || name || '#' || partition)
from OA1P.TQZ007GBGRIXNew a
group by rz, dbSys, loadTs
order by 1,2,3,4 desc
;x;
select rz, dbSys, dbName, name, partition,instance
, validBegin, validEnd, updateStatsTime, loadTs, o.*
from OA1P.TQZ006GBGRTSNew o
where rz = 'RR2' and dbSys = 'DP2G' -- and dbName = 'MF01A1P'
and validBegin > '2016-01-07-00.00.00'
-- and name = 'A311A'
order by 7 desc
-- order by 1, 2, 3, 4, 5, 6, 7 desc
;x;
select rz, dbSys, dbName, name, partition,instance
, validBegin, validEnd, updateStatsTime, loadTs, n.*
from OA1P.TQZ006GBGRTSNew n
where rz = 'RZZ' and dbSys = 'DE0G' and dbName = 'MF01A1P'
and name = 'A311A'
order by 1, 2, 3, 4, 5, 6, 7 desc
;x;
select rz, dbSys, dbName, name, partition,instance
, max(validBegin)
from OA1P.TQZ006GBGRTSNew n
group by rz, dbSys, dbName, name, partition,instance
order by 1, 2, 3, 4, 5, 6, 7 desc
;x;
set current path oa1p;
select rz, dbSys, state, loadTs, count(*)
, min(dbName || '.' || name || '#' || partition)
from OA1P.TQZ007GBGRIXSTATS a
where rz = 'RZ2' and updatestatsTime > current timestamp - 3 days
group by rz, dbSys, state, loadTs
order by 1,2,3,4 desc
;x;
update
sysibm.sysTableSpaceStats
set npages = 600000
, totalRows = 6000000
where dbName = 'QZ01A1P' and name = 'A007A' and partition = 2
;
select *
from sysibm.sysTableSpaceStats
where dbName = 'QZ01A1P' and name = 'A006A' and partition = 2
;
commit
;x;
select count(*)
from OA1P.TQZ007GBGRIXSTATS a
where rz = '?'
;x;
select rz, dbSys, state, loadTs, count(*)
, min(dbName || '.' || name || '#' || partition)
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZZ' and updatestatsTime > current timestamp - 2 days
group by rz, dbSys, state, loadTs
order by 1,2,3,4 desc
;x;
select dbName, name, partition
, fosFmte7(real(real(nActive) * pgSize / 1048576)) actGB
, updatestatstime, totalRows, nActive
, a.*
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DVBP'
and dbName = 'XBDG6002'
and name = 'SDG60063'
-- and partition = 7
order by partition, updatestatstime desc
;x;
select count(*), rz, dbSys, max(updateStatsTime) upd, max(loadTs)
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RR2' and loadTs > current timestamp - 20 days
and updateStatsTIme > current timestamp - 20 days
group by rz, dbSys, loadTs
order by 2, 3, 4 desc
;x;
select *
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2' and dbSys = 'DVBP' and dbName = 'XBFJ3002'
and name = 'SFJ30022' and partition in (11, 14, 19)
;x;
with t2 as
(
select rz, dbSys, dbName, name
, partition, instance
, max(loadTs) loadTs
, max(case when loadTs > '2015-05-18-00.00.00'
then null else loadTs end) loadBef
from OA1P.TQZ006GBGRTSSTATS a
where rz = 'RZ2'
and dbName = 'XC01A1P' and name = 'A501A'
and loadTs < '2015-05-20-00.00.00'
group by rz, dbSys, dbName, name, partition, instance
)
select a.*
from t2 join OA1P.TQZ006GBGRTSSTATS a
on a.rz = t2.rz and a.dbSys = t2.dbSys
and a.dbName = t2.dbName and a.name = t2.name
and a.instance = t2.instance and a.partition = t2.partition
and a.loadTs in ( t2.loadTs, t2.loadBef)
order by t2.rz, t2.dbSys, t2.dbName, t2.name
, t2.instance, t2.partition, a.loadTs desc
;x;
, t as
(
select t.rz, t.dbSys, t.dbName, t.Name, min(t.tsTy) tsTy
, smallInt(t.partition * t.limPart) partition
, t.instance
, min(t.limGb) limGb
, min(t.parts) parts
, min(t.clone) clone
, min(t.tsInst) tsInst
, real(sum(real(t.nActive) * t.pgSize / 1048576)) actGB
from OA1P.TQZ006GBGRTSSTATS t join t2 a
select *
from oa1p.tqz006gbgrTsStats
where rz = 'RZ2'
and dbName = 'XC01A1P' and name = 'A501A'
and loadTs >= '2015-05-01-00.00.00'
order by loadTs desc
;x;
select count(*) c, rz, dbSys, loadTs
from oa1p.tqz006gbgrTsStats
where rz = 'RZ2'
and loadTs >= '2015-02-22-00.00.00'
group by rz, dbSys, loadTs
order by rz, dbSys, loadTs
;x;
select date(loadTs), max(updateStatsTime)
from oa1p.tqz006gbgrtsStats
where rz = 'RR2' and dbSys = 'DBOF'
-- and dbName = 'WB11A1P'
-- and name = 'A704A'
-- and partition = 343
-- and loadTs >= '2014-12-01-00.00.00'
group by date(loadTs)
order by 1 desc
;x;
order by rz, dbSys, dbName, name, partition, updatestatstime desc
;x;
with d as
(
select count(*) c, rz, dbSys, dbName
from oa1p.tqz007gbgrixStats
where rz = 'RZZ' and loadTs >= '2014-07-16-00.00.00'
group by rz, dbSys, dbName
)
select sum(c) over(partition by rz, dbSys
order by dbName
)
, d.*
from d
order by rz, dbSys, dbName
;x;
select count(*), rz, dbSys, loadTs
from oa1p.tqz006gbgrtsStats
where rz <> '?'
group by rz, dbSys, loadTs
order by rz, dbSys, loadTs desc
;x;
with t2 as
(
select rz, dbSys, dbName, name
, partition, instance, max(loadTs) loadTs
from OA1P.TQZ006GBGRTSSTATS a
group by rz, dbSys, dbName, name, partition, instance
)
, t as
(
select t.rz, t.dbSys, t.dbName, t.Name, min(t.tsTy) tsTy
, smallInt(t.partition * t.limPart) partition
, t.instance
, min(t.limGb) limGb
, min(t.parts) parts
, min(t.clone) clone
, min(t.tsInst) tsInst
, real(sum(real(t.nActive) * t.pgSize / 1048576)) actGB
from OA1P.TQZ006GBGRTSSTATS t join t2 a
on t.rz = a.rz
and t.dbSys = a.dbSys
and t.dbName = a.dbName
and t.Name = a.Name
and t.partition = a.partition
and t.instance = a.instance
and t.loadTS = a.loadTs
group by t.rz, t.dbSys, t.dbName, t.Name
, smallInt(t.partition * t.limPart)
, t.instance
)
, i2 as
(
select rz, dbSys, dbName, ts, indexSpace
, partition, instance, max(loadTs) loadTs
from OA1P.TQZ007GbGrIxSTATS a
group by rz, dbSys, dbName, ts, indexSpace, partition, instance
)
, i as
(
select i.*
, real(real(nActive) * ixPgSz / 1048576) actGB
from OA1P.TQZ007GBGRIxSTATS i join i2 a
on i.rz = a.rz
and i.dbSys = a.dbSys
and i.dbName = a.dbName
and i.ts = a.ts
and i.indexSpace= a.indexSpace
and i.partition = a.partition
and i.instance = a.instance
and i.loadTS = a.loadTs
)
, u (limGb, actGb, db, ts, ix, part, inst, rz, dbSys
, tsTy, tsLimGb, tsParts, tsClone, tsInst) as
(
select limGb, actGb, dbName, name, ' --ts--'
, partition, instance, rz, dbSys
, tsTy, limGb, parts, clone , tsInst
from t
union all select limGb, actGb, dbName, ts , name
, partition, instance, rz, dbSys
, tsTy, tslimGb, tsParts, tsClone, tsInst
from i
)
, s as
(
select u.*
, (select max(info) from oa1p.tqz008GbGrSchweExp e
where e.rz = u.rz and e.dbSys = u.dbSys
and left(e.db , e.dbLen) = left(u.db , e.dbLen)
and left(e.ts , e.tsLen) = left(u.ts , e.tsLen)
and e.part in(u.part, 0)
and e.tsTy in (u.tsTy, ' ')
and e.dsMin <= u.tslimGB
and validBegin <= current date
and validEnd > current date
) schwInfo
from u
)
, v as
(
select int(case when schwInfo is not null
then int(substr(schwInfo, 16, 6))
else raise_error(70001, 'schwelle null ts='
|| db || '.' || ts || '#' || part)
end) schwelle
, s.*
from s
-- order by db, ts, part, ix
)
select substr(db, 1, 8) "db"
, substr(ts, 1, 8) "ts"
, substr(ix, max(1, length(ix) - 7), 8) "...index"
, substr(case when part = 0 and tsParts = 0 then ''
else case when part = 0 then ' npi'
else value(right(' ' || part, 4), '----') end
||'/'|| value(right(' '||strip(char(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"
, tsTy "y"
, substr(schwinfo, 23) "schwellwert key"
from v
where -- actGb > real(limGb / 100 * schwelle)
db like 'MF01%'
and db <> 'DSNDB01' -- directory ist anders
and rz = 'RZ2' and dbSys = 'DBOF'
order by db, ts, ix, part, ix
;x;
select *
from OA1P.TQZ007GBGRixSTATS a
where rz = 'RZ2' and dbSys = 'DBOF' and dbName = 'MF01A1P'
and name like 'IMF150A%'
;x;
select count(*), date(loadTs)
from oa1p.tqz006gbgrtsStats
-- where rz = 'RZ2'
group by date(loadTs)
order by date(loadTs) desc
;x;
insert into oa1p.tqz006GbGrTsStats
(state, rz, dbSys, dbName, name, partition, instance
, pgSize, tsType, nTables, parts, maxParts, dsSize
, segSize, tsTy, dsGB, clone, tsInst, tbCr, tb, tbTy, tbId
, dbid, obid, psid, ibmReqD
, updateStatsTime)
with s as
(
select row_number()
over (partition by dbName, name, partition, instance
order by loadDt desc, updateStatsTime desc) rn
, s.*
from oa1p.tqz006GbGrTsStats s
where rz = 'RZ4' and dbSys = 'DP4G' and state <> 'd'
fetch first 100 rows only
)
select 'd', rz, dbSys, dbName, name, partition, instance
, pgSize, tsType, nTables, parts, maxParts, dsSize
, segSize, tsTy, dsGB, clone, tsInst, tbCr, tb, tbTy, tbId
, dbid, obid, psid, ibmReqD
, (select max(updateStatsTime)
from oa1p.tqz006GbGrTsStats n
where n.rz = '?' and n.dbSys = '?'
)
from s
where rn = 1
and not exists (select 1
from oa1p.tqz006GbGrTsStats n
where n.rz = '?' and n.dbSys = '?'
and n.dbName = s.dbName
and n.Name = s.Name
and n.partition = s.partition
and n.instance = s.instance
)
;
commit
;x;
with s as (
select
s.pgSize, s.type tsType, s.nTables
, s.partitions parts, s.maxPartitions maxParts, s.dsSize
, s.segsize
, char(value(case
when s.type <> ' ' then s.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='||s.type
|| ' partitions=' || strip(char(partitions))
|| ' segsize=' || strip(char(segsize))
|| ' db.ts=' || strip(s.dbName) ||'.'||s.name)
end, '?'), 1) tsTy
, smallInt(value(case
when dssize <> 0 then int(dssize / 1048576)
when s.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, -99)) dsGB
, s.obid, s.clone, s.instance tsInst
, value(t.creator, '') tbCr
, value(t.name, case when nTables = 0 then 'none'
when nTables > 1 then 'multi'
else 'missing' end) tb
, value(t.type, '') tbTy
, smallint(value(t.obId, 0)) tbId
, r.*
from sysibm.sysTableSpaceStats r
join sysibm.sysTablespace s
on r.dbName = s.dbName and r.name = s.name
and r.dbId = s.dbId and r.psid = s.psid
left join sysibm.sysTables t
on s.nTables = 1
and t.dbName = s.dbName and t.tsName = s.name
and ( (t.type not in ('A', 'V', 'C', 'G')
and s.instance = r.instance)
or (t.type = 'C' and s.instance <> r.instance))
)
select dbName, name, partition, instance, count(*)
from s
group by dbName, name, partition, instance
order by 5 desc
;x;
select rz, dbSys, count(*)
from oa1p.tQz006GbGrTsMeta
group by rz, dbSys
;x;
select *
from S100447.tGbGrSchwelle
;
select *
from S100447.tGbGrSchwHist