zOs/SQL/CATCOPRE
with p as
(
select dbName, tsName, partition,
( select max(f.timestamp) from sysibm.sysCopy f
where p.dbName = f.dbName and p.tsName = f.tsName
and f.dsNum in (p.partition, 0)
and f.icType in ('F', 'R','X')
) laFu
from sysibm.sysTablePart p
where dbName like 'MF01%'
)
select substr(strip(p.dbName) || '.' || strip(p.tsName)
|| ':' || right(' ' || strip(char(partition)), 4), 1, 22),
laFu, c.icType, c.dsNum, c.Timestamp,
c.dsName, c.*
from p left join sysibm.sysCopy c
on p.dbName = c.dbName and p.tsName = c.tsName
and c.dsNum in (p.partition, 0)
and c.icType in ('F', 'I', 'R','X')
and c.timestamp >= laFu
order by p.dbName, p.tsName, p.partition, c.timestamp desc
;;;;
AND (( CP.ICTYPE IN ('F','R','X') -- fullcopy or fullLog
AND CP.TIMESTAMP > CURRENT TIMESTAMP - $-{days} DAYS
) or ((CP.ICTYPE = 'C' -- created today
-- part added today
or (CP.ICTYPE = 'A' and CP.sType = 'A')
) and date(cp.timestamp) >= current date
) )
(
select case when strip(min(dbName)) like '_*' and min(dbName) > 'A*'
then left(min(dbName), 1) else ''
end fr,
case when strip(max(dbName)) like '_*'
then left(max(dbName), 1) else ''
end || x'FFFF' to
FROM DLC.OBJECTS_V13
WHERE EXCLUDE='I' AND NAME='QDDBOF INCL EXCLUDES'
)
, p as
$] else $@=[
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')
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
$]
if $ptaInc then $@=[
join frTo
on db.name >= frTo.fr and db.name <= frTo.to
$]
$@=[
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
AND NOT (PT.DBNAME LIKE 'DGDB%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE 'DGO%') -- PROTOTYPEN
AND NOT (PT.DBNAME LIKE '%A1X%') -- Neue Prototypen
AND NOT (PT.DBNAME LIKE 'DAU%') -- Schulung Gerrit
AND NOT (PT.DBNAME LIKE 'IDT%') -- ibm tools
---- end @proc exclGen: gemeinsame excludes ------------------------
AND NOT (PT.DBNAME LIKE 'OE02%') -- Mail Ivo Eichmann
AND NOT (PT.DBNAME LIKE 'CSQ%') -- M-QUEUE DATENBANK
---- end @proc exclude ---------------------------------------------
---- 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 db.Name like 'WI02%' --- ????
)
, q as
(
select case when laFull < char(current timestamp - $-[168+$hh$] hours)
then 'full old'
when copyUpdateTime > current timestamp - $hh 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'
ORDER BY DBNAME, TSNAME, PARTITION
WITH UR
$]
$/sql/
call sqlSel
m.inc.0 = 0
m.ful.0 = 0
cAll = 0
pAll = 0
$| $@forWith c $@[
cAll = cAll + 1
if datatype($NACTIVE, 'n') then
pAll = pAll + $NACTIVE
say left($COPY, 10) left($DBNAME, 8) left($TSNAME, 8) ,
right($PARTITION, 5) left($LAFULL, 30)
say right($COPYCHANGES , 12),
|| right('>'$COPYUPDATEDPAGES, 10),
|| right('%'$NACTIVE, 10) ,
left($COPYLASTTIME, 19),
left($COPYUPDATETIME, 19)
if cAll <= $partLim then
call mAdd if(abbrev($COPY, 'inc'), inc, ful),
, ' INCLUDE TABLESPACE' strip($DBNAME)'.'strip($TSNAME),
'PARTLEVEL' if($PARTITION <> 0, $PARTITION)
$]
$;
say 'total' cAll 'parts and' pAll 'pages'
say ' ' m.inc.0 'incremental and' m.ful.0 'full part copies'
$;
$>DSN.BESENWAG.$dbSy(GENINC)
$@makeList-{INC, FULL NO, 'incremental', cAll, pAll}
$;
$>DSN.BESENWAG.$dbSy(GENFUL)
$@makeList-{FUL, FULL YES, 'full' , cAll, pAll}
$;
$@proc makeList $@/makeList/
parse arg ,lst, full, tit, cAll, pAll
$** say 'lst' lst 'full' full 'tit' tit 'cAll' cAll 'pAll' pAll
$$- '--' sysvar('sysnode') $dbSy date('s') time()
$$- '-- total : ' cAll 'parts' pAll 'pages'
$$- '--' left(tit, 11) 'copy: ' m.lst.0 'parts'
if $previewOnly then
$$ OPTIONS(PREVIEW)
else
$$ OPTIONS EVENT(ITEMERROR,SKIP)
if m.lst.0 > 0 then $@=[
$$- ' LISTDEF LST'lst ' -- ' m.lst.0 'parts'
$@do ix=1 to m.lst.0 $$- m.lst.ix
COPY LIST LST$-{lst} COPYDDN(TCOPYD)
PARALLEL $-{full}
SHRLEVEL CHANGE
$]
$/makeList/
//************************************* copy ***************************
// IF (ABEND OR NOT ABEND) THEN
//COPYINC EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=(DBTF,'QCSBESTP.COPYINC')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=DBTF.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.DBTF(GENINC)
// ENDIF
//************************************* copy ***************************
// IF (ABEND OR NOT ABEND) THEN
//COPYFUL EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=(DBTF,'QCSBESTP.COPYFUL')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=DBTF.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.DBTF(GENFUL)
// ENDIF
//*
//************************************* send mail if error *************
// IF (ABEND OR RC GT 7 OR RC LT 0) THEN
//EMAIL EXEC PGM=OS3550
//STEPLIB DD DISP=SHR,DSN=MQM.QP00
//SYSPRINT DD SYSOUT=*
//IEMLOG DD SYSOUT=*
//IEMAPI DD *
SUBJECT RZ1.DBTF job qcsBesTp
AUTHOR GGDB2
RECPLIST GGDB2
DOCTEXT
meldung vom technischen saveablauf - BesenWagen
Im Job qcsBesTp konnte mindestens 1 Objekt nicht kopiert werden.
Zur Analyse im sysprint einen find auf "skip" oder "dsnu1027i" absetzen
DOCTEXTEND
// ENDIF
//*
//************************************* create member to mark finish ***
// IF (ABEND OR NOT ABEND) THEN
//FINISH EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD *
job qcsBesTp finished
//SYSUT2 DD DISP=SHR,DSN=DSN.BESENWAG.DBTF(FINISH)
// ENDIF