zOs/SQL/CHECKRTC
------------------------------------------------------------------
-- checkJob1: höchstens ein Job mit eoj Null
--
select count(*) cnt, count(*) - count(eoj) eojNull, job
from tstRts.tRtsReoRunJob j
group by job
order by 2 desc, 1 desc, job
with ur
fetch first 100 rows only
;
select *
from tstRts.tRtsReoRunJob j
where eoj is null -- and job = 'A540769'
order by job, tst
with ur
;
-- delete from tstRts.tRtsReoRunJob j
-- where tst = '2010-04-01-00.00.00.000000' ;
-- commit;
------------------------------------------------------------------
-- checkJob2: eoj < next tst und eoj=null am schluss
--
with n as
( select (select count(*)
from tstRts.tRtsReoRunJob a
where a.job = j.job and a.tst > j.tst ) nextCnt,
(select min(tst)
from tstRts.tRtsReoRunJob a
where a.job = j.job and a.tst > j.tst ) nextTst,
j.*
from tstRts.tRtsReoRunJob j
)
select case when eoj is null and nextCnt > 0 then 'eoj null not last'
when nextTst <= eoj then 'eoj <= next'
else '' end err,
n.*
from n
order by 1 desc, job, tst
with ur
fetch first 100 rows only
;
-- delete from tstRts.tRtsReoRunJob j
-- where tst in ('2010-06-17-18.15.52.749858'
-- ,'2010-06-24-10.31.48.639515'
-- ,'2010-06-24-10.36.04.847417'
-- ,'2010-07-07-07.44.33.195139'
-- ,'2010-07-07-07.45.28.484218'
-- )
-- ; commit ;
------------------------------------------------------------------
-- checkPart1: reoTst between tst and eoj
--
select p.*, j.*
from tstRts.tRtsReoRunPart p, tstRts.tRtsReoRunJob j
where p.tst = j.tst
and (p.reoTst <= j.tst or p.reoTst >= j.eoj)
order by j.job, j.tst desc
with ur
;
------------------------------------------------------------------
-- checkPart2: check reoTime and ranges
--
select case
when p.paVon <> r.paVon then '97 paVon <>'
when p.paBis <> r.paBis then '96 paBis <>'
when p.part < r.paVon or p.part > r.paBis
then '95 part outside von bis'
when p.reoTime is not null and r.cnt <> r.cntReoTst
then '85 reoTime with reoTst null'
when p.reoTime is not null and p.part <> p.paVon
then '84 reoTime part <> paVon'
when p.reoTime <> r.rngTime
then '83 reoTime <> rngTime'
when p.reoTime is null and r.cnt = r.cntReoTst
and p.part = p.paVon
then '05 reoTime can be set'
else '' end err, p.*, r.*
from tstRts.tRtsReoRunPart p, tstRts.vRtsReoRunRng r
where p.tst = r.tst and p.rng = r.rng
order by 1 desc, p.tst desc
fetch first 100 rows only
;
??? ;
------------------------------------------------------------------
-- checkPart? ??? rnghstens ein Job mit eoj Null
--
; fetch first 100 rows only
select count(*) cnt,
sum(case when reoTst is null then 1 else 0 end) reoTstNull,
sum(case when reoTime is null then 1 else 0 end) reoTimeNull,
j.*
from tstRts.tRtsReoRunPart j
group by job
order by 2 desc, 3 desc, 1 desc
with ur
------------------------------------------------------------------
-- check 5 parts ==> reoTst between tst and eoj
--
select r.reoTime,
(select count(*) from tstRts.tRtsReoRunPart p
where p.tst = r.tst and p.rng = r.rng
and r.reoTst is not null) "partsNN",
(select count(*) from tstRts.tRtsReoRunPart p
where p.tst = r.tst and p.rng = r.rng
and r.reoTst is null) "partsNull",
(select max(reoTst) from tstRts.tRtsReoRunPart p
where p.tst = r.tst and p.rng = r.rng
) "partsReoTstMax",
r.*, j.*
from tstRts.tRtsReoRunJob j, tstRts.tRtsReoRunPart r
where j.tst = r.tst
and r.part = r.paVon
order by job, j.tst, r.rng, r.ty, r.sta
with ur
fetch first 100 rows only
;
------------------------------------------------------------------
-- check 4 parts ==> reoTst between tst and eoj
--
select p.*, j.*
from tstRts.tRtsReoRunJob j, tstRts.tRtsReoRunPart p
where j.tst = p.tst
and ( p.reoTst <= j.tst
or p.reoTst >= j.eoj)
order by job, j.tst, p.rng, p.ty, p.sta
with ur
fetch first 100 rows only
;
------------------------------------------------------------------
-- check 3 parts ==> count statistics pro range and type
--
select job, j.tst, p.rng, p.ty, p.sta,
count(*) cntParts,
sum(case when reoTst is null then 1 else 0 end) reoTstNull,
min(reoTst) reoTstMin,
max(reoTst) reoTstMax
from tstRts.tRtsReoRunJob j, tstRts.tRtsReoRunPart p
where j.tst = p.tst
group by job, j.tst, p.rng, p.ty, p.sta
order by job, j.tst, p.rng, p.ty, p.sta
with ur
fetch first 100 rows only
;;
------------------------------------------------------------------
-- 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 tstRts.tRtsReoRunPart p
, tstRts.vRtsReoTS 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 tstRts.tRtsReoRunPart p
, tstRts.tRtsReoRunJob j
, tstRts.vRtsReoTS 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, tstRts.tRtsReoRunPart 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, tstRts.vRtsReoTs 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, tstRts.vRtsReoIx 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
;
------------------------------------------------------------------
-- analysis 1:
-- show reorg history and current reorgimportance&Reason
-- for all partitions with many reorgs
with d as
(
select count(*) cnt, db, sp, part,
min(p.ty) ty, min(dbid) dbid, min(spid) spid
from tstRts.tRtsReoRunPart p
, tstRts.tRtsReoRunJob j
where p.sta = 'r'
and p.tst = j.tst
group by db, sp, part
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, tstRts.tRtsReoRunPart 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, tstRts.vRtsReoTs 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, tstRts.vRtsReoIx 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 tstRts.tRtsReoRunPart p
, tstRts.tRtsReoRunJob 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 tstRts.tRtsReoRunPart
group by ty, sta
---------------------------------------------------------
-- fix: überlüssige parts löschen
update
tstRts.tRtsReoRunPart p
set rngI0 = -9876
where sta = '0'
and not exists (select 1 FROM tstRts.tRtsReoRunTSStats t
where t.tst = p.tst and t.rng = p.rng)
and not exists (select 1 FROM tstRts.tRtsReoRunIxStats i
where i.tst = p.tst and i.rng = p.rng)
;
delete from tstRts.tRtsReoRunPart p
where rngI0 = -9876
;
with c as
( select
(select count(*) FROM tstRts.tRtsReoRunTSStats t
where t.tst = p.tst and t.rng = p.rng) cntTs,
(select count(*) FROM tstRts.tRtsReoRunIxStats i
where i.tst = p.tst and i.rng = p.rng) cntIx,
p.*
from tstRts.tRtsReoRunPart p
)
select * from c
where cntTs = 0 and cntIx = 0
;
commit;
;; where not exists (
select 1 from tstRts.tRts
;
select ty, count(*)
from tstRts.tRtsReoRunPart
group by ty
;;
select *
from tstRts.tRtsReoRunPart
where not exists0-07-08-00.07.35.261954'
;