zOs/SQL/TECSVDDX
set current sqlid = 'S100447';
drop view oa1p.vqz005RecovLoad ;
create view oa1p.vqz005RecovLoad as
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, p2 as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, p.space pSpc
, value(stage, '') stage
, value(staUpd, '1111-11-11-11.11.11') staUpd
, value(staTb, '') staTb
, value(unlTst, '1111-11-11-11.11.11') unlTst
, value(unl, '') unl
, value(punTst, '1111-11-11-11.11.11') punTst
, value(pun, '') pun
, value(u.info, '') infoUnl
, value(u.err, '') errUnl
, r.dbName rDb
, r.lastDataChange
, r.copyUpdatetime
, r.copychanges
, r.copyupdatedpages
, case when s.instance = 1 and s.clone = 'N' then '1'
when s.clone = 'N' then '' || s.instance || 'only'
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clone' end insTxt
, ( select max( substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
|| max(case when icType <> 'I'
then substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5) else '' end )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) icPa
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
|| max(case when icType <> 'I'
then substr(char(timestamp), 1, 26) ||char(icType, 1)
|| right(' ' || dsNum, 5) else '' end )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) ic0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
left join oa1p.tqz005TecSvUnload u
on u.db = s.dbName and u.ts = s.name and u.pa = p.partition
and u.pa >= 0
left join sysibm.sysTableSpaceStats r
on s.dbId = r.dbId and s.psId = r.psId
and p.partition = r.partition and two.i = r.instance
and s.dbName = r.dbName and s.name = r.name
)
, p3 as
(
select p2.*
, max(value(substr(icPa, 33, 32), '1111-11-11-11.11.11.111111- ')
,value(substr(ic0 , 33, 32), '1111-11-11-11.11.11.111111- ')
, '1111-11-11-11.11.11.111111- ') basTTP
, max(value(substr(icPa, 1, 32), '1111-11-11-11.11.11.111111- ')
,value(substr(ic0 , 1, 32), '1111-11-11-11.11.11.111111- ')
, '1111-11-11-11.11.11.111111- ') incTTP
from p2
)
, p4 as
(
select p3.*
, substr(basTTP, 27, 1) basTy
, substr(basTTP, 28, 5) basPa
, timestamp(substr(basTTP, 1, 26)) basTst
, substr(incTTP, 27, 1) incTy
, substr(incTTP, 28, 5) incPa
, timestamp(substr(incTTP, 1, 26)) incTst
from p3
)
, p as
(
select p4.*
, case when pSpc = -1 then 'okDefNo'
when basTy not in ('A','C','F','R','X') then 'logDisc'
when basTst < current timestamp - 23 days then 'tooOld'
else 'ok' end recov
, basTy || case basTy
when '-' then '=missing'
when 'A' then '=addPart'
when 'C' then '=create'
when 'F' then '=fulCopy'
when 'I' then '=incCopy'
when 'P' then '=recPIT'
when 'R' then '=LoaRpLog'
when 'S' then '=LoaRpLoNo'
when 'W' then '=ReorgLoNo'
when 'X' then '=ReorgLog'
when 'Y' then '=LoaRsLoNo'
else '=???' end basTyTx
from p4
)
, e2 as
(
select p.*
, case when recov = 'okDefNo' then ''
when recov = 'tooOld' or ( left(recov, 2) = 'ok'
and basTst < current timestamp - 15 day)
then 'older15d' || ':' || basTyTx
when left(recov, 2) = 'ok'
and basTst < current timestamp -8 day
then 'older8d' || ':' || basTyTx
when left(recov, 2) = 'ok' then ''
else recov || ':' || basTyTx
end erReD
, strip(case
when unl = '' then 'noUnload'
when unlTst < current timestamp-100 year
then 'unlTstNull'
when rDb is null then 'noRTS'
when lastDataChange > p.unlTst then 'dataChange>unl'
when copyUpdatetime > unlTst then 'copyUpdate>unlTst'
when BasTst > unlTst
and (date(incTst) <> '18.05.2015' or basTy <> 'F')
then 'unlTst<ful='||basTyTx
when incTy='I' and unlTst < incTst
and date(incTst) <> '18.05.2015'
then 'unlTst<incCopy'
when copyChanges <> 0 then 'copyChanges<>0'
when copyUpdatedPages <> 0 then 'updatedPages<>0'
when copyUpdatetime is not null then 'copyUpdateNotNull'
when lastDataChange > p.BasTst and p.basTy not in('A','-')
and unlTst > '2015-09-12-12.00.00'
then 'dataChange>ful='||basTyTx
when basTy <> 'F' then 'lastFul=' || basTyTx
when incTy = 'I' and incTst > unlTst
and date(incTst) <> '18.05.2015'
then 'incTst>unlTst'
when lastDataChange is null and unlTst
< '2015-04-15-00.00.00' then 'dataChangeV11>unl'
when lastDataChange is null
and p.incTst < '2015-04-15-00.00.00'
then 'dataChangeV11>incCopy'
when incTy = 'I' and incTst > unlTst
and date(incTst) = '18.05.2015'
then 'inc180515>unl'
else ''
end) erReU
from p
)
, e as
(
select e2.*
, strip(case
when stage = '' then recov || ':' || basTyTx
when stage in ('IN', 'UL')
and erReD <> '' and erReU <> ''
then erReU || ' ' || erReD
when stage = 'IN' and unl <> '' and
staUpd < current timestamp - 24 hour
then 'stillUnlAft24h'
when stage not in ('IN', 'UL') and erReD <> '' then erReD
else ''
end ) erRec
from e2
)
select *
from e
;