zOs/SQL/GBGRCLEB
-- gbGr cleanup
-- check / delete ungültige drops (state = 'd')
-- (1) select count
-- (2) delete
-- switch zwischen (1) und (2) durch anpassen der mit --??
-- markierten stellen
--
with d as
(
select rz, dbSys, dbName, name, partition, instance, updatestatstime
, state, loadTs
, ( select char(n.loadTS) || char(n.updateStatsTime) || n.state
from OA1P.TQZ006GBGRTSSTATS n
where d.rz = n.rz and d.dbSys = n.dbSys
and d.dbName = n.dbName and d.name = n.name
and d.partition = n.partition
and d.instance = n.instance
and d.loadTS < n.loadTS
order by n.loadTs
fetch first 1 rows only
) nx
from OA1P.TQZ006GBGRTSSTATS d
where state <> 'a'
and rz = 'RZ2' -- and dbSys = 'DBOF'
)
, n as
(
select d.*
, timestamp(substr(nx, 1, 26)) nxLoad
, timestamp(substr(nx, 27, 26)) nxUpd
, substr(nx, 53 ) nxState
from d
)
, o2 as
(
select n.*
, ( select char(o.loadTS) || char(o.updateStatsTime) || o.state
from OA1P.TQZ006GBGRTSSTATS o
where n.rz = o.rz and n.dbSys = o.dbSys
and n.dbName = o.dbName and n.name = o.name
and n.partition = o.partition
and n.instance = o.instance
and n.nxLoad < o.loadTs
order by o.loadTs
fetch first 1 rows only
) ov
from n
)
, o as
(
select o2.*
, timestamp(substr(ov, 1, 26)) ovLoad
, timestamp(substr(ov, 27, 26)) ovUpd
, substr(ov, 53 ) ovState
from o2
)
, p as
(
select o.*
, case when days(ovLoad) - days(loadTs) < 8 then 'o<8 '
when days(ovLoad) - days(loadTs) < 15 then 'o<15 '
else '' end
||case when days(nxLoad) - days(loadTs) <= 7 then 'n<8 '
when days(nxLoad) - days(loadTs) <= 15 then 'n<15 '
else '' end
||case when nxUpd < loadTs then 'nx<Lo' else '' end dno
, case when ovState = 'a' and nxUpd < loadTs then 'dn'
when ovState = 'a' and nxLoad < loadTs + 8 days then 'd'
when ovState = 'd' and ( nxUpd < loadTs
or nxLoad < loadTs + 8 days) then 'no'
when ovState is not null then ''
when nxState = 'd' then 'n'
when nxState = 'a' and nxUpd < loadTs then 'dn'
when nxState = 'a' and nxLoad < loadTs + 8 days then 'd'
else '' end deldno
from o
)
select state, nxState, ovState, dno, delDno, count(*)
from p
group by state, nxState, ovState, dno, delDno
;x;
select count(*), dd
from d
group by dd
order by value(dd, -99)
;
;x;
with s as
(
select rz, dbSys, dbName, name, partition, instance, updatestatstime
, count(*) cnt
from OA1P.TQZ006GBGRTSSTATS
where rz = 'RZ2' and dbSys = 'DBOF'
-- and dbName in ('XC01A1P', 'XR01A1P')
-- and (name like 'A2%' or name like 'A5%')
group by rz, dbSys, dbName, name, partition, instance, updatestatstime
having count(*) > 1
)
, l as
(
select s.*
, row_number() over(partition by s.rz, s.dbSys, s.dbName
, s.name, s.partition, s.instance
, s.updateSTatsTime
order by a.loadTs
) rn
, a.loadTs
from s
join OA1P.TQZ006GBGRTSSTATS a
on a.rz = s.rz and a.dbSys = s.dbSys
and a.dbName = s.dbName and a.name = s.name
and a.partition = s.partition
and a.instance = s.instance
and a.updateStatsTime = s.updateStatsTime
)
select loadTs, count(*)
from l
where rn > 1
group by loadTs
order by 2 desc
;x;
select *
from l
order by rz, dbSys, dbName, name, partition, instance
, updatestatstime, rn
;x;
, n as
(
select nN.*
, ( select max(nD.updatestatsTime)
from OA1P.TQZ006GBGRTSSTATS nD
where nS.rz = nD.rz and nS.dbSys = nD.dbSys
and nS.dbName = nD.dbName and nS.name = nD.name
and nS.partition = nD.partition
and nS.instance = nD.instance and nD.state = 'a'
and not
( nN.totalRows = nD.totalRows
and nN.nActive = nD.nActive
and nN.nPages = nD.nPages
and nN.reorgInserts = nD.reorgInserts
and nN.REORGDELETES = nD.REORGDELETES
and nN.REORGUPDATES = nD.REORGUPDATES
and nN.REORGMASSDELETE = nD.REORGMASSDELETE
and nN.dataSize = nD.dataSize
) ) difNew
from s nS
join OA1P.TQZ006GBGRTSSTATS nN
on nS.rz = nN.rz and nS.dbSys = nN.dbSys
and nS.dbName = nN.dbName and nS.name = nN.name
and nS.partition = nN.partition and nS.instance = nN.instance
and nS.updatestatstime = nN.updateStatsTime
)
, e as
(
select
( select min(updatestatsTime)
from OA1P.TQZ006GBGRTSSTATS a
where a.rz = d.rz and a.dbSys = d.dbSys and a.state = 'a'
and a.dbName = d.dbName and a.name = d.name
and a.partition = d.partition
and a.instance = d.instance
and a.updateStatsTime
> value(d.difNew, d.updateStatsTime - 1e-6 seconds)
) eqOld
, d.*
from n d
)
select *
from n
where name = 'A517A'
order by 1 desc , dbName, name, partition
;x;
--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
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 rz, dbSys, dbName, name, partition, instance
, max(updatestatstime) updatestatstime
from OA1P.TQZ006GBGRTSSTATS s1
where rz = 'RZ2' and dbSys = 'DBOF' and state = 'a'
and dbName in ('XC01A1P', 'XR01A1P')
and (name like 'A2%' or name like 'A5%')
group by rz, dbSys, dbName, name, partition, instance
)
, n as
(
select nN.*
, ( select max(nD.updatestatsTime)
from OA1P.TQZ006GBGRTSSTATS nD
where nS.rz = nD.rz and nS.dbSys = nD.dbSys
and nS.dbName = nD.dbName and nS.name = nD.name
and nS.partition = nD.partition
and nS.instance = nD.instance and nD.state = 'a'
and not
( nN.totalRows = nD.totalRows
and nN.nActive = nD.nActive
and nN.nPages = nD.nPages
and nN.reorgInserts = nD.reorgInserts
and nN.REORGDELETES = nD.REORGDELETES
and nN.REORGUPDATES = nD.REORGUPDATES
and nN.REORGMASSDELETE = nD.REORGMASSDELETE
and nN.dataSize = nD.dataSize
) ) difNew
from s nS
join OA1P.TQZ006GBGRTSSTATS nN
on nS.rz = nN.rz and nS.dbSys = nN.dbSys
and nS.dbName = nN.dbName and nS.name = nN.name
and nS.partition = nN.partition and nS.instance = nN.instance
and nS.updatestatstime = nN.updateStatsTime
)
, e as
(
select
( select min(updatestatsTime)
from OA1P.TQZ006GBGRTSSTATS a
where a.rz = d.rz and a.dbSys = d.dbSys
and a.dbName = d.dbName and a.name = d.name
and a.partition = d.partition
and a.instance = d.instance and a.state = 'a'
and a.updateStatsTime
> value(d.difNew, d.updateStatsTime - 1e-6 seconds)
) eqOld
, d.*
from n d
)
select char(value(strip(dbName) || ',' || strip(name)
|| ',' || partition
|| ',' || char(eqOld)
|| ',' || char(updateStatsTime), ''), 80) txt
from e
order by dbName, name, partition
;
;x;