zOs/SQL/CATRECUN
$#=
$>. fEdit()
$=useLgRn = 0
$=isElar = 0
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, s.dbId, s.psId
, 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
)
, p3 as
(
select db, ts, pa, inst, pSpc, insTxt, dbId, psId
, 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 'okLog' 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
)
, u as
(
select p.*
, 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
from p
left join oa1p.tqz005TecSvUnload u
on u.db = p.db and u.ts = p.ts and u.pa = p.pa
and u.pa >= 0
)
, e2 as
(
select u.*
, 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 r.dbName is null then 'noRTS'
when r.lastDataChange > u.unlTst then 'dataChange>unl'
when r.copyUpdatetime > unlTst then 'copyUpdate>unlTst'
when BasTst > unlTst
$@ if \ $isElar then $@=[
and (date(incTst) <> '18.05.2015' or basTy <> 'F')
$]
then 'unlTst<ful='||basTyTx
when incTy='I' and unlTst < incTst
$@ if \ $isElar then $@=[
and date(incTst) <> '18.05.2015'
$]
then 'unlTst<incCopy'
when r.copyChanges <> 0 then 'copyChanges<>0'
when r.copyUpdatedPages <> 0 then 'updatedPages<>0'
$*(
when r.copyUpdatetime > BasTst and basTy = 'F'
then 'copyUpdate>ful='||basTyTx
when r.copyUpdatetime > incTst and incTy = 'I'
then 'copyUpdate>incCopy'
$*)
when r.copyUpdatetime is not null then 'copyUpdateNotNull'
when r.lastDataChange > u.BasTst and u.basTy not in('A','-')
$@ if \ $isElar then $@=[
and unlTst > '2015-09-12-12.00.00'
$]
then 'dataChange>ful='||basTyTx
when basTy <> 'F' then 'lastFul=' || basTyTx
when incTy = 'I' and incTst > unlTst
$@ if \ $isElar then $@=[
and date(incTst) <> '18.05.2015'
$]
then 'incTst>unlTst'
when r.lastDataChange is null and unlTst
< '2015-04-15-00.00.00' then 'dataChangeV11>unl'
when lastDataChange is null
and u.incTst < '2015-04-15-00.00.00'
then 'dataChangeV11>incCopy'
$@ if \ $isElar then $@=[
when incTy = 'I' and incTst > unlTst
and date(incTst) = '18.05.2015'
then 'inc180515>unl'
$]
else ''
$@ if $useLgRn then $@=[
end || case
when unl = ''
or unlTst < current timestamp - 100 years then ''
when lr.start > unlTst then ' lgRn>unl'
when lr.start is null then ' lgRnNone'
else ''
$]
end) erReU
from u
left join sysibm.sysTableSpaceStats r
on u.dbId = r.dbId and u.psId = r.psId
and u.pa = r.partition and u.inst = r.instance
and u.db = r.dbName and u.ts = r.name
$@ if $useLgRn then $@=[
left join oa1u.tqz004TecSvLgRn lr
on u.db = lr.db and u.ts = lr.ts and u.pa = lr.pa
$]
)
, e as
(
select e2.*
, strip(case
when stage = '' then recov || ':' || basTyTx
$@ if $isElar then $@=[
when ts in -- gestoppte TS, im Loeschprozess
( 'SF710141'
, 'SF710142'
, 'SF710143'
, 'SF71014H'
, 'SF760141'
, 'SF760142'
, 'SF760143'
, 'SF76014H'
) then 'toDrop'
-- when stage = '-w' then '-m'
when stage in ('UL', 'DL', '-w') and erReU <> ''
then erReU
when stage in ('UL', 'DL', '-w') then ''
when erReD <> '' then erReD
$] $@ else $@=[
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 count(*), stage
, substr(strip(erRec || ' ' || errUnl), 1, 70)
-- , min(err), max(err)
-- , min(unl), max(unl)
from e
group by stage, strip(erRec || ' ' || errUnl)
order by 2, 3
;