zOs/SQL/REORUANA
select count(*), ty, sta, min(reason)
from s100447.tReoRunPart p
where p.db = 'WB12A1T' and sp ='A500A'
and tst > current timestamp - 1 year
group by substr(reason, 1, 10), ty, sta
order by substr(reason, 1, 10), ty ,sta
;;
fro
select tst, reason, p.*
where p.db = 'WB12A1T' and sp ='A500A' and part=2
order by tst desc
;
;;;
select (select count(*)
from s100447.tReoRunJob j
where j.job = d.fJob and j.ty = d.jTy
------------------------------------------------------------------
-- analysis 1:
-- show reorg history and current reorgimportance&Reason
-- for all partitions with many reorgs
with d as
(
select sum(case when p.sta = 'r' then 1 else 0 end) cReo,
sum(case when p.sta = 's' then 1 else 0 end) cSpa,
db, sp, part,
count(distinct job) cJob, min(job) fJob, max(job) tJob,
min(j.ty) jTy,
min(p.ty) ty, min(dbid) dbid, min(spid) spid
from s100447.tReoRunPart p
join s100447.tReoRunJob j
on p.tst = j.tst
where p.sta in ('r', 's') and p.tst > current timestamp - 3 month
and reason > '0' and not left(reason, 1) = '-'
and job like 'QR%'
group by db, sp, part
-- having count(*) > 1 and min(job) not like 'A%'
)
select *
from d
order by 2 desc , 1 desc
;;;
CREO CSPA DB SP PART C
---+---------+---------+---------+---------+---------+--
18 0 WB12A1T A500A 2
16 0 NZ01A1T A001A 0
15 0 CZ11A1T A906A 4
15 0 CZ11A1T A906A 3
14 0 CZ11A1T A905A 0
14 0 CY02A1T ICY053A3 0
14 0 CY02A1T ICY053A2 0
14 0 CY02A1T ICY053A1 0
14 0 CY02A1T ICY053A0 0
14 0 CY02A1T A055A 0
14 0 WB12A1T A500A 1
13 0 VV29A1T VDPS438 0
13 0 UU02A1T A610A 0
13 0 UU02A1T A260A 0
13 0 UU02A1T A250A 0
13 0 UU02A1T A220A 0
13 0 UU02A1T A200A 0
13 0 UU02A1T A170A 0
13 0 UU02A1T A131A 0
13 0 UU02A1T A100A 0
13 0 XR01A1T IXR101A3 0
13 0 XR01A1T IXR101A1 0
13 0 XR01A1T IXR101A0 0
13 0 XC02A1T AXCQ011 0
13 0 AV04A1T A413A03 0
13 0 AV04A1T A402A03 0
13 0 AV04A1T A401A03 0
13 0 AV02A1T A207A 0
13 0 AV02A1T A206A 0
13 0 AV01A1T IAV063A1 0
13 0 AV01A1T A009A 0
select cReo, cSpa, d.db, d.sp, d.part, p.reason, p.tst
from d
left join s100447.tReoRunPart p
on p.db = d.db || '' and p.sp = d.sp || '' and p.part = d.part
and p.tst > current timestamp - 3 month
order by 2 desc , 1 desc, p.tst desc
;;;
select (select count(*)
from s100447.tReoRunJob j
where j.job = d.fJob and j.ty = d.jTy
and j.tst > current timestamp - 3 month ) totJobs,
d.*
from d
order by 3 desc ,2 desc
fetch first 100 rows only
;;;
, h (cnt, db, sp, part, cla, reason, tst) as
(
select d.cnt, d.db, d.sp, d.part, d.ty, p.reason, p.tst
from d, s100447.tReoRunPart p
where p.sta = 'r'
and d.db = p.db and d.sp = p.sp and d.part = p.part
union all select d.cnt, d.db, d.sp, d.part,
'T' || strip(char(importance)), r.reason, r.updateStatsTime
from d, s100447.vReoTs r
where d.ty = 't'
and d.db = r.db and d.sp = r.ts and d.part = r.part
and d.dbid = r.dbid and d.spid = r.psid and d.part = r.part
union all select d.cnt, d.db, d.sp, d.part,
'I' || strip(char(importance)), r.reason, r.updateStatsTime
from d, s100447.vReoIx r
where d.ty = 'i'
and d.db = r.db and d.sp = r.is and d.part = r.part
and d.dbid = r.dbid and d.spid = r.isoBid and d.part = r.part
)
select * from h
-- where db like 'A%'
order by 1 desc, 2, 3, 4, 7 desc
;x;
select count(*), db, sp, part, min(p.tst), max(p.tst),
min(job), max(job)
from s100447.tReoRunPart p
, s100447.tReoRunJob j
where p.sta = 'r'
and p.tst = j.tst
group by db, sp, part
order by 1 desc, 2, 3, 4
;
x;
select count(*), ty, sta
from s100447.tReoRunPart
group by ty, sta
---------------------------------------------------------
-- fix: überlüssige parts löschen
update
s100447.tReoRunPart p
set rngI0 = -9876
where sta = '0'
and not exists (select 1 FROM s100447.tReoRunTSStats t
where t.tst = p.tst and t.rng = p.rng)
and not exists (select 1 FROM s100447.tReoRunIxStats i
where i.tst = p.tst and i.rng = p.rng)
;
delete from s100447.tReoRunPart p
where rngI0 = -9876
;
with c as
( select
(select count(*) FROM s100447.tReoRunTSStats t
where t.tst = p.tst and t.rng = p.rng) cntTs,
(select count(*) FROM s100447.tReoRunIxStats i
where i.tst = p.tst and i.rng = p.rng) cntIx,
p.*
from s100447.tReoRunPart p
)
select * from c
where cntTs = 0 and cntIx = 0
;
commit;
;; where not exists (
select 1 from s100447.tRts
;
select ty, count(*)
from s100447.tReoRunPart
group by ty
;;
select *
from s100447.tReoRunPart
where not exists0-07-08-00.07.35.261954'
;
------------------------------------------------------------------
-- analysis 2 TS:
-- show reorg history and current reorgImportance&Reason
-- for all partitions wich should be reorganized NOW
select r.db, r.ts, r.part,
substr(strip(char(r.importance)) || ' ' || r.reason, 1, 30),
substr(p.reason, 1, 30) "pReason", p.tst pTst, p.reoTst,
r.lastBuilt
from s100447.tReoRunPart p
, s100447.vReoTS r
where p.sta = 'r'
and p.db = r.db and p.sp = r.ts and p.part = r.part
and r.importance > 0
and r.lastBuilt > p.tst
order by importance, r.reason, db, ts, part
with ur
;?;
------------------------------------------------------------------
-- analysis 3 TS:
-- show mismatch between reorgTst and realtime stats
-- ???????????titions wich should be reorganized NOW
select r.db, r.ts, r.part,
substr(strip(char(r.importance)) || ' ' || r.reason, 1, 30),
substr(p.reason, 1, 30) "pReason", p.tst pTst, p.reoTst,
r.lastBuilt
from s100447.tReoRunPart p
, s100447.tReoRunJob j
, s100447.vReoTS r
where p.sta = 'r'
and p.db = r.db and p.sp = r.ts and p.part = r.part
and p.tst = j.tst
and ((r.lastBuilt > p.tst and r.lastBuilt < j.eoj
and (p.reoTst is null or p.reoTime is null)
or ((r.lastBuilt < p.tst or r.lastBuilt > j.eoj)
and (p.reoTst is not null or p.reoTime is not null))))
order by importance, r.reason, db, ts, part
with ur
;?;
having count(*) > 1 and min(job) not like 'A%'
)
, h (cnt, db, sp, part, cla, reason, tst) as
(
select d.cnt, d.db, d.sp, d.part, d.ty, p.reason, p.tst
from d, s100447.tReoRunPart p
where p.sta = 'r'
and d.db = p.db and d.sp = p.sp and d.part = p.part
union all select d.cnt, d.db, d.sp, d.part,
'T' || strip(char(importance)), r.reason, r.updateStatsTime
from d, s100447.vReoTs r
where d.ty = 't'
and d.db = r.db and d.sp = r.ts and d.part = r.part
and d.dbid = r.dbid and d.spid = r.psid and d.part = r.part
union all select d.cnt, d.db, d.sp, d.part,
'I' || strip(char(importance)), r.reason, r.updateStatsTime
from d, s100447.vReoIx r
where d.ty = 'i'
and d.db = r.db and d.sp = r.is and d.part = r.part
and d.dbid = r.dbid and d.spid = r.isoBid and d.part = r.part
)
select * from h
-- where db like 'A%'
order by 1 desc, 2, 3, 4, 7 desc
;