zOs/REXX/CONSUMXB
declare global temporary table session.unl
( db char(8), ts char(8), pa smallint, sta char(2)
, unl char(44), info varChar(70), err varchar(20)
) on commit preserve rows
;
create unique index session.unlIx on session.unl (db,ts, pa)
include (sta, unl)
;
insert into session.unl
with s as
( -- stage & info from TXBI003
select t.dbName db, t.tsName ts, r.partNumber pa
, value(r.stage, '') sta
, 'xb ' || storageArea || '#' || r.segment
|| ' ' || char(date(lastImport))
|| ' ' || strip(objectFamily) || '@' || bu info
FROM sysibm.systables t
join BUA.TXBI003 r -- storageArea and segment
-- are part of tableName
on substr(t.name, 3, 3) = r.storageArea
and substr(t.name, 6, 3) = r.segment
and t.creator = 'BUA'
and t.name like 'XB%'
)
, e as
( -- unloads from TXBC021 and TXBC021S
-- decode db and ts from unload DSN
select substr(earess, 4, 8) db
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13)) ts
, partNumber pa
, value(eaRess, '') unl
, '1' uTb
from BUA.TXBC021 t
where EYRESS = 5000 and ESRESS = 0
union all select substr(earess, 4, 8) db
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13)) ts
, partNumber pa
, value(eaRess, '') unl
, 's' uTb
from BUA.TXBC021s t
where EYRESS = 5000 and ESRESS = 0
)
, u (db, ts, pa, sta, unl, info) as
(
select -- join stage and unloads
value(s.db, e.db) db
, value(s.ts, e.ts) ts
, value(s.pa, e.pa) pa
, s.sta
, e.unl
, value(s.info, '') || value(' u=' || e.uTb, '')
from s full outer join e
on s.db = e.db and s.ts = e.ts and s.pa = e.pa
-- the migration tables %WWW%
-- have no entries in the above tables
-- however, Kiran will do an unload|
union all select t.dbName, t.tsName, p.partition, 'ww'
, 'XB.' || t.dbName || '.' || t.tsName
|| '.P'|| right('00000' || partition, 5) || '.WWW?llq'
, 'www'
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.tsName like '%WWW%' or t.name like '%WWW%')
)
select db, ts, pa
, min(sta) sta
, min(unl) unl
, min(info) info
-- more than one unload per part?
, case when count(*) <> 1
then ', ' || count(*) || ' unloads' else '' end err
from u
group by db, ts, pa
;
commit
;
--- counts fuer unloads -----------------------------------------------
select count(*) "#parts"
, count(distinct db || '.' || ts) "#TS"
, count(distinct db ) "#DB"
, sum(case when unl is null or unl = '' then 0 else 1 end) "#unl"
, sum(case when sta = 'ww' then 1 else 0 end) "#www"
from session.unl
;
with u as
(
select case when info is null then '---'
when locate('u=', info) > 0
then substr(info, locate('u=', info)) else '' end u
from session.unl
)
select count(*), u
from u
group by u
;
-- check data from TXBI003 and TXBC021*
-- check all partitions have metaData
with t as
(
select db, ts
from session.unl
group by db, ts
)
, p as
(
select dbName db, tsName ts, partition pa
from sysibm.sysTablePart p join t
on dbName=db and tsName=ts
)
, e as
(
select value(u.db, p.db) db
, value(u.ts, p.ts) ts
, value(u.pa, p.pa) pa
, sta
, substr(err -- more than one unload per part?
-- missing in DB2 catalog?
|| case when p.db is null then ', part notin DB2' else '' end
-- missing in TXBI003? correct stage?
|| case when sta is null then ', part notin TXBI003'
when sta not in ('RW', 'CL', 'DL', 'UL', 'ww')
then ', bad sta in TXBI003'
else '' end
|| case when unl is null and sta not in ('RW', 'CL')
then ', no unl in TXBC021%' else '' end
-- check unl dsn --------
|| case when unl not like 'XB.XB%'
then ', unl not XB.XB% ' else '' end
|| case when locate('.', unl, 4) <> 12
then 'unl db len ' else '' end
|| case when locate('.', unl, 13) not between 14 and 21
then ', unl ts len' else '' end
|| case when locate('.', unl, 13) not between 14 and 21
then ', unl ts len' else '' end
-- is part in DSN correct?
|| case when substr(unl, locate('.', unl, 13), 8)
<> '.P' || right('00000' || u.pa, 5) || '.'
then ', pa dsn=' || substr(unl, locate('.', unl, 13), 8)
else '' end
|| case when p.pa < 1 then ', part<1'
when p.pa > 200 and sta not in ('UL', 'DL')
then ', part>200' else '' end
|| case when p.pa >
case p.db || '.' || left(p.ts, 7)
when 'XBDJC001.SDJC004' then 231 --4 ts, 231 minPa
when 'XBDJC001.SDJC005' then 607 --4 ts, 607 minPa
when 'XBDJC001.SDJC006' then 601 --4 ts, 601 minPa
when 'XBDJC001.SDJC007' then 441 --4 ts, 441 minPa
when 'XBDJC001.SDJC008' then 301 --4 ts, 301 minPa
when 'XBDJC002.SDJC010' then 321 --4 ts, 321 minPa
when 'XBDJC002.SDJC011' then 270 --4 ts, 270 minPa
when 'XBDPM001.SDPM002' then 212 --4 ts, 212 minPa
when 'XBFC4001.SFC4002' then 501 --4 ts, 501 minPa
when 'XBFC4001.SFC4003' then 301 --4 ts, 301 minPa
when 'XBFC4001.SFC4004' then 301 --4 ts, 301 minPa
when 'XBFC4001.SFC4005' then 336 --4 ts, 336 minPa
when 'XBFC4001.SFC4006' then 330 --4 ts, 330 minPa
when 'XBFC4001.SFC4007' then 249 --4 ts, 249 minPa
when 'XBFC4002.SFC4009' then 281 --4 ts, 281 minPa
when 'XBFQY002.SFQY002' then 202 --5 ts, 202 minPa
else 200
end
then ', part >200/aus' else '' end
|| ' ', 2) err
, unl, info
from session.unl u full outer join p
on u.db = p.db and u.ts = p.ts and u.pa = p.pa
)
select * from e
where err <> ''
order by 1, 2, 3
with ur
;
--- tables from DB XB% missing in TXBI003 -----------------------------
select substr(dbName, 1, 8) db
, substr(tsName, 1, 8) ts
, substr(creator, 1, 8) cr
, name tb
from sysibm.sysTables t
where dbName like 'XB%'
and not exists (select 1 from session.unl u
where t.dbName = u.db and t.tsName = u.ts)
order by 1, 2
;
--- www tables --------------------------------------------------------
select db, ts
from session.unl
where sta = 'ww'
group by db, ts
order by 1, 2
;x
--- generate partition exception list --------------------------------
select 'when ''' || dbName || '.' || left(name, 7)
|| ''' then ' || max(partitions)
|| ' -- ' || count(*) || ' ts, ' || min(partitions) || ' minPa'
from sysibm.sysTablespace
where dbName like 'XB%' and partitions > 200
group by dbName, left(name, 7)
order by 1
;