zOs/SQL/TECSVXBA
with a as
( --- union of segments and infos for new tables --------------------
select enStorAr n , right('000' || enSeg, 3) seg
from bua.txba201 a
group by enStorAr, enSeg
)
, b as
(
select *
from a
where not exists (select 1
from BUA.TXBI003 i
where i.storageArea_N = a.n
and i.segment = a.seg
and i.partNumber = 1
)
)
, c as
(
select n, seg
, case when n <= 999 then right('000' || n, 3)
when n <= 35657 /* 1296 = 36**2 */
then substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, (n + 10998) / 1296 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 1296) / 36 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 36) + 1, 1)
end stoAr
from b
)
, t as
(
select c.*, t.dbName db, t.tsName ts, t.creator cr, t.name tb
from c left join sysibm.sysTables t
on left(t.name, 8) = 'XB' || c.stoAr || c.seg
)
select ' INCLUDE TABLESPACE ' || db || '.' || ts || ' PARTLEVEL 1'
from t
order by 1
;x;
select * from t
order by db, ts
with ur
;x;
select storageArea, storageArea_N, segment, partNumber, stage
, 'i', LASTHKTS, lastImport
)
, sG (area, n, areaC, seg, pa, stage, sTb, hkTS, laIm, err) as
( --- group numeric Area, seg and pa
--- compute alpha area from numeric area -------------------
select min(area), n
, case when n <= 999 then right('000' || n, 3)
when n <= 35657 /* 1296 = 36**2 */
then substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, (n + 10998) / 1296 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 1296) / 36 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 36) + 1, 1)
end
, seg, pa
, max(stage)
, max(sTb)
, max(hkTS)
, max(laIm)
, case when sum(case when sTb = 'i' then 1 else 0 end) > 1
then ' multiXbi003' else '' end
||case when sum(case when sTb = 'a' then 1 else 0 end) > 1
then ' multiXba201' else '' end
from sU
group by n, seg, pa
)
;x;***************************
with sU (area, n, seg, pa, stage, sTb, hkTS, laIm) as
( --- union of segments and infos for new tables --------------------
select storageArea, storageArea_N, segment, partNumber, stage
, 'i', LASTHKTS, lastImport
from BUA.TXBI003 R
union all select '?', enStorAr, right('000' || enSeg, 3), 1, '-a'
, 'a', cast(null as timestamp), cast(null as timestamp)
from bua.txba201
)
, sG (area, n, areaC, seg, pa, stage, sTb, hkTS, laIm, err) as
( --- group numeric Area, seg and pa
--- compute alpha area from numeric area -------------------
select min(area), n
, case when n <= 999 then right('000' || n, 3)
when n <= 35657 /* 1296 = 36**2 */
then substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, (n + 10998) / 1296 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 1296) / 36 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 36) + 1, 1)
end
, seg, pa
, max(stage)
, max(sTb)
, max(hkTS)
, max(laIm)
, case when sum(case when sTb = 'i' then 1 else 0 end) > 1
then ' multiXbi003' else '' end
||case when sum(case when sTb = 'a' then 1 else 0 end) > 1
then ' multiXba201' else '' end
from sU
group by n, seg, pa
)
, seg(t8, pa, stage, sTb, hkTs, laIm, err) as
( --- compute t8 = eaDba = 'XB' || area || seg ----------------------
select 'XB' || areaC || seg
, smallInt(pa), stage, sTb, hkTs, laIm
, case when area <> '?' and area <> areaC
then ' areaC<>' || area else '' end || err
from sG
)
, uU (eaDba, db, ts, pa, unl, sTb) as
( --- union of both unload tables -----------------------------------
select eaDba, substr(earess, 4, 8)
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13))
, partNumber pa, eaRess, 'c'
from BUA.TXBC021 t
where EYRESS = 5000 and ESRESS = 0
union all select eaDba, substr(earess, 4, 8)
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13))
, partNumber pa, eaRess, 's'
from BUA.TXBC021s t
where EYRESS = 5000 and ESRESS = 0
)
, uG (eaDba, db, ts, pa, unl, sTb, err) as
( --- group uU -----------------------------------------------------
select max(eaDba), db, ts, pa, max(unl), max(sTb)
, case when count(*) <> 1
then ' multiUnl-' || min(sTb) || '-' || max(sTb)
else '' end
from uU
group by db, ts, pa
--- without fetch first or order by we get
--- SQLCODE = -171: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT
--- 1 OF INSTR OR LOCATE_IN_STRING IS INVALID ------------
fetch first 2147483647 rows only
)
, unl(eaDba, db, ts, pa, unl, pun, sTb, err) as
( --- check unl, derive pun ----------------------------------------
select eaDba, db, ts, pa
, value(unl, '')
, case when unl is null or unl = '' then ''
when locate_in_string(unl, '.', -1, 2) < 1 then ''
when substr(unl, locate_in_string(unl, '.', -1, 2) + 1
, locate_in_string(unl, '.', -1, 1)
- locate_in_string(unl, '.', -1, 2) - 1)
<> 'SYSREC' then ''
else left(unl, locate_in_string(unl, '.', -1, 2) )
|| 'SYSPCH'
end
, sTb
, case when unl is null or unl = '' then ''
when unl not like 'XB.XB%' then ' unlNotXB.XB%'
when locate('.', unl, 4) <> 12 then ' unlDbLen'
when locate('.', unl, 13) not between 14 and 21
then ' unlTsLen'
when locate_in_string(unl, '.', -1, 2) <1 then ' unl<2q'
when substr(unl, locate_in_string(unl, '.', -1, 2) +1
, locate_in_string(unl, '.', -1, 1)
- locate_in_string(unl, '.', -1, 2) - 1)
<> 'SYSREC' then ' sysrecNotInUnl'
else ''
end || err
from uG
)
, tp (db, ts, pa, t8, err) as
( --- tablePart and tables from db2 catalog -------------------------
select t.dbName, t.tsName, partition, max(left(t.name, 8))
, case when max(left(t.name, 8)) <> min(left(t.name, 8))
then ' multiTables' else '' end
from sysibm.sysTables t
join sysibm.sysTablePart p on
t.dbName = p.dbName and t.tsName = p.tsName
where t.dbName like 'XB%'
and t.type not in ('A', 'V')
group by t.dbName, t.tsName, partition
)
, j2 (db, ts, pa, t8, unl, pun, sTb, err) as
( --- join unl and tp ------------------------------------------------
select value(tp.db, unl.db)
, value(tp.ts, unl.ts)
, value(tp.pa, unl.pa)
, value(tp.t8, unl.eaDba) eaDba
, unl.unl
, unl.pun
, unl.sTb
, case when tp.db is null then ' notInDB2' else '' end
|| case when tp.t8 <> unl.eaDba then ' t8<>eaDba=' || unl.eaDba
else '' end
|| value(unl.err, '') || value(tp.err, '')
from tp
full outer join unl
on tp.db = unl.db and tp.ts = unl.ts and tp.pa = unl.pa
)
--- select count(*), err from j2 group by err;x;
, j3 (db, ts, pa, t8, stage, unl, pun, sTb, hkts, err) as
( --- join segments -------------------------------------------------
select char(value(j2.db, '-noDB-'), 8)
, char(value(j2.ts, j2.t8, seg.t8, '-noTsT8-'), 8) --- avoid dups
, smallInt(value(j2.pa, seg.pa, -99))
, char(value(j2.t8, seg.t8, '-noT8-'), 8)
, case when seg.stage is null and j2.ts like '%WWW%' then '-w'
else value(seg.stage, '-m') end
, case when seg.stage is null and j2.ts like '%WWW%'
then 'XB.MIG.U.' || db || '.' || ts
|| '.P' || right('0000'|| j2.pa, 5)||'.REC.D15338'
else value(j2.unl, '') end
, case when seg.stage is null and j2.ts like '%WWW%'
then 'XB.MIG.U.' || db || '.' || ts || '.PUN.D15338'
else value(j2.pun, '') end
, value(seg.sTb, '') || value(j2.sTb, '')
, value(seg.hkts, '1111-11-11-11.11.11.111111')
, case when j2.db is null then ' notInDB2' else '' end
|| value(seg.err, '') || value(j2.err, '')
from j2
full outer join seg
on j2.t8 = seg.t8 and j2.pa = seg.pa
)
, j (db, ts, pa, stage, unlTst, unl, pun, sTb, t8, err) as
( --- final values and errors ---------------------------------------
select db, ts,pa, stage
, case when stage = '-w' then '2015-12-04-16.00.00.000000'
else hkts end
, unl, pun, sTb, t8
, strip(case when stage = '-m' then ' notInXbi003,Xba201'
when stage not in ('RW', 'CL', 'UL', 'DL', '-a', '-w')
then ' badStage=' || stage
when stage in ('UL', 'DL', '-w') and unl = ''
then ' noUnload '
when stage not in ('CL', 'DL', 'UL', '-w') and unl <> ''
then ' unloadInBadStage'
else '' end
||case when left(unl, 21) <> left(pun, 21) then ' prefUnl<>Pun'
else '' end || err)
from j3
)
select db, ts, pa, stage
, '1111-11-11-11.11.11.111111'
, value(sTb, '')
, unlTst
, unl
, '1111-11-11-11.11.11.111111'
, pun
, ''
, err
from j