zOs/SQL/XBUNLDEC
--- ndbs: neuer Elar Design seit 2013/14 ------------------------------
--- global table fuer Partitionen, stage, segment ---------------------
declare global temporary table session.ndPa
( 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%'
;
--- counts fuer ndbs --------------------------------------------------
select count(*) "ndbs Parts"
, count(distinct db || '.' || ts) "ndbs TS's"
, count(distinct db ) "ndbs DB's"
from session.ndPa
;
--$$ XB - ndbs: falscher stage in TXBI003
select *
from session.ndPa
where stage not in ('RW', 'CL', 'UL', 'DL')
order by db, ts, pa
;
--- temporary table fuer syscopy -------------------------------------
declare global temporary table session.copy
( db char(8), ts char(8), paFr smallInt, paTo smallInt
, dsNum smallInt, icType char(1), tst timestamp
) on commit preserve rows;
create unique index session.txIx on session.copy (db,ts, paFr, paTo)
include (dsNum, icType, tst)
;
insert into session.copy
with l as
(
select c.dbName db, c.tsName ts, c.dsNum, c.icType, c.timestamp tst
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.lowDsNum
end paFr
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.highDsNum
end paTo
from sysibm.sysCopy c
join sysibm.sysTableSpace s
on c.dbName = s.dbName and c.tsName = s.name
where ICTYPE IN ('C', 'F', 'S', 'W', 'Y')
or (ICTYPE = 'A' and sType = 'A') -- part added
)
, g as
(
select db, ts, paFr, paTo
, max(char(tst) || ictype || dsNum) last
from l
group by db, ts, paFr, paTo
)
select db, ts, paFr, paTo
, smallInt(substr(last, 28)) dsNum
, substr(last, 27, 1) icType
, timestamp(substr(last, 1, 26)) tst
from g
;
select count(*) "copy count"
, count(distinct db || '.' || ts) "copy TS's"
, count(distinct db ) "copy DB's"
from session.copy
;
--$$ XB - fehlende fullcopies / recoverybase
with l as
(
select p.*
, ( select max(char(tst) || icType || char(dsNum))
from session.copy c
where p.db = c.db and p.ts = c.ts
and (p.pa between c.paFr and c.paTo
or c.paFr = 0)
) last
from session.ndPa p
where stage not in ('UL', 'DL')
)
, m as
(
select l.*
, substr(last, 27, 1) ty
, smallint(substr(last, 28)) dsNum
, timestamp(substr(last, 1, 26)) tst
from l
)
select db, ts, pa, stage, ty, dsNum, tst
from m
where ty is null or not
((ty = 'F' and tst > current timestamp - 172 hours)
or (ty in ('C', 'A') and tst > current timestamp - 24 hours))
order by 1, 2, 3
with ur
;
?????
select *
FROM SYSIBM.SYSCOPY
WHERE LOWDSNUM = 58
with ur
;;;
select count(*), icType, dsNum, LOWdsNum, HIGHdsNum
FROM SYSIBM.SYSCOPY
WHERE LOWdsNum <> DSNUM OR HIGHdsNum <> DSNUM
GROUP BY icType, dsNum, LOWdsNum, HIGHdsNum
ORDER by 1 desc
with ur
;;;;
; x;
select count(*)
from sysibm.sysTables t
where t.dbName in (select db from session.ts)
;
select count(*)
from sysibm.sysTables t
where t.dbName in (select db from session.ts)
and not exists (select 1 from session.ts x
where t.dbName = x.db and t.tsName = x.ts)
;
select char(creator, 8), char(name, 20)
, char(dbName, 8), char(tsName, 8)
from sysibm.sysTables t
where t.dbName in (select db from session.ts)
and not exists (select 1 from session.ts x
where t.dbName = x.db and t.tsName = x.ts)
fetch first 100 rows only
;
select sum(case when p.dbName is not null and x.db is not null
then 1 else 0 end) both
, sum(case when p.dbName is not null and x.db is null
then 1 else 0 end) sysOnly
, sum(case when p.dbName is null and x.db is not null
then 1 else 0 end) xOnly
from (select * from sysibm.sysTablePart
where (dbName, tsName) in
(select db, ts from session.ts) ) p
full join session.ts x
on p.dbName = x.db and p.tsName = x.ts and p.partition = x.pa
;
select db, ts, pa
from (select * from sysibm.sysTablePart
where (dbName, tsName) in
(select db, ts from session.ts) ) p
full join session.ts x
on p.dbName = x.db and p.tsName = x.ts and p.partition = x.pa
where p.dbName is null
fetch first 100 rows only
;
select dbName, tsName, partition
from (select * from sysibm.sysTablePart
where (dbName, tsName) in
(select db, ts from session.ts) ) p
full join session.ts x
on p.dbName = x.db and p.tsName = x.ts and p.partition = x.pa
where x.db is null
fetch first 100 rows only
;x;
and not exists (select 1 from session.ts x
where s.dbName = x.db and s.Name = x.ts)
;
;x;
declare global temporary table session.unl
( db char(8), ts char(8), pa smallint, unl char(44)
) on commit preserve rows;
create unique index session.unlIx on session.unl (db,ts, pa)
include (unl)
;
insert into session.unl
select substr(earess, 4, 8), substr(earess, 13, 8), partNumber
, case when earess not like 'XB.XB%' then
raise_error(77001, 'bad eaRess % ' || earess)
when locate('.', earess, 4) <> 12 then
raise_error(77002, 'bad eaRess 2. . ' || earess)
when locate('.', earess, 13) <> 21 then
raise_error(77002, 'bad eaRess 3. . ' || earess)
else eaRess
end
from BUA.TXBC021 t
where EYRESS = 5000
and ESRESS = 0
with cs
;
commit
;
insert into session.unl
select substr(earess, 4, 8), substr(earess, 13, 8), partNumber
, case when earess not like 'XB.XB%' then
raise_error(77001, 'bad eaRess % ' || earess)
when locate('.', earess, 4) <> 12 then
raise_error(77002, 'bad eaRess 2. . ' || earess)
when locate('.', earess, 13) <> 21 then
raise_error(77002, 'bad eaRess 3. . ' || earess)
else eaRess
end
from BUA.TXBC021s t
where EYRESS = 5000
and ESRESS = 0
with cs
;
commit
;
select count(*) from session.unl
;
select current timestamp, current server from sysibm.sysDummy1;
-- elar: stage und unload file name
-- für XB Tabellen Partitionen
--
-- Achtung: Performance ist sehr heikel
-- diese Version braucht meist weniger als 1 min
-- im Gegensatz zu vielen anderen Versuchen|
--
with p as
( select t.creator cr, t.name tb, t.dbName db, t.tsName ts
, p.partition pa, r.stage, r.status
,'XB' || R.STORAGEAREA || R.SEGMENT seg
, R.objectFamily family
, R.STORAGEAREA
, case when r.stage is null then 'no TXBI003'
when r.stage = 'UL' then 'unlo'
else 'copy' end flag1
FROM sysibm.systables t
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join BUA.TXBI003 R
on t.creator = 'BUA'
and t.name like 'XB%'
and substr(t.name, 3, 3) = r.storageArea
and substr(t.name, 6, 3) = r.segment
and p.partition = r.partNumber
)
, r as
(
select p.*
, case when flag1 = 'unlo' then
(
select unl from session.unl u
where p.db = u.db and p.ts = u.ts and p.pa = u.pa
)
when flag1 = 'copy' then
( select dsName
from sysibm.sysCopy c
where c.dbName = p.db and c.tsName = p.ts
and p.pa = c.dsNum and icType = 'F'
and timestamp > current timestamp - 10 days
order by timestamp desc
fetch first 1 row only
)
else null
end ucDsn
from p
)
, o as
(
select case when flag1 like 'no %' then flag1
when ucDsn is null then char('no ' || flag1, 7)
when S100447.DSLOCATE(ucDsn) IS NULL
then char('noCat ' || flag1, 10)
else char(flag1 || ' ok', 7)
end flag2
, r.*
from r
-- fetch first 10000 rows only
)
select flag2, count(*)
from o
group by flag2
with ur
;
select current timestamp, current server from sysibm.sysDummy1;
;XX
select count(*) , min(eaRess), max(earess)
from BUA.TXBC021 t
where EYRESS = 5000
and ESRESS = 0
and earess like '%WWW%'
;x;
union all select t.*
from BUA.TXBC021 t
where EYRESS = 5000
and ESRESS = 0