zOs/SQL/XBBN
declare global temporary table session.xbPa
( db char(8), ts char(8), pa smallint
, cr char(3), tb char(20)
, sta char(2)
, unl char(44)
, j char(3), info varChar(70), err varchar(20)
) on commit preserve rows
;
create unique index session.xbPaIx on session.xbPa (db,ts, pa)
include (sta, unl)
;
insert into session.xbPa
with u (db, ts, pa, unl, info) as
( -- unloads from TXBC021 and TXBC021S
-- decode db and ts from unload DSN
select substr(earess, 4, 8)
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13))
, partNumber
, value(eaRess, '')
, 'u1'
from BUA.TXBC021 t
where EYRESS = 5000 and ESRESS = 0
union all select substr(earess, 4, 8)
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13))
, partNumber
, value(eaRess, '')
, 'uS'
from BUA.TXBC021s t
where EYRESS = 5000 and ESRESS = 0
-- www table from migration
-- have no entries in the above tables
-- however, Kiran will do an unload|
union all select t.dbName, t.tsName, p.partition
, '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%')
)
, xt as
(
select 'BUA' cr,
ar.eypref || ar.enstorar || sg.segment
|| ar.eykngklass || ar.eykngocc
|| ar.enzeile || ar.enspalte tb
, sg.partnumber pa
, stage sta
, 'xb ' || sg.storageArea || '#' || sg.segment
|| ' ' || strip(objectFamily) || '@' || bu
|| ' ' || char(date(logProdFrom))
|| '-' || char(date(logProdTo)) info
, row_number() over() rn
from bua.txbi003 sg -- segement table
inner join bua.txbc091 ar -- storageareatables table
on ar.enenter = 0 and ar.eypref = 'XB'
and ar.enstorar = sg.storagearea
)
, p as
(
select t.creator cr, t.name tb, t.dbName db, t.tsName ts
, p.partition pa
from sysibm.sysTables t join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
where t.dbName like 'XB%'
)
, j as
(
select value(p.db, u.db, '?' || xt.rn) db
, value(p.ts, u.ts, '?' || row_number() over ()) ts
, value(p.pa, u.pa) pa
, value(p.cr, xt.cr) cr
, value(p.tb, xt.tb) tb
, value(xt.sta, case when u.info like '%www%' then 'ww'
else null end) sta
, u.unl
, case when p.db is null then '' else 'd' end
|| case when u.db is null then '' else 'u' end
|| case when xt.cr is null then '' else 'x' end j
, value(' ' || strip(xt.info), '' )
|| value(' ' || strip(u.info), '' ) info
from p
full outer join u
on u.db = p.db and u.ts = p.ts and u.pa = p.pa
full outer join xt
on xt.cr = p.cr and xt.tb = p.tb and xt.pa = p.pa
)
select db, ts, pa
, max(cr) cr
, max(tb) tb
, max(sta) sta
, max(unl) unl
, max(j) j
, max(info) info
-- more than one unload per part?
, case when count(*) <> 1
then ', ' || count(*) || ' joins' else '' end err
from j
group by db, ts, pa
;
select count(*), count(unl), count(distinct unl)
from session.xbPa
;
select *
from session.xbPa
where err <> ''
order by db, ts, cr, tb, pa
;
with e as
(
select db, ts, pa, cr, tb, sta, unl, j, info, substr(
err -- more than one row per part?
-- missing in DB2 catalog?
|| case when j not like '%d%' then ', part notin DB2' else '' end
-- missing in TXBI003? correct stage?
|| case when j not like '%x%' and (sta is null or sta <> 'ww')
then ', part notin TXBI003'
when sta is null then ', sta null'
when sta not in ('RW', 'CL', 'DL', 'UL', 'ww')
then ', bad sta in TXBI003'
else '' end
|| case when unl is null or unl = '' then
case when sta in ('RW', 'CL') then ''
else ', no unload in TXBC021?' end
-- check unl dsn --------
when unl not like 'XB.XB%'
then ', unl not XB.XB% '
when locate('.', unl, 4) <> 12 then 'unl db len '
when locate('.', unl, 13) not between 14 and 21
then ', unl ts len'
-- is part in DSN correct?
when substr(unl, locate('.', unl, 13), 8)
<> '.P' || right('00000' || pa, 5) || '.'
then ', pa<>'
|| substr(unl, locate('.', unl, 13), 8)
else '' end
|| case when pa < 1 then ', part<1'
when pa > 200 and sta not in ('UL', 'DL')
then ', part>200' else '' end
|| case when pa >
case db || '.' || left(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
from session.xbPa x
)
select db, ts, min(pa) paMin, max(pa) paMax, count(*) cnt
, cr, tb, info, j, err
from e
where err <> ''
group by db, ts, cr, tb, info, j, err
order by db, ts, cr, tb, min(pa)
;
select db, ts, min(pa) paMin, max(pa) paMax, count(*) cnt
, cr, tb, info, j, err
from session.xbPa
where sta = 'ww'
group by db, ts, cr, tb, info, j, err
order by db, ts, cr, tb, min(pa)
;x;
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
;
--*** elar NDBS *******************************************************
--- ndbs: neuer Elar Design seit 2013/14 ------------------------------
--- global table fuer Partitionen, stage, segment ---------------------
declare global temporary table session.xbPa
( db char(8), ts char(8), pa smallInt, stage char(2), seg char(6)
) on commit preserve rows;
create unique index session.ndPaIx on session.ndPa (db,ts, pa)
include (stage, seg)
;
insert into session.ndPa
select t.dbName, t.tsName
, r.partNumber, r.stage, r.storageArea || r.segment
FROM sysibm.systables t
join BUA.TXBI003 R
on substr(t.name, 3, 3) = r.storageArea
and substr(t.name, 6, 3) = r.segment
where t.creator = 'BUA'
and t.name like 'XB%'
;
commit
;
--- counts fuer ndbs --------------------------------------------------
select count(*) "ndbs Parts"
, count(distinct db || '.' || ts) "ndbs TS's"
, count(distinct db ) "ndbs DB's"
from session.ndPa
;
commit
;
--************************************************************
--$$r XB ndbs - falscher stage in TXBI003
--************************************************************
select *
from session.ndPa
where stage not in ('RW', 'CL', 'UL', 'DL')
order by db, ts, pa
;
--************************************************************
--$$r XB ndbs - fehlender/falscher stage in TXBI003
--************************************************************
select char(p.dbName, 8) db
, char(p.tsName, 8) ts
, smallint(p.partition) part
, n.stage
, n.seg
, int(totalRows) rtsRows
from sysibm.sysTablePart p
join sysibm.sysTablespace s
on s.dbName = p.dbName and s.name = p.tsName
left join session.ndPa n
on p.dbName = n.db and p.tsName = n.ts and p.partition = n.pa
left join sysibm.sysTablespaceStats r
on s.dbid = r.dbid and s.psid = r.psid
and p.partition = r.partition and s.instance = r.instance
where p.dbName in (select db from session.ndPa)
and tsName not like '%WWW%'
and (n.stage is null
or stage not in ('RW', 'CL', 'UL', 'DL'))
and totalRows <> 0 -- ??? temporaer, bis stage DL nachgefuehrt
order by 1, 2, 3
;
commit;
-- ndbs: temporary table für unloads
declare global temporary table session.unl
( db char(8), ts char(8), pa smallint, unl char(44), err varChar(30)
) on commit preserve rows;
create unique index session.unlIx on session.unl (db,ts, pa)
include (unl)
;
insert into session.unl
with f as
(
select substr(earess, 4, 8) db
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13)) ts
, partNumber pa, eaRess, '1' ptb
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, eaRess, 's' ptb
from BUA.TXBC021s t
where EYRESS = 5000 and ESRESS = 0
)
, g as
(
select db, ts, pa, min(eaRess) eaRess, count(*) cnt
, min(pTb) || '+' || max(pTb) pTb
from f
group by db, ts, pa
)
select db, ts, pa, eaRess
, case when earess not like 'XB.XB%'
then 'eaRess not XB.XB% ' || pTb
when locate('.', earess, 4) <> 12
then 'eaRess db len ' || pTb
when locate('.', earess, 13) not between 14 and 21
then 'eaRess ts len ' || pTb
when cnt <> 1 then 'duplicates ' || cnt || ' ' || pTb
else '' end err
from g
with cs
;
commit;
--************************************************************
--$$r XB ndbs - ungueltige Einträge in BUA.TXBC021/S
--************************************************************
select *
from session.unl
where err <> ''
order by db, ts, pa
;
--************************************************************
--$$r XB ndbs - fehlende unloads fuer stage UL
--************************************************************
select p.*
from session.ndPa p
left join session.unl u
on p.db = u.db and p.ts = u.ts and p.pa = u.pa
where stage = 'UL' and u.db is null
order by db, ts, pa
;
--************************************************************
--$$r XB ndbs - nicht katalogisierte Unloads
--************************************************************
select p.db, p.ts, p.pa, p.stage, u.unl
from session.ndPa p
join session.unl u
on p.db = u.db and p.ts = u.ts and p.pa = u.pa
and p.stage = 'UL'
where s100447.dslocate(unl) is null
order by db, ts, pa
;
commit;
?????????????????????????????????
with xt as
(
select t.creator cr, t.name tb, t.dbName db, t.tsName ts
, sg.*
, ar.*
from bua.txbi003 sg -- segement table
inner join bua.txbc091 ar -- storageareatables table
on enenter = 0 and ar.eypref = 'XB'
and ar.enstorar = sg.storagearea
inner join SYSIBM.SYSTABLES t
on T.CREATOR = 'BUA'
and t.name = ar.eypref || ar.enstorar || sg.segment
|| ar.eykngklass || ar.eykngocc
|| ar.enzeile || ar.enspalte
and ar.eyPref = left(t.name, 2)
and ar.enstorar = substr(t.name, 3, 3)
and sg.segment = substr(t.name, 6, 3)
and ar.eykngKlass = substr(t.name, 9, 1)
and ar.eykngocc = substr(t.name, 10, 1)
and ar.enZeile = substr(t.name, 11, 3)
and ar.enSpalte = substr(t.name, 14, 3)
)
, xu as
(
select u1.eaRess u1, us.earess us, xt.*
from xt
left outer JOIN BUA.TXBC021s us
on us.EADBA = 'XB'||STORAGEAREA||SEGMENT
AND xt.PARTNUMBER = us.PARTNUMBER
AND LOCATE(STRIP('.' || strip(TS) || '.'), us.EARESS) > 0
and us.EYRESS = 5000 AND us.ESRESS= 0
left outer JOIN BUA.TXBC021 u1
on u1.EADBA = 'XB'||STORAGEAREA||SEGMENT
AND xt.PARTNUMBER = u1.PARTNUMBER
AND LOCATE(STRIP('.' || strip(TS) || '.'), u1.EARESS) > 0
and u1.EYRESS = 5000 AND u1.ESRESS= 0
)
select count(*) parts, count(distinct tb) tables
, count(u1) u1, count(us) us, count(distinct us) usDist
from xu
-- where cr = 'BUA' and tb like 'XBCZR003PS0%'
where objectFamily = 'gm.best'
;x;
with ur ;x;
select ENENTER
, EYPREF
, ENSTORAR
, count(*)
from BUA.TXBC091
where enenter = 0 -- and eypref = 'XB'
group by enenter, eypref, enstorar
order by 4 asc
fetch first 1000 rows only
with ur
;x;
select count(*), EADBA, partnumber
from bua.txbc021s
where EYRESS = 5000 AND ESRESS= 0
group by EADBA, partnumber
order by 1 desc
fetch first 100 rows only
;x;
select count(*), count(distinct name) type
from SYSIBM.SYSTABLES t
where dbname like 'XB%'
and name not like '%WWW%'
group by type
;
1 ENENTER
2 EYPREF
3 ENSTORAR
4 EYKNGKLASS
5 EYKNGOCC
6 ENZEILE
7 ENSPALTE
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%'
)
select count(distinct db || '.' || ts) from s
with ur
;
with xt as
(
SELECT 'BUA' cr,
T091.EYPREF||T091.ENSTORAR||SEGMENT||EYKNGKLASS||
EYKNGOCC||ENZEILE||ENSPALTE TB
, PARTNUMBER PARTNR
, T091.EYPREF||T091.ENSTORAR||SEGMENT SEG,
T003.STATUS,STAGE,OBJECTFAMILY, bu, LOGPRODFROM,LOGPRODTO
FROM BUA.TXBI003 T003
INNER JOIN BUA.TXBC091 T091 ON ENENTER = 0 AND T091.EYPREF = 'XB'
AND T091.ENSTORAR = STORAGEAREA
)
select count(distinct tb) from xt
with ur;x;
inner join SYSIBM.SYSTABLES t
on T.CREATOR = xt.cr and t.name = xt.tb
with ur ;x;
select distinct dbName, tsName -- , xt.*
from xt full outer join
(select * from SYSIBM.SYSTABLES where dbName like 'XB%') t
on T.CREATOR = xt.cr and t.name = xt.tb
-- where objectFamily = 'exab.form.akt.2'
where t.creator is null
or (xt.cr is null and not t.name like '%WWW%')
with ur
;x;
--- sql from tool elarBN to find unload etc......
/* --- find objectFamily = exab.form.akt.2
year = 2012
SELECT OBJECTFAMILY,YEAR(LOGPRODTO)
FROM BUA.TXBI003
WHERE STORAGEAREA = 'FJ3'
AND SEGMENT = '001'
; ----------------------------------------------------------------
*/
WITH DSN_INLINE_OPT_HINT
(CORRELATION_NAME,ACCESS_TYPE,ACCESS_NAME,JOIN_SEQ,JOIN_METHOD) as
(
VALUES ('T003', 'INDEX', NULL, 1, NULL) ,
('T091', 'INDEX', NULL, 2, 'NLJ'),
('SYST', 'INDEX', NULL, 3, 'NLJ'),
('SYSC', 'INDEX', NULL, 4, 'NLJ'),
('C21A', 'INDEX', NULL, 6, 'NLJ'),
('C21B', 'INDEX', 'IXBC021U' ,7, 'NLJ' )
)
, TB_IC AS
(
SELECT DSNAME DSN,'BUA' CREATOR,SYST.DBNAME,SYST.TSNAME,
T091.EYPREF||T091.ENSTORAR||SEGMENT||EYKNGKLASS||
EYKNGOCC||ENZEILE||ENSPALTE TB,PARTNUMBER PARTNR,
SEGMENT SEGID,STORAGEAREA,
T091.EYPREF||T091.ENSTORAR||SEGMENT SEG,
T003.STATUS,STAGE,OBJECTFAMILY,LOGPRODFROM,LOGPRODTO
FROM BUA.TXBI003 T003
INNER JOIN BUA.TXBC091 T091 ON ENENTER = 0 AND T091.EYPREF = 'XB'
AND T091.ENSTORAR = STORAGEAREA
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME
= T091.EYPREF||T091.ENSTORAR||SEGMENT||EYKNGKLASS||EYKNGOCC
LEFT JOIN SYSIBM.SYSCOPY SYSC ON SYSC.DBNAME = SYST.DBNAME
AND SYSC.TSNAME = SYST.TSNAME AND PARTNUMBER = SYSC.DSNUM
AND SYSC.ICTYPE = 'F' AND SYSC.OTYPE = 'T'
WHERE STAGE IN ('RW','CL')
AND SYSC.TIMESTAMP = (SELECT MAX(TIMESTAMP)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = SYST.DBNAME
AND TSNAME = SYST.TSNAME
AND PARTNUMBER = DSNUM AND ICTYPE = 'F'
AND OTYPE = 'T'
)
)
, Tm21 AS
(
SELECT OBJECTFAMILY,EARESS,EADBA,STORAGEAREA,SEGMENT SEGID,
LOGPRODFROM,LOGPRODTO,
T003.PARTNUMBER PARTNR,T003.STATUS,STAGE,
T091.EYPREF||T091.ENSTORAR||SEGMENT||EYKNGKLASS||
EYKNGOCC||ENZEILE||ENSPALTE TB,
tsName
FROM BUA.TXBI003 T003
INNER JOIN BUA.TXBC091 T091 ON ENENTER = 0 AND T091.EYPREF = 'XB'
AND T091.ENSTORAR = STORAGEAREA
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME = T091.EYPREF||T091.ENSTORAR||SEGMENT
||EYKNGKLASS||EYKNGOCC||ENZEILE||ENSPALTE
INNER JOIN BUA.TXBC021 C21A ON
EADBA = 'XB'||STORAGEAREA||SEGMENT
AND T003.PARTNUMBER = C21A.PARTNUMBER
AND LOCATE(STRIP(TSNAME), EARESS) > 0
WHERE EYRESS = 5000 AND ESRESS= 0 AND STAGE IN ('UL','DL')
UNION ALL select
OBJECTFAMILY,EARESS,EADBA,STORAGEAREA,SEGMENT SEGID,LOGPRODFROM,
LOGPRODTO,T003.PARTNUMBER PARTNR,
T003.STATUS,STAGE,
T091.EYPREF||T091.ENSTORAR||SEGMENT||EYKNGKLASS
||EYKNGOCC||ENZEILE||ENSPALTE TB,
TSNAME
FROM BUA.TXBI003 T003
INNER JOIN BUA.TXBC091 T091 ON ENENTER = 0 AND T091.EYPREF = 'XB'
AND T091.ENSTORAR = STORAGEAREA
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME = T091.EYPREF||T091.ENSTORAR||SEGMENT
||EYKNGKLASS||EYKNGOCC||ENZEILE||ENSPALTE
INNER JOIN BUA.TXBC021S C21B ON
EADBA = 'XB'||STORAGEAREA||SEGMENT
AND T003.PARTNUMBER = C21B.PARTNUMBER
AND LOCATE(STRIP(TSNAME), EARESS) > 0
WHERE EYRESS = 5000 AND ESRESS= 0 AND STAGE IN ('UL','DL')
)
SELECT TB,EARESS DSN,PARTNR,STATUS,STAGE
FROM T021
WHERE OBJECTFAMILY = 'exab.form.akt.2 '
AND 2012 BETWEEN YEAR(LOGPRODFROM) AND YEAR(LOGPRODTO)
AND SEGID = '001'
UNION ALL SELECT TB,DSN,PARTNR,STATUS,STAGE
FROM TB_IC
WHERE OBJECTFAMILY = 'exab.form.akt.2 '
AND 2012 BETWEEN YEAR(LOGPRODFROM) AND YEAR(LOGPRODTO)
AND SEGID = '001'
ORDER BY 3,1 DESC WITH UR
--- ohne t091 ==>
WITH DSN_INLINE_OPT_HINT
(CORRELATION_NAME,ACCESS_TYPE,ACCESS_NAME,JOIN_SEQ,JOIN_METHOD) as
(
VALUES ('T003', 'INDEX', NULL, 1, NULL) ,
-- ('T091', 'INDEX', NULL, 2, 'NLJ'),
('SYST', 'INDEX', NULL, 3, 'NLJ'),
('SYSC', 'INDEX', NULL, 4, 'NLJ'),
('C21A', 'INDEX', NULL, 6, 'NLJ'),
('C21B', 'INDEX', 'IXBC021U' ,7, 'NLJ' )
)
, TB_IC AS
(
SELECT DSNAME DSN,'BUA' CREATOR,SYST.DBNAME,SYST.TSNAME,
'XB'||STORAGEAREA||SEGMENT||EYKNGKLASS||
EYKNGOCC||ENZEILE||ENSPALTE TB,PARTNUMBER PARTNR,
SEGMENT SEGID,STORAGEAREA,
'XB'||STORAGEAREA||SEGMENT SEG,
T003.STATUS,STAGE,OBJECTFAMILY,LOGPRODFROM,LOGPRODTO
FROM BUA.TXBI003 T003
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME
= 'XB'||STORAGEAREA||SEGMENT||EYKNGKLASS||EYKNGOCC
LEFT JOIN SYSIBM.SYSCOPY SYSC ON SYSC.DBNAME = SYST.DBNAME
AND SYSC.TSNAME = SYST.TSNAME AND PARTNUMBER = SYSC.DSNUM
AND SYSC.ICTYPE = 'F' AND SYSC.OTYPE = 'T'
WHERE STAGE IN ('RW','CL')
AND SYSC.TIMESTAMP = (SELECT MAX(TIMESTAMP)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = SYST.DBNAME
AND TSNAME = SYST.TSNAME
AND PARTNUMBER = DSNUM AND ICTYPE = 'F'
AND OTYPE = 'T'
)
)
, T021 AS
(
SELECT OBJECTFAMILY,EARESS,EADBA,STORAGEAREA,SEGMENT SEGID,
LOGPRODFROM,LOGPRODTO,
T003.PARTNUMBER PARTNR,T003.STATUS,STAGE,
'XB'||STORAGEAREA||SEGMENT||EYKNGKLASS||
EYKNGOCC||ENZEILE||ENSPALTE TB,
tsName
FROM BUA.TXBI003 T003
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME = 'XB'||STORAGEAREA||SEGMENT
||EYKNGKLASS||EYKNGOCC||ENZEILE||ENSPALTE
INNER JOIN BUA.TXBC021 C21A ON
EADBA = 'XB'||STORAGEAREA||SEGMENT
AND T003.PARTNUMBER = C21A.PARTNUMBER
AND LOCATE(STRIP(TSNAME), EARESS) > 0
WHERE EYRESS = 5000 AND ESRESS= 0 AND STAGE IN ('UL','DL')
UNION ALL select
OBJECTFAMILY,EARESS,EADBA,STORAGEAREA,SEGMENT SEGID,LOGPRODFROM,
LOGPRODTO,T003.PARTNUMBER PARTNR,
T003.STATUS,STAGE,
'XB'||STORAGEAREA||SEGMENT||EYKNGKLASS
||EYKNGOCC||ENZEILE||ENSPALTE TB,
TSNAME
FROM BUA.TXBI003 T003
INNER JOIN SYSIBM.SYSTABLES SYST ON SYST.CREATOR = 'BUA'
AND SYST.NAME = 'XB'||STORAGEAREA||SEGMENT
||EYKNGKLASS||EYKNGOCC||ENZEILE||ENSPALTE
INNER JOIN BUA.TXBC021S C21B ON
EADBA = 'XB'||STORAGEAREA||SEGMENT
AND T003.PARTNUMBER = C21B.PARTNUMBER
AND LOCATE(STRIP(TSNAME), EARESS) > 0
WHERE EYRESS = 5000 AND ESRESS= 0 AND STAGE IN ('UL','DL')
)
SELECT TB,EARESS DSN,PARTNR,STATUS,STAGE
FROM T021
WHERE OBJECTFAMILY = 'exab.form.akt.2 '
AND 2012 BETWEEN YEAR(LOGPRODFROM) AND YEAR(LOGPRODTO)
AND SEGID = '001'
UNION ALL SELECT TB,DSN,PARTNR,STATUS,STAGE
FROM TB_IC
WHERE OBJECTFAMILY = 'exab.form.akt.2 '
AND 2012 BETWEEN YEAR(LOGPRODFROM) AND YEAR(LOGPRODTO)
AND SEGID = '001'
ORDER BY 3,1 DESC WITH UR
; X;