zOs/REXX/QCSBESXP
$#@
$=distTst = 0 $** 1 distribute to oLib, 0 to dsn.besenWag...
$=oLib=- userid()'.tst.tecsv'
$=oLib= DSN.SOURCE.TECSV.GEN
$<>
$>. fEdit($-oLib'(##besenw) ::f', 'v')
$$ $'$#@'
$$ $'$** distribute qcsBesXP'
$** $@%[oneJob rz1 dboc$]
$@%[oneJob rzz de0g$]
$@%[oneJob rzz devg$]
$@%[oneJob rzz dpzg$]
$@%[oneJob rr2 dbof$]
$@%[oneJob rq2 dbof$]
$@%[oneJob rz2 dbof$]
$@%[oneJob rz4 dp4g$]
$*( history
19. 4.16 auch txc52* exclud't
27.10.15 mit term step und neuem mail
9. 3.15 neue Syntax, mit plexChar, lctl(QZT00*) entfernt ==> conSumGe
19.12.14 nur user explain tables excluden
3.12.14 mit RQ2
27.11.14 mit defineNo auf space statt spaceF
18.9.14 mit icType=R/Z --> fullCopy, AC04 excluded
$*)
$proc oneJob $@/oneJob/
parse upper arg , rz dbSys
$= rz =- rz
$= rzD =- iiRz2Dsn(rz)
$= dbSys =- dbSys
$= pd =- iiRz2P(rz)iiDbSys2C(dbSys)
$= job = qcsBe${pd}P
$= JOB =- translate($job)
$= hh =- if(dbSys='DBOF', 5, 3)
$= tst =- f('%t s')
$=partLim=- if(rz=='RR2' | rz='RQ2', 500, 999999)
$$ call dsnCopy '$oLib($job)' ,
if $distTst then
$$ $' ' , '$rz/$oLib($job)'
else
$$ $' ' , '$rz/dsn.besenWag.$dbSys(qcsBesXP)'
$<>
$>$oLib($job)
$@=/oneJob1/
//$JOB JOB (ADM27506,0241,,3628),'DB2 TECSV BESENWAGEN',
// MSGCLASS=E,CLASS=P2,TIME=1440,SCHENV=DB2ALL
//*********************************************************************
//* tecSV der DB2 Tabellen - Besenwagen $rz/$dbSys
//* version vom 19. 4.16 auch txc52* exclud't
//* generiert am $tst
//* durch rz4/dsn.source.tecsv(qcsBesXP)
//* ||| alle Aenderung dortDrin ||||||||||||
//* hh = $hh (Stunden zurück)
//* partLim = $partLim (maximale Part Copies pro Typ)
//************************************* generate copy statements *******
//GEN EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,
// PARM='WSH'
//SYSPROC DD DSN=DSN.DB2.EXEC,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
//WSH DD *
$'$#@'
$'$= dbSys =' $dbSys
$'$= rz =' $rz
$'$= hh =' $hh
$'$= partLim =' $partLim
$/oneJob1/
$@#/oneJob2/
$=previewOnly=0
call sqlConnect $dbSys
$;
$<=/sql/
$/oneJob2/
$@=/oneJobSql/
$= vh = $'$hh'
with cx as
(
select dbName db, tsName ts, dsNum part, instance
, max(case when ICTYPE not IN ('I')
then char(timestamp) || icType || strip(dsNum)
else '' end) laFu
, max(char(timestamp) || icType || strip(dsNum)) laInc
, max(case when timestamp < current timestamp - $vh hours then ''
when icType in ('I', 'F') then 'new' || icType
else '' end) newCo
from sysibm.sysCopy
where ICTYPE not IN ('A', 'B', 'C', 'D', 'M', 'Q')
group by dbName, tsName, dsNum, instance
)
, ci(i) as
( select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, p as
(
SELECT PT.DBNAME, pt.tsName, pt.partition, ci.i, ts.clone, ts.instance,
pt.space ptSpace,
case when ts.instance = i then 'base' else 'clone' end baCl,
value(max(c1.laFu, c0.laFu) , c1.laFu, c0.laFu, '') laFu,
value(max(c1.laInc, c0.laInc), c1.laInc, c0.laInc, '') laInc,
value( c1.newCo, c0.newCo, '') newCo,
r.nActive,
updateStatstime ,
loadrLasttime ,
reorgLasttime ,
copyLasttime,
copyUpdatedPages,
copyChanges,
copyUpdateTime,
copyUpdateLRSN
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 ci on ci.i=ts.instance or ts.clone = 'Y'
left join cx c1 on c1.db = pt.dbName and c1.ts = pt.tsName
and c1.part = pt.partition and c1.instance = ci.i
and c1.part <> 0
left join cx c0 on c0.db = pt.dbName and c0.ts = pt.tsName
and c0.part = 0 and c0.instance = ci.i
left join SYSIBM.SYSTABLESpaceStats r
on r.dbid = db.dbid
and r.psid = ts.psid
and r.partition = pt.partition
and r.instance = ci.i
WHERE 0 = 0
----- exludes ----------------------------------------------------------
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 translate(left(pt.tsName, 7), '999999999AA', '012345678FG')
= 'A999999') -- user 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
AND NOT (PT.DBNAME LIKE 'OE02%') -- Mail Ivo Eichmann
AND NOT (PT.DBNAME LIKE 'CSQ%' -- M-QUEUE DATENBANK
AND PT.TSNAME like 'TSBLOB%' )
$@[
if wordPos($dbSys, 'DBOF DE0G') > 0 then $@=[
and not
( (PT.dbName = 'XC01A1P' and PT.tsName <> 'A500A'
and (PT.tsName LIKE 'A2%'or PT.tsName LIKE 'A5%'))
-- EOS: Armin Breyer
or (PT.dbName = 'XR01A1P' AND PT.tsName LIKE 'A2%' )
) -- ERET: Armin Breyer
$] else if wordPos($dbSys, 'DVBP DEVG') > 0 then $@=[
AND PT.DBNAME not like 'XB%' -- elar macht saves selbst
$] else if wordPos($dbSys, 'DBOC DP4G') > 0 then $@=[
AND PT.DBNAME not in ('AC04A1P' -- ACF2 macht saves selbst
, 'DB2PDB','DB2PDB2', 'DB2PDB3') -- performance DB
AND NOT (PT.DBNAME like 'DSN%')
$]
if wordPos($rz, 'RZX RZY RZZ') > 0 then $@=[
AND NOT (PT.DBNAME LIKE 'OE02%') -- Mail Ivo Eichmann
AND NOT (PT.DBNAME LIKE 'CSQ%') -- M-QUEUE DATENBANK
$]
$]
AND DB.TYPE NOT IN ('T','W')
AND TS.NTABLES <> 0
)
, q as
(
select case when ptSpace = -1 then 'no defineNo'
when laFu is null then 'full null'
when substr(laFu, 27, 1) <> 'F' then 'full icType'
when laFu < char(current timestamp $*+
- $'$-[168+'$vh$'$]' hours)
then 'full week'
when copyLasttime is null then 'full rtsCo'
when copyLasttime < loadrLastTime then 'full rtsLo'
when copyLasttime < reorgLastTime then 'full rtsRe'
when copyUpdateTime <= current timestamp - $vh hours
and nActive * 0.1 <= COPYUPDATEDPAGES then 'full upda'
when substr(laInc, 27, 1) not in('F','I') then 'inc icType'
when copyUpdateTime > current timestamp - $vh hours
then 'no updTime'
when COPYUPDATEDPAGES <> 0 then 'inc updPag'
when copyChanges <> 0 then 'inc updCha'
when copyUpdateTime is not null then 'inc updTim'
when copyUpdateLRSN is not null then 'inc updLrs'
else 'no changes'
end copy,
p.*
from p
)
select *
from q
where left(copy, 2) <> 'no'
ORDER BY DBNAME, TSNAME, PARTITION, i
WITH UR
$/oneJobSql/
$@#/oneJob3/
$/sql/
call sqlSel
m.sum.NBF = 0 0 0
m.sum.NBI = 0 0 0
m.sum.YBF = 0 0 0
m.sum.YBI = 0 0 0
m.sum.YCF = 0 0 0
m.sum.YCI = 0 0 0
m.NBF.0 = 0
m.NBI.0 = 0
m.YBF.0 = 0
m.YBI.0 = 0
m.YCF.0 = 0
m.YCI.0 = 0
cAll = 0
$| $@forWith c $@[
cAll = cAll + 1
kk = translate($CLONE || left($BACL, 1) || left($COPY, 1))
say left($COPY $NEWCO, 15) left($DBNAME, 8) left($TSNAME, 8) ,
right($PARTITION, 5) 'clone' $CLONE $BACL right($INSTANCE, 2) ,
'rtsUpdT' $UPDATESTATSTIME
say ' fu' left($LAFU, 32) 'inc' left($LAINC, 32) kk
say ' rts chag' strip($COPYCHANGES),
'upPg' strip($COPYUPDATEDPAGES),
'acPg' strip($NACTIVE),
'coUp' $COPYUPDATETIME,
'coLa' $COPYLASTTIME
if datatype($NACTIVE, 'n') then
nn = word(m.sum.kk, 1) + $NACTIVE
else
nn = word(m.sum.kk, 1)
if datatype($COPYUPDATEDPAGES, 'n') then
nn = nn (word(m.sum.kk, 2) + $COPYUPDATEDPAGES)
else
nn = nn word(m.sum.kk, 2)
m.sum.kk = nn
if wordPos(strip(kk),'NBF NBI YBF YBI YCF YCI') < 1 then
call err 'not supported kk='kk
if m.kk.0 <= $partLim then
call mAdd kk,
, ' INCLUDE TABLESPACE' strip($DBNAME)'.'strip($TSNAME),
'PARTLEVEL' if($PARTITION <> 0, $PARTITION)
$]
$<>
$>DSN.BESENWAG.$dbSys(GENINC)
$@%[makeList - NBI, FULL NO, 'not cloned', YBI, 'cloned base'$]
$<>
$>DSN.BESENWAG.$dbSys(GENFUL)
$@%[makeList - NBF, FULL YES, 'not cloned',YBF, 'cloned base'$]
$<>
$>DSN.BESENWAG.$dbSys(GENCLINC)
$@%[makeList - YCI, FULL NO CLONE, 'cloned clone'$]
$<>
$>DSN.BESENWAG.$dbSys(GENCLFUL)
$@%[makeList - YCF, FULL YES CLONE, 'cloned clone'$]
$<>
$@proc makeList $@/makeList/
parse arg ,lst, full, tit, l2, t2
tfu = if(substr(lst, 3, 1)=='I', 'incremental', 'full')
$$- '--' sysvar('sysnode') $dbSys date('s') time()
$$- '--' left(tit tfu, 30) 'copy: ' m.lst.0 'parts'
say left(tit tfu, 30) right(m.lst.0, 10) right(word(m.sum.lst, 1), 14),
right(word(m.sum.lst, 2), 14)
if m.lst.0 > 0 | m.l2.0 > 0 then $@[
if $previewOnly then
$$ OPTIONS(PREVIEW)
else
$$ OPTIONS EVENT(ITEMERROR,SKIP)
$$- ' LISTDEF LST'lst
$]
$@do ix=1 to m.lst.0 $$- m.lst.ix
if l2 \== '' then $@[
say left(t2 tfu, 30) right(m.l2.0, 10) right(word(m.sum.l2, 1), 14),
right(word(m.sum.l2, 2), 14)
$$- '--' left(t2 tfu, 30) 'copy: ' m.l2.0 'parts'
$@do ix=1 to m.l2.0 $$- m.l2.ix
$]
if m.lst.0 > 0 | m.l2.0 > 0 then $@=[
COPY LIST LST$-[lst$] COPYDDN(TCOPYD)
PARALLEL
$-[full$]
SHRLEVEL CHANGE
$]
$/makeList/
$/oneJob3/
$@=/oneJob4/
//************************************* copy ***************************
// IF GEN.RUN AND GEN.RC < 8 THEN
// IF (ABEND OR NOT ABEND) THEN
//COPYINC EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=($dbSys,'$JOB.COPY')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.$dbSys(GENINC)
// ENDIF
//************************************* copy ***************************
// IF (ABEND OR NOT ABEND) THEN
//COPYFUL EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=($dbSys,'$JOB.COPY')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.$dbSys(GENFUL)
// ENDIF
//************************************* copy ***************************
// IF (ABEND OR NOT ABEND) THEN
//COPYCLIN EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=($dbSys,'$JOB.COPY')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.$dbSys(GENCLINC)
// ENDIF
//************************************* copy ***************************
// IF (ABEND OR NOT ABEND) THEN
//COPYCLFU EXEC PGM=DSNUTILB,REGION=0000M,COND=(8,LT),
// DYNAMNBR=99,PARM=($dbSys,'$JOB.COPY')
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSTEMPL DD DISP=SHR,DSN=$dbSys.DBAA.LISTDEF(TEMPL)
//SYSIN DD DISP=SHR,DSN=DSN.BESENWAG.$dbSys(GENCLFUL)
// ENDIF
// ENDIF
//*
// IF (ABEND OR RC GT 7 OR RC LT 0) THEN
//************************************* term utility if error **********
//TERM EXEC PGM=IKJEFT1A,REGION=0000M
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM($dbSys)
-TERM UTILITY('$JOB.COPY')
END
//************************************* send mail if error *************
//EMAIL EXEC PGM=OS3560
//STEPLIB DD DSN=PCL.U0000.P0.${rzD}AKT.PERM.@008.LLB,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//MAILIN DD *
sender=db-administration.db2@credit-suisse.com
to=db-administration.db2@credit-suisse.com
subject=$rz/$dbSys Besenwagen: ABEND in $job
testInfo=Y
info=Y
send=Y
text=ABEND or bad rc in Besenwagen
text= rz = $rz
text= dbSys = $dbSys
text= job = $job
// ENDIF
//*
//************************************* create member to mark finish ***
// IF (ABEND OR NOT ABEND) THEN
//FINISH EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD *
job $job finished $rz/$dbSys
//SYSUT2 DD DISP=SHR,DSN=DSN.BESENWAG.$dbSys(FINISH)
// ENDIF
$/oneJob4/
$/oneJob/
$#out 20160419 13:20:13