zOs/SQL/CONSUM
-- [DBOF]
-- Control Summary SQL für RZ2/DBOF job QMW0008P
-- generiert am 12/07/18 11:06:10
-- durch rz1/dsn.source.tecSv(conSumGe)
-- ||| alle Aenderung dortdrin ||||||
--************************************************************
--?? fehlende IncrCopies Tablespaces, letzte 8 Tage:
--************************************************************
---- begin @proc missFUllcopies1: fehlende Fullcopies -----------------
with p as (
SELECT PT.DBNAME, pt.tsName, pt.partition,
( SELECT char(timestamp) || icType
FROM SYSIBM.SYSCOPY CP
WHERE PT.DBNAME = CP.DBNAME
AND PT.TSNAME = CP.TSNAME
AND cp.dsNum in (PT.PARTITION, 0)
AND CP.ICTYPE IN ('F','R','X')
AND CP.TIMESTAMP > CURRENT TIMESTAMP - 7 days
order by timestamp desc
fetch first 1 row only
) laFull,
r.nActive,
COPYLASTTIME,
COPYUPDATEDPAGES,
COPYCHANGES,
COPYUPDATETIME
---- end @proc selIncrCopy: select fullcopy etc. --------------------
FROM SYSIBM.SYSDATABASE DB
join SYSIBM.SYSTABLESPACE TS
on DB.NAME = PT.DBNAME
join SYSIBM.SYSTABLEPART PT
on DB.NAME = TS.DBNAME
AND TS.NAME = PT.TSNAME
left join SYSIBM.SYSTABLESpaceStats r
on r.dbid = db.dbid
and r.psid = ts.psid
and r.partition = pt.partition
WHERE 0 = 0
---- end @proc missFUllcopies1: fehlende Fullcopies -----------------
---- begin @proc exclude ----------------------------------------------
----- begin @proc exclGen: gemeinsame excludes -------------------------
AND NOT (PT.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (PT.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (PT.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (PT.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (PT.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (PT.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(PT.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (PT.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (PT.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
---- end @proc exclGen: gemeinsame excludes -------------------------
AND NOT (PT.DBNAME = 'XC01A1P' AND PT.TSNAME LIKE 'A2%' )
-- EOS: Armin Breyer
AND NOT (PT.DBNAME = 'XR01A1P' AND PT.TSNAME LIKE 'A2%' )
-- ERET: Armin Breyer
AND NOT (PT.DBNAME = 'CSQDBOF' AND PT.TSNAME like 'TSBLOB%' )
---- end @proc exclude ----------------------------------------------
AND DB.TYPE NOT IN ('T','W')
---- begin @proc missFUllcopies2: fehlende Fullcopies -----------------
AND TS.NTABLES <> 0
AND PT.SPACEF <> -1 -- attention space is sometimes wrong|
AND NOT EXISTS (
---- begin @proc selIncrCopy: select fullcopy etc. --------------------
SELECT ' '
FROM SYSIBM.SYSCOPY CP
WHERE PT.DBNAME = CP.DBNAME
AND PT.TSNAME = CP.TSNAME
AND cp.dsNum in (PT.PARTITION, 0)
AND (( CP.ICTYPE IN ('I', 'F','R','X') -- copy or fullLog
AND CP.TIMESTAMP > CURRENT TIMESTAMP - 2 hours
) or ((CP.ICTYPE = 'C' -- created today
-- part added today
or (CP.ICTYPE = 'A' and CP.sType = 'A')
) and date(cp.timestamp) >= current date
) )
---- end @proc selIncrCopy: select fullcopy etc. --------------------
)
)
, q as
(
select case when laFull < char(current timestamp - 172 hours)
then 'full old'
when copyUpdateTime > current timestamp - 7 hours
then 'no newUpd'
when nActive * 0.1 <= COPYUPDATEDPAGES then 'full upda'
when COPYUPDATEDPAGES <> 0 or copyChanges <> 0 then 'inc'
else 'no changes'
end copy,
p.*
from p
)
select *
from q
where left(copy, 2) <> 'no'
and dbName like 'AU%'
-- r.copyUpdateTime < current timestamp - 2 hours
ORDER BY DBNAME, TSNAME, PARTITION
WITH UR;
---- end @proc missFUllcopies2: fehlende Fullcopies -----------------
;x;
select (select max(char(c.timestamp) || ' ' || char(c.dsNum)
|| icType || sType)
from sysibm.sysCopy c
where c.dbName = p.dbName and c.tsName = p.tsName
and c.dsNum in (0, p.partition)
AND C.ICTYPE IN ('I', 'F','R','X', 'C', 'A')
and (c.icType <> 'A' or c.sType = 'A')
) -- part added today
select (select max(char(c.timestamp) || ' ' || char(c.dsNum)
|| icType || sType)
from sysibm.sysCopy c
where c.dbName = p.dbName and c.tsName = p.tsName
and c.dsNum in (0, p.partition)
AND C.ICTYPE IN ('I', 'F','R','X', 'C', 'A')
and (c.icType <> 'A' or c.sType = 'A')
) -- part added today
from sysibm.sysTablePart p
where dbName = 'MF01A1P' and tsName = 'A150A'
order by partition
with ur;x;
---- end @proc selIncrCopy: select fullcopy etc. --------------------
)
order by r.timestamp desc
fetch first row only
)
;x;
select p.partition, c.*
from sysibm.sysTablePart p
left join sysibm.sysCopy c
on c.dbName = p.dbName and c.tsName = p.tsName
and (c.timestamp, c.dsNum, c.icType) in
(select r.timestamp, r.dsNum, r.icType
from sysibm.sysCopy r
where r.dbName = p.dbName and r.tsName = p.tsName
and r.dsNum in (0, p.partition)
order by r.timestamp desc
fetch first row only
)
where p.dbName = 'MF01A1P' and p.tsName = 'A150A'
order by dbName, tsName, dsNum
;x;
selectcs as (
select dbName, tsName, dsNum
from sysibm.sysCopy c
-- [DBOF]
-- Control Summary SQL für RZ2/DBOF job QMW0008P
-- generiert am 12/07/18 11:06:10
-- durch rz1/dsn.source.tecSv(conSumGe)
-- ||| alle Aenderung dortdrin ||||||
--************************************************************
--?? fehlende IncrCopies Tablespaces, letzte 8 Tage:
--************************************************************
---- begin @proc missFUllcopies1: fehlende Fullcopies -----------------
SELECT PT.DBNAME, pt.tsName, pt.partition,
( SELECT icType || ' ' || char(timestamp)
FROM SYSIBM.SYSCOPY CP
WHERE PT.DBNAME = CP.DBNAME
AND PT.TSNAME = CP.TSNAME
AND cp.dsNum in (PT.PARTITION, 0)
AND CP.ICTYPE IN ('F','R','X')
-- AND CP.TIMESTAMP > CURRENT TIMESTAMP - 7 days
order by timestamp desc
fetch first 1 row only
) laFull
---- end @proc selIncrCopy: select fullcopy etc. --------------------
FROM SYSIBM.SYSDATABASE DB
join SYSIBM.SYSTABLESPACE TS
on DB.NAME = PT.DBNAME
join SYSIBM.SYSTABLEPART PT
on DB.NAME = TS.DBNAME
AND TS.NAME = PT.TSNAME
join SYSIBM.SYSTABLESpaceStats r
on r.dbid = db.dbid
and r.psid = ts.psid
and r.partition = pt.partition
WHERE
r.copyUpdateTime < current timestamp - 2 hours
---- end @proc missFUllcopies1: fehlende Fullcopies -----------------
---- begin @proc exclude ----------------------------------------------
----- begin @proc exclGen: gemeinsame excludes -------------------------
AND NOT (PT.DBNAME like 'DSNDB%') -- DB2 CATALOG
AND NOT (PT.DBNAME LIKE 'DSN8%') -- IBM TEST DB
AND NOT (PT.DBNAME LIKE 'WKDBD%') -- DB2 WORK DATABASE
AND NOT (PT.DBNAME = 'DSNTESQ') -- DB2 CATALOG CLONE
AND NOT (PT.DBNAME LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT (PT.DBNAME LIKE 'DB2PLAN%') -- explain tables
and not translate(PT.dbName, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT (PT.DBNAME LIKE 'DB2ALA%') -- marec generated
AND NOT (PT.DBNAME LIKE '%MAREC%') -- marec generated
AND NOT (PT.DBNAME LIKE 'DACME%') -- Mail Heinz Bühler
---- end @proc exclGen: gemeinsame excludes -------------------------
AND NOT (PT.DBNAME = 'XC01A1P' AND PT.TSNAME LIKE 'A2%' )
-- EOS: Armin Breyer
AND NOT (PT.DBNAME = 'XR01A1P' AND PT.TSNAME LIKE 'A2%' )
-- ERET: Armin Breyer
AND NOT (PT.DBNAME = 'CSQDBOF' AND PT.TSNAME like 'TSBLOB%' )
---- end @proc exclude ----------------------------------------------
AND DB.TYPE NOT IN ('T','W')
---- begin @proc missFUllcopies2: fehlende Fullcopies -----------------
AND TS.NTABLES <> 0
AND PT.SPACEF <> -1 -- attention space is sometimes wrong|
AND NOT EXISTS (
---- begin @proc selIncrCopy: select fullcopy etc. --------------------
SELECT ' '
FROM SYSIBM.SYSCOPY CP
WHERE PT.DBNAME = CP.DBNAME
AND PT.TSNAME = CP.TSNAME
AND cp.dsNum in (PT.PARTITION, 0)
AND (( CP.ICTYPE IN ('I', 'F','R','X') -- copy or fullLog
AND CP.TIMESTAMP > CURRENT TIMESTAMP - 2 hours
) or ((CP.ICTYPE = 'C' -- created today
-- part added today
or (CP.ICTYPE = 'A' and CP.sType = 'A')
) and date(cp.timestamp) >= current date
) )
---- end @proc selIncrCopy: select fullcopy etc. --------------------
)
ORDER BY DBNAME, TSNAME, PT.PARTITION
WITH UR;
---- end @proc missFUllcopies2: fehlende Fullcopies -----------------
;x;
select (select max(char(c.timestamp) || ' ' || char(c.dsNum)
|| icType || sType)
from sysibm.sysCopy c
where c.dbName = p.dbName and c.tsName = p.tsName
and c.dsNum in (0, p.partition)
AND C.ICTYPE IN ('I', 'F','R','X', 'C', 'A')
and (c.icType <> 'A' or c.sType = 'A')
) -- part added today
select (select max(char(c.timestamp) || ' ' || char(c.dsNum)
|| icType || sType)
from sysibm.sysCopy c
where c.dbName = p.dbName and c.tsName = p.tsName
and c.dsNum in (0, p.partition)
AND C.ICTYPE IN ('I', 'F','R','X', 'C', 'A')
and (c.icType <> 'A' or c.sType = 'A')
) -- part added today
from sysibm.sysTablePart p
where dbName = 'MF01A1P' and tsName = 'A150A'
order by partition
with ur;x;
---- end @proc selIncrCopy: select fullcopy etc. --------------------
)
order by r.timestamp desc
fetch first row only
)
;x;
select p.partition, c.*
from sysibm.sysTablePart p
left join sysibm.sysCopy c
on c.dbName = p.dbName and c.tsName = p.tsName
and (c.timestamp, c.dsNum, c.icType) in
(select r.timestamp, r.dsNum, r.icType
from sysibm.sysCopy r
where r.dbName = p.dbName and r.tsName = p.tsName
and r.dsNum in (0, p.partition)
order by r.timestamp desc
fetch first row only
)
where p.dbName = 'MF01A1P' and p.tsName = 'A150A'
order by dbName, tsName, dsNum
;x;
selectcs as (
select dbName, tsName, dsNum
from sysibm.sysCopy c