zOs/SQL/TECSVCOP
/* #HCCD (TS) RTS full IMAGE COPY
-- tecSv full copy
-- FICDDBOF für RZ2/DBOF für Job QGS400FP
-- generiert als FICD#BF um 2015-12-17-12.48.51
-- durch rz4/dsn.source.tecSv(conSumGe) >>> alle Aenderung da <<<
SELECT 'DI,PI,PA,IN' , DBID , PSID , PA , INST */
/* tecsvCop sql: what copy is needed? full, incremental or none
18.12.15 walter: part=0 wieder eingebaut, inc raus
ignore icType T (term util) and J (compr Dict)
*/
select substr(dbName, 1, 8) db
, substr(name, 1, 8) ts
, pa
, substr(inst || ' ' || insTxt, 1, 7) instanc
, what
, basTy b
, basPa
, basTst
from
( -- r: why and how to copy, join sysTableSpaceStats
select q.*
, overlay(case
when inst is null
then raise_Error(70001, 'inst null '
|| q.dbName || '.' || q.name)
when nTables < 1 then 'n noTables ' || nTables
-- let utility figure out define no or yes
-- but dbAnalyzer always produces RTS not found messages
-- ==> unfortunately not a good idea |
when pSpace = -1 then 'n defineNo ' || pSpace
when basTy <> 'F' then 'f basIcType ' || basTy
when basPa <> pa then 'f multiPart'
when basTst < current timestamp-7 days then 'f week'
when r.updateStatsTime is null then 'f noRts'
when r.copyLastTime is null then 'f r.copyLast null'
when r.nactive * 0.1 <= r.copyupdatedpages
then 'f updates'
/* when incTst < r.copyLastTime - 60 seconds
then 'f i << r.copyLast'
when incTy not in ('I','F') then 'i incIcType ' || incTy */
when r.copyupdatedpages <> 0 then 'i updates'
when r.copyChanges <> 0 then 'i changes'
when r.copyUpdateLRSN is not null then 'i updLRSN'
when r.copyUpdateTime is not null then 'i updTime'
else 'n noUpdates'
end, left(' ' || insTxt, 6), 2, 0, octets) what
from
( -- q decode bas and inc fields
select p.*
, timestamp(substr(bas, 1, 26)) basTst
, substr(bas, 27, 1) basTy
, smallint(substr(bas, 28)) basPa
/* , timestamp(substr(inc, 1, 26)) incTst
, substr(inc, 27, 1) incTy
, smallint(substr(inc, 28)) incPa */
from
( -- p tablespace, instance, tablePart
select s.*
, p.partition pa
, p.space pSpace
, max(value(s.bas0, ''), value(
( select char(timestamp) || icType || dsNum
from sysibm.sysCopy c
where s.dbName = c.dbName and s.name = c.tsName
and p.partition = c.dsNum and p.partition > 0
and s.inst = c.instance
and c.icType not
IN ('A', 'B', 'C', 'D', 'I', 'J', 'M', 'Q', 'T')
order by c.timestamp desc
fetch first 1 rows only
) , ''), '1111-11-11-11.11.11.111111-0' ) bas
/* , max(value(s.inc0, ''), value(
( select char(timestamp) || icType || dsNum
from sysibm.sysCopy c
where s.dbName = c.dbName and s.name = c.tsName
and p.partition = c.dsNum and p.partition > 0
and s.inst = c.instance
and c.icType not
IN ('A', 'B', 'C', 'D', 'J', 'M', 'Q', 'T')
order by c.timestamp desc
fetch first 1 rows only
) , ''), '1111-11-11-11.11.11.111111-0' ) inc */
from
( -- s tablespace and instance
select dbName, name, partitions parts
, dbId, psId, nTables
, i.inst
, case when s.clone <> 'Y' then ' '
when s.instance = i.inst then 'base '
else 'clone' end insTxt
, ( select char(timestamp) || icType || dsNum
from sysibm.sysCopy c
where s.dbName = c.dbName and s.Name = c.tsName
and 0 = c.dsNum and i.inst = c.instance
and c.icType not
IN ('A', 'B', 'C', 'D', 'I', 'J', 'M', 'Q', 'T')
order by c.timestamp desc
fetch first 1 rows only
) bas0
/* , ( select char(timestamp) || icType || dsNum
from sysibm.sysCopy c
where s.dbName = c.dbName and s.Name = c.tsName
and 0 = c.dsNum and i.inst = c.instance
and c.icType not
IN ('A', 'B', 'C', 'D', 'J', 'M', 'Q', 'T')
order by c.timestamp desc
fetch first 1 rows only
) inc0 */
from sysibm.sysTablespace s
left join -- clone handling: add instances
( select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
) i (inst)
on s.instance = i.inst or s.clone = 'Y'
) s
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
) p
) q
left join sysibm.sysTableSpaceStats r
on q.dbName = r.dbName and q.name = r.name
and q.dbid = r.dbid and q.psid = r.psid
and q.pa = r.partition and q.inst = r.instance
) r
where what like '$tsF%%' -- doppelte % fuer ca dbAnalyser
and
----- begin @proc exclude: excludes --- S --------------------------
NOT (dbName LIKE 'WKDB%%') -- DB2 WORK DATABASE
AND NOT (dbName LIKE '%%MAREC%%') -- marec generated
AND NOT (dbName LIKE 'DACME%%') -- Mail Heinz Bühler
AND NOT (dbName LIKE 'QZ91%%') -- test klem 43
AND NOT (dbName LIKE 'QZ92%%') -- test klem 43
and not translate(dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (dbName LIKE 'DB2ALA%%') -- marec generated
AND NOT (dbName LIKE 'DB2POOL%%') -- DB2 STOR.POOL WIESI
AND NOT (dbName LIKE 'DB2MAPP%%') -- REORG MAPPING TABLES
AND NOT (dbName LIKE 'DB2PLAN%%' -- explain tables
and translate(left(name, 7), '999999999AA', '012345678FG')
= 'A999999' -- user explain tables
)
AND NOT (dbName like 'DSNDB%%') -- DB2 CATALOG
AND NOT (dbName LIKE 'DSN8%%') -- IBM TEST DB
AND NOT (dbName = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (dbName like 'CSQ%%' AND name like 'TSBLOB%%' )
-- M-QUEUE DATENBANK
AND NOT (dbName = 'XC01A1P' and name <> 'A500A'
and (name LIKE 'A2%%'or name LIKE 'A5%%'))
-- EOS: Armin Breyer
AND NOT (dbName = 'XR01A1P' AND name LIKE 'A2%%' )
-- ERET: Armin Breyer
AND NOT
(dbName = 'BE01A1P' and name like 'A0%%' -- BE save
or dbName = 'CD02A1P' and name = 'A600A')
----- end @proc exclude: excludes --- S --------------------------
and dbName = 'QZ01A1P' and name like 'A%' --??
order by dbName, name, pa, inst
;