zOs/SQL/CHECKRTA

------------------------------------------------------------------
--  analysis 1:
--     show reorg history and current reorgimportance&Reason
--     for TS partitions with many reorgs
with u as
( select db, sp ts, part, p.tst, reoTst, reason,
       reorgLastTime, totalRows,
       reorgInserts, reorgUpdates, reorgDeletes
    from tstRts.tRtsReoRunPart p, tstRts.tRtsReoRunTsStats s
    where p.ty = 't' and p.reoTst is not null
        and p.tst = s.tst and p.rng = s.rng and p.part = s.partition
        and p.tst > current timestamp - 1 month
  union all select db, ts, part, updateStatsTime tst,
       cast('9999-12-31-23.59.59' as timestamp) reoTst,
       strip(char(importance)) || ' ' || reason reason,
       reorgLastTime, totalRows,
       reorgInserts, reorgUpdates, reorgDeletes
    from tstRts.vRtsReoTS
            where importance > 0
), d as
(
select count(*) cnt, db, ts, part
    from u
    group by db, ts, part
 -- having count(*) > 1
    having max(reoTst) = '9999-12-31-23.59.59'
        and max(case when reoTst <= current timestamp
                         then reotst
                         else '1000-12-31-23.59.59'
                end) > current timestamp - 40 hours
)
select d.cnt, u.*
    from u, d
    where u.db = d.db and u.ts = d.ts and u.part = d.part
    order by cnt desc, db, ts, part, tst desc
;
;x;
------------------------------------------------------------------
--  analysis 1 old:
--     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.reoTst is not null
        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;
------------------------------------------------------------------
--  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
;
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