zOs/REXX/CONSUMGE
$#@
$*( control summary generator Version 3.1 5.10.16
5.10.16 walter xDoc rzY/Z
15. 9.16 walter remove old code, add comments
16. 6.16 dvbp : 2 TS aus "LOB" Prüfung ausgeschlossen
16. 6.16 dvbp : 13 TS mit > 200 Partitionen ausgeschlossen
22. 4.16 eos stage RD
19. 4.16 fmtBin7, neue xDoc, views .........
18.12.15 cDbaMdl mit ficd/iic mit part 0 etc., $tstDist, xDoc
20.10.15 tos810, copy nur falls seit 4 tagen keine Utility
ddlControl ==> QZT09
24. 9.15 with recover view and unload table for xDocs
4. 5.15 log Discontinuity Delta (timing window ingorieren)
9. 3.15 besenwagen fuer alle DBOF
19.12.14 save nonUser explain tables
3.12.14 fix fetch first only rr2/rq2/dbof, m rz dependent, RQ2 BE
27.11.14 fix define no: aus space statt (falsch) spaceF auslesen
11. 9.14 rz1 raus, rq2 rein, rz?sql raus
18. 8.14 conSum Elar: Fehler in txbc021/s rapportiern ohne absturz
8. 8.14 copyArc: alles neu erstellen, nicht mehr reNamen
18. 7.14 dvbp: 65 TS mit > 200 Partitionen ausschliessen
$*)
$=fun = m $** c=controlSummary QZT00??0 QZT00??1
$** d=ddlControl QZT09??1
$** r=copyArchive QZT10??0 QZT10??1
$** m=ca2 dba Models FICD? IIC? EXCL? STOP?
$** x=einmalAktion
$** 1=new plex naming convention, 0=old Rz naming
$=tstOut =- userid()'.tst.tecSv'
$=tstOut = - $** - out to productive libs, otherwise to this lib
$=tstDist =- 1 & $tstOut <> '-' $** distribute to tst or prod
$=logDisDelta = 10 minutes
$=useLgRn = 0
$=vCr=OA1P
$****************** generate all LCTLs for all rz/dbSys ****************
$= outLib = DSN.SOURCE.TECSV.GEN
$= outAtt = ::f mgmtClas(COM#A069)
if $fun == 'c' then $@[
$= distMbr = ##conSum control Summary und TecSv LCTLs
$] else if $fun == 'd' then $@[
$= distMbr = ##ddlCon ddl Control LCTLs
$] else if $fun == 'm' then $@[
$= outLib = DSN.SOURCE.CADB.CDBAMGEN
$= distMbr = ##dbaMdl ca DBA Models
$] else if $fun == 'r' then $@[
$= distMbr = ##copyAr copyArchive LCTLs
$] else if $fun == 'x' then $@[
$= distMbr = ##xxDist einmalAktion
$] else $@[
call err 'bad fun' $fun
$]
if $tstOut <> '-' then $@[
$= outLib = $tstOut
$= outAtt = ::f
$]
$= myTst =- f('%t s')
$= funInfo =- subWord($distMbr, 2)
$= distMbr =- word($distMbr, 1)
$=csDist =. jOpen(file($-outLib"("$-distMbr")" $-outAtt), '>')
call jWrite $csDist, $'$#@'
call jWrite $csDist, $'$** wsh script: distribute' $funInfo
call jWrite $csDist, $'$** generiert' $myTst
$=rzOne= $''
if 0 then $@[
$>. fEdit()
$@% gen rz2 dvbp
$;
call err 'tstEnd'
$]
if 0 then $@[
$@% gen rz1 dbtf
$@% gen rz1 dvtb
$@% gen rz1 dboc
$@rzEnd
$]
if 1 then $@[
$@% gen rz2 dbof
$@% gen rz2 dp2g
$@% gen rz2 dvbp
$@rzEnd
$]
if 1 then $@[
$@% gen rr2 dbof
$@% gen rr2 dp2g
$@% gen rr2 dvbp
$@rzEnd
$]
if 1 then $@[
$@% gen rq2 dbof
$@% gen rq2 dp2g
$@% gen rq2 dvbp
$@rzEnd
$]
if 1 then $@[
$@% gen rz4 dbol
$@% gen rz4 dp4g
$@rzEnd
$]
if 1 then $@[
$@% gen rzx de0g
$@% gen rzx devg
$@% gen rzx dpxg
$@% gen rzx dx0g
$@rzEnd
$]
if 1 then $@[
$@% gen rzy de0g
$@% gen rzy devg
$@% gen rzy dpyg
$@rzEnd
$]
if 1 then $@[
$@% gen rzz de0g
$@% gen rzz devg
$@% gen rzz dpzg
$@rzEnd
$]
call jClose $csDist
if $fun == 'm' then $@[
call jWrite $csDis2, $"$]"
call jClose $csDis2
$]
call adrIsp "view dataset('"$outLib"("$distMbr")')", 4
$****************** generate all LCTLs for one rz/dbSys ****************
$proc $@/gen/
parse upper arg ., rz dbSys
$=rz=- rz
$=rzDsn =- iiRz2Dsn(rz)
$=dbSys=- dbSys
$=dbC =- iiDbSys2C(dbSys)
$** beSave: qc515* every two hours
$=beSave =- dbSys == DBOF & wordPos(rz, 'RZ2 RR2') > 0
$** houseKeeping by eRet/Eos/xBox
$=isElar=- wordPos($dbSys, 'DVBP DEVG') > 0
$** houseKeeping by Elar/Eos/xBox
$=xDocHK =- wordPos($rz'/'$dbSys $*+
, 'RZ2/DBOF RR2/DBOF RZZ/DE0G RZY/DE0G' $*+
'RZ2/DVBP RR2/DVBP RZZ/DEVG RZY/DEVG') > 0
$** tecSv should not save xDoc
$=xDocNS =- $xDocHK | wordPos($rz'/'$dbSys $*+
, 'RQ2/DBOF RQ2/DVBP RZX/DEVG') > 0
$** xDocs Unloads must exist
$=xDocUnl =- $xDocHK & \ ($rz == 'RR2' & $isElar)
if $xDocNS then $@[
if $isElar then
$= xDocTx = XB docs
else
$= xDocTx = XC/XR docs
$= xDocNoTx = (non $xDocTx)
$= xDocBrTx = ($xDocTx)
if $rz == 'RZ2' & \ $isElar then
$= xDocConSum = $'$$r'
else
$= xDocConSum = $''
$] else $@[
$= xDocTx = $''
$= xDocBrTx = $''
$= xDocNoTx = $''
$= xDocConSum = $''
$]
if $xDocConSum = '' then
$= xDocConSu2 = - ??? noch nicht in count
else
$= xDocConSu2 = $''
$=isTec =- abbrev($dbSys, 'DP') | ( $dbSys == 'DBOC')
$=p2 =- iirz2p(rz)$dbC
$=job67 = 0$dbC
if word($rzOne, 1) == $rz then
$= rzOne = $rzOne $dbSys
else if $rzOne == '' then do
$= rzOne = $rz $dbSys
call jWrite $csDist, "say '"left("--- distributing" $funInfo,
"to" $rz" ", 65, '-')"'"
end
else
call err 'rz='rz 'dbSys='dbSys 'but rzOne='$rzOne
say 'gen rz='$rz', dbSys='$dbSys', p2='$p2 ,
|| ', isElar='$isElar', isTec='$isTec
$=lcLi=DSN.DB2.LCTL
if \ $tstDist then $@[
$=ll=$lcLi
$=outCaR = DSN.CADB2.$rzDsn.P0.CDBAMDL
$] else $@[
$=ll = $tstOut
$=outCaR = $tstOut
$]
if $fun == 'c' then $@[
$** c=controlSummary QZT00??0 QZT00??1
$= job = QZT00${job67}P
$= lctl = QZT00${p2}0
call jWrite $csDist, "call dsnCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lctl")'"
$;
$>$outLib($lctl)
if $xDocHK then $@[
$$ %tecSvUnl $dbSys
if $rz = RZ2 then
$$ sub 'dsn.besenwag.$dbSys(qcsBx${p2}p)'
$]
if $rz = RZZ | $dbSys = DBOC | $dbSys=DBOF | $dbSys = DP4G then
$$ %besenWag $dbSys
$;
$= lctl = QZT00${p2}1
call jWrite $csDist, "call dsnCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lctl")'"
$;
$>$outLib($lctl)
$@genConSum
$;
if $xDocHK then $@[
$= lctl = QZT00${p2}X
$= job = QCSBX${p2}P
$<>
$>$outLib($lctl)
$@% genBesenWagen
call jWrite $csDist, "call dsnCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lctl")'"
$]
$]
if $fun == 'd' then $@[
$** d=ddlControl QZT09??1
$= job = QZT09${p2}P
$= lctl = QZT09${p2}1
call jWrite $csDist, "call dsnCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lctl")'"
$;
$>$outLib($lctl)
$@genDDLCon
$]
if $fun == 'r' then $@[
$** r=copyArchive QZT10??0 QZT10??1
$@copyArc0 $>$outLib(QZT10${p2}0)
$;
$@copyArc1 $>$outLib(QZT10${p2}1)
$;
call jWrite $csDist, "call dsnCopy" ,
"'"$outLib"(QZT10"$p2"0)' ,"
call jWrite $csDist, " , '"$rz"/"$ll"(QZT10"$p2"0)'"
call jWrite $csDist, "call dsnCopy" ,
"'"$outLib"(QZT10"$p2"1)' ,"
call jWrite $csDist, " , '"$rz"/"$ll"(QZT10"$p2"1)'"
$]
if $fun == 'x1' then $@[
$** x=einmalAktion alte copyArc LCTLs archivieren
call jWrite $csDist, "call dsnCopy" ,
"'"$rz"/"$lcLi"(QZT10"$p2"0)' ,"
call jWrite $csDist, " , '"$tstOut"(QZT10"$p2"0)'"
call jWrite $csDist, "call dsnCopy" ,
"'"$rz"/"$lcLi"(QZT10"$p2"1)' ,"
call jWrite $csDist, " , '"$tstOut"(QZT10"$p2"1)'"
call jWrite $csDist, "call dsnCopy" ,
"'"$rz"/"$lcLi"(QMW10000)' ,"
call jWrite $csDist, " , '"$tstOut"(QMW10"$p2"0)'"
call jWrite $csDist, "call dsnCopy" ,
"'"$rz"/"$lcLi"(QMW1000M)' ,"
call jWrite $csDist, " , '"$tstOut"(QMW10"$p2"M)'"
$]
if $fun == 'x' then $@[
$** x=einmalAktion delete old copyArc LCTLs
dl = DSN.DB2.LCTL
ll = $dbSys'.DBAA.LCTL'
d1 = $dbC
j2 = iirz2c(rz)ii$dbC
call jWrite $csDist, "call dsnDel" $rz", '"dl"("$rz"SQL)'"
call jWrite $csDist, "call dsnDel" $rz", '"dl"("$rz"SQLOL)'"
call jWrite $csDist, "call dsnDel" $rz", '"dl"(RZ2SQL)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMT00"j2"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMT00"j2"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMT002F0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMT002F1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00"j2"M)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00"j2"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00081)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00082)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00101)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00102)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00131)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW00132)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW002Q1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW1000M)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW10000)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW71"j2"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QMW712"d1"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G01)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G02)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G03)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G04)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G05)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G06)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G07)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416G08)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416201)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416202)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416203)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416204)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416205)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416206)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416207)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416208)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416223)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416224)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416225)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416226)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416227)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416228)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416611)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416612)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416613)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416614)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416615)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416616)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416617)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QM416618)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT00"j2"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT00"j2"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT002"d1"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT002"d1"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT10"j2"M)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT10"j2"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT10"j2"1)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT102"d1"0)'"
call jWrite $csDist, "call dsnDel" $rz", '"ll"(QZT102"d1"1)'"
$]
if $fun == 'm' then $@[
$** m=ca2 dba Models FICD? IIC? EXCL? STOP?
$> $outLib(EXCL#$p2)
$@% genId EXCL$dbSys EXCL#$p2 QGS* exclude cbamdl für tecSv
$$ $' and'
$@% excludeCaMdl T
$<>
$> $outLib(STOP#$p2)
$$ #HCCD STOP,STOP
$@% genId STOP$dbSys STOP#$p2 QGS* stop cdbamdl für tecSv
$$ $' and'
$@% excludeCaMdl T
$;
$> $outLib(FICD#$p2)
$@% tecSvSql f FICD$dbSys FICD#$p2
$@% excludeCaMdl S
if $rz == 'RR2' & $dbSys == 'DBOF' then
$$- ' fetch first 16500 rows only'
else if $rz == 'RQ2' & $dbSys == 'DBOF' then
$$- ' fetch first 10500 rows only'
$;
$> $outLib(IIC#$p2)
$@% tecSvSql i IIC$dbSys IIC#$p2
$@% excludeCaMdl S
$@% mdlDist - $p2, $dbSys
$]
$/gen/
$proc $@/mdlDist/
parse arg , p2, dbSys
call jWrite $csDist, "call dsnCopy '"$outLib"(EXCL#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(EXCL"dbSys")'"
call jWrite $csDist, "call dsnCopy '"$outLib"(STOP#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(STOP"dbSys")'"
call jWrite $csDist, "call dsnCopy '"$outLib"(FICD#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(FICD"dbSys")'"
call jWrite $csDist, "call dsnCopy '"$outLib"(IIC#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(IIC"dbSys")'"
if \ ${?mdlDistRz} then $@[
$=mdlDistRz = $''
$=csDis2 =. jOpen(file($outLib"(##dbaMRZ)" $outAtt), '>')
call jWrite $csDis2, $"$#:"
call jWrite $csDis2, $"$** wsh script: distribute",
$funInfo "to rz"
call jWrite $csDis2, $"$** generiert" $myTst
call jWrite $csDis2, "rz = RZX"
call jWrite $csDis2, "rzD =- iiRz2Dsn($rz)"
call jWrite $csDis2, $"dst = $rz/dsn.cadb2.$rzD.P?.cdbaMdl"
call jWrite $csDis2, ""
call jWrite $csDis2, $"$#@"
call jWrite $csDis2, $"if $rz = 'RZ0' then $@["
call jWrite $csDis2, " call dsnCopy ",
$"'DSN.SOURCE.CADB.CDBAMDL', $dst"
$]
if $mdlDistRz <> $rz then $@[
$=mdlDistRz = $rz
call jWrite $csDis2, $"$] else if $rz = '"$rz$"' then $@["
call jWrite $csDis2, " call dsnCopy ",
$"'DSN.SOURCE.CADB.CDBAMDL', $dst"
$]
call jWrite $csDis2, " call dsnCopy '"$outLib"(EXCL#"p2")' ,"
call jWrite $csDis2, $" , $dst'(EXCL"dbSys")'"
call jWrite $csDis2, " call dsnCopy '"$outLib"(STOP#"p2")' ,"
call jWrite $csDis2, $" , $dst'(STOP"dbSys")'"
call jWrite $csDis2, " call dsnCopy '"$outLib"(FICD#"p2")' ,"
call jWrite $csDis2, $" , $dst'(FICD"dbSys")'"
call jWrite $csDis2, " call dsnCopy '"$outLib"(IIC#"p2")' ,"
call jWrite $csDis2, $" , $dst'(IIC"dbSys")'"
$/mdlDist/
$****************** generate ID: header & select current ... **********
$proc $@=/genIdCur/
$arg aAA
$@% genId $aAA
--************************************************************
-- Identifikation
--************************************************************
set current path oa1p;
select current timestamp "now", current server "currentServer"
from sysibm.sysDummy1
;
$/genIdCur/
$****************** generate ID3: 3 oder 4 id lines *******************
$proc $@=/genId/
$arg aDi aGe aJo aTi
$@ if $aTi <> '' then
-- $aTi
$@ if aJo <> '-' then
-- $aDi für $rz/$dbSys für Job $aJo
$@ else
-- $aDi für $rz/$dbSys
$@ if $aGe = '-' | $aGe = $aDi then
-- generiert um $myTst
$@ else
-- generiert als $aGe um $myTst
-- durch rz4/dsn.source.tecSv(conSumGe) >>> alle Aenderung da <<<
$/genId/
$****************** write rz?Sql from generated LCTLs *****************
$proc $@/rzEnd/ $** macht nichts mehr mehr
if $rzOne == '' then
call err 'rzEnd empty rzOne'
$= rzOne = $''
$/rzEnd/
$****************** generate controlSummary ***************************
$proc $@=/genConSum/
$@% genIdCur $lctl - $job Control Summary
--*********************************************************************
--$'$$'s fehlende Fullcopies Tablespaces, letzte 8 Tage $xDocNoTx
--*********************************************************************
$@missFullCopies1
and
$@%[exclude PT * $]
$@%[missFullCopies2 8$]
commit;
--*********************************************************************
--$'$$'r fehlende RecoveryBases Tablespaces, letzte 8 Tage $xDocNoTx
--*********************************************************************
$@% missBaseV2Beg older8d 8
and
$@% exclude = -vr C
$@% missBaseV2End
commit;
--*********************************************************************
--$'$$'r fehlende Fullcopies Indexspaces, letzte 8 Tage:
--************************************************************
SELECT SUBSTR(IX.CREATOR,1,8) AS CREATOR
,SUBSTR(IX.NAME,1,8) AS IXNAME
,SUBSTR(IX.DBNAME,1,8) AS DBNAME
,SUBSTR(IX.INDEXSPACE,1,8) AS IXSPACE
,IP.PARTITION
,DATE(IX.CREATEDTS) AS CREATEDATE
FROM SYSIBM.SYSINDEXES IX,
SYSIBM.SYSINDEXPART IP
WHERE IX.CREATOR = IP.IXCREATOR
AND IX.NAME = IP.IXNAME
AND IX.COPY = 'Y'
AND IP.SPACE <> -1 -- defineNo is in space not spaceF|
and
$@%[exclude IX * $]
AND NOT EXISTS (
$@%[selFullCopy IX.DBNAME IX.INDEXSPACE IP.PARTITION 8$]
)
ORDER BY CREATOR, IXNAME, IP.PARTITION
WITH UR;
commit;
--************************************************************
--$'$$'s Imagecopy Datasets die nicht katalogisiert sind:
--************************************************************
WITH DS AS
(
SELECT DBNAME, TSNAME, DSNUM
,MAX(ICDATE) ICDATE
,MAX(JOBNAME)JOBNAME
,DSNAME
FROM SYSIBM.SYSCOPY C
WHERE ICTYPE IN ('F','I')
AND C.TIMESTAMP >= CURRENT TIMESTAMP - 21 DAYS
and
$@%[exclude C K$]
GROUP BY DBNAME, TSNAME, DSNUM, DSNAME
)
SELECT SUBSTR(DBNAME,1,8) AS DBNAME
,SUBSTR(TSNAME,1,8) AS TSNAME
,CHAR(DSNUM) AS PART
,ICDATE, JOBNAME, DSNAME
FROM DS
where S100447.DSLOCATE(DSNAME) IS NULL
ORDER BY DBNAME, TSNAME, PART
WITH UR;
commit;
$@ if $beSave then $@=/conSuXBS/
--************************************************************
--$'$$'r XBS TS: fehlende RecoveryBases letzte 2 Tage:
--************************************************************
$@% missBaseV2Beg older2d 2
and
$@%[setQDbTs = -vr $]
$@bePred
$@% missBaseV2End
commit;
$/conSuXBS/
$@ if $xDocHK then $@=/conSumXDoc/
--*********************************************************************
--- $xDocTx ***
$@ if $isElar then $@=[
--- elar NDBS: neuer Elar Design seit 2013/14 ***
$] $@ else $@=[
--- XC/XR Kontrolle AuditPendenz 2015 ***
$]
$@xDocUnlErr
$@xDocRecErr
$@ if $isElar & $xDocHK then $@=[ $** ???? war xDocUnl
--********************************************************************
--$'$$'r XB ndbs - nicht katalogisierte Unloads
--********************************************************************
with s as
(
select db, ts, pa, stage || ' ' || staTb stage, unl
from oa1p.tqz005TecSvUnload
where unl <> '' and stage <> '-r'
)
select *
from s
where s100447.dslocate(unl) is null
order by db, ts, pa
fetch first 1000 rows only
;
$]
$/conSumXDoc/
$/genConSum/
$****************** generate DDLControl *******************************
$proc $@/genDDLCon/
$@% genIdCur $lctl - $job Control DDL
if $isElar then $@=/ddlElar/
--************************************************************
--$'$$' XB tablepaces mit > 200 Partitionen:
--************************************************************
select dbname, name, partitions
from sysibm.systablespace
where (partitions > 254 and dbName not like 'XB%')
or ( partitions > 200 and dbname like 'XB%'
$@[ if $dbSys = 'DVBP' then $@#[
and not ( -- Liste der 65 alten / temporären / fehlerhaften TS
-- mit > 200 Partitionen die wir nicht anzeigen
-- gemaess Absprache mit Elar vom 17.7.14
(dbName = 'XBCZ1001' and name in ('SHS0101$', 'SIT02001'
, 'SIT0201$', 'SPS0101$', 'SPS0301$'))
or (dbName = 'XBDJC001' and name in ('SDJC0041', 'SDJC0042'
, 'SDJC0043', 'SDJC004H', 'SDJC0051', 'SDJC0052', 'SDJC0053'
, 'SDJC005H', 'SDJC0061', 'SDJC0062', 'SDJC0063', 'SDJC006H'
, 'SDJC0071', 'SDJC0072', 'SDJC0073', 'SDJC007H', 'SDJC0081'
, 'SDJC0082', 'SDJC0083', 'SDJC008H'))
or (dbName = 'XBDJC002' and name in ('SDJC0101', 'SDJC0102'
, 'SDJC0103', 'SDJC010H', 'SDJC0111', 'SDJC0112', 'SDJC0113'
, 'SDJC011H'))
or (dbName = 'XBDPM001' and name in ('SDPM0021', 'SDPM0022'
, 'SDPM0023', 'SDPM002H'))
or (dbName = 'XBDPM002' and name in ('SDPM0181', 'SDPM0182'
, 'SDPM0183', 'SDPM018H', 'SDPM0221', 'SDPM0222', 'SDPM0223'
, 'SDPM022H'))
or (dbName = 'XBFC4001' and name in ('SFC40021', 'SFC40022'
, 'SFC40023', 'SFC4002H', 'SFC40031', 'SFC40032', 'SFC40033'
, 'SFC4003H', 'SFC40041', 'SFC40042', 'SFC40043', 'SFC4004H'
, 'SFC40051', 'SFC40052', 'SFC40053', 'SFC4005H', 'SFC40061'
, 'SFC40062', 'SFC40063', 'SFC4006H', 'SFC40071', 'SFC40072'
, 'SFC40073', 'SFC4007H'))
or (dbName = 'XBFQY002' and name in ('SFQY0021', 'SFQY0022'
, 'SFQY0023', 'SFQY0024', 'SFQY002H'))
or (dbName = 'XBFC4002' and name in ('SFC40091', 'SFC40092'
, 'SFC40093', 'SFC4009H'))
)
$] $]
)
order by dbName, name
;
commit;
$/ddlElar/
$@=/ddlCon1/
--************************************************************
--$'$$' LOB-Tablespaces mit falschen Spezifikationen:
--************************************************************
SELECT SUBSTR(DBNAME,1,8) AS DBNAME
,SUBSTR(NAME,1,8) AS TSNAME
,BPOOL
,LOG
FROM SYSIBM.SYSTABLESPACE S
WHERE TYPE = 'O'
AND (BPOOL NOT IN('BP8','BP32K') OR LOG = 'N')
and
$@%[exclude S L$]
ORDER BY DBNAME, TSNAME
WITH UR
;
commit;
--************************************************************
--$'$$' Tablespaces mit fehlerhafter Spezifikation:
--************************************************************
SELECT DISTINCT SUBSTR(TS.DBNAME,1,8) AS DBNAME
,SUBSTR(TS.NAME,1,8) AS TSNAME
,TS.BPOOL
,SUBSTR(PT.STORNAME,1,8) AS STORNAME
,PT.STORTYPE
FROM SYSIBM.SYSTABLESPACE TS,
SYSIBM.SYSTABLEPART PT
WHERE ts.dbNAME = PT.DBNAME
AND TS.NAME = PT.TSNAME
and
$@%[exclude PT F$]
AND (TS.BPOOL = 'BP0'
OR ( PT.STORNAME <> 'GSMS'
and (pt.dbName not like 'XB%'
or pt.storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4') ) )
OR PT.STORTYPE = 'E')
ORDER BY DBNAME, TSNAME
WITH UR;
commit;
--************************************************************
--$'$$' Indexspaces mit fehlerhafter Spezifikation:
--************************************************************
SELECT DISTINCT SUBSTR(IX.CREATOR,1,8) AS CREATOR
,SUBSTR(IX.NAME,1,8) AS IXNAME
,IX.BPOOL
,SUBSTR(IP.STORNAME,1,8) AS STORNAME
,IP.STORTYPE
FROM SYSIBM.SYSINDEXES IX,
SYSIBM.SYSINDEXPART IP
WHERE IX.CREATOR = IP.IXCREATOR
AND IX.NAME = IP.IXNAME
and
$@%[exclude IX F$]
AND (IX.BPOOL = 'BP0'
OR ( IP.STORNAME <> 'GSMS'
and (ix.dbName not like 'XB%'
or ip.storName not in
('GSMS1', 'GSMS2', 'GSMS3', 'GSMS4') ) )
OR IP.STORTYPE = 'E')
ORDER BY CREATOR, IXNAME
WITH UR;
commit;
--************************************************************
--$'$$' tableParts mit pri/secQty <> -1 oder vielen extents
--************************************************************
SELECT SUBSTR(PT.DBNAME,1,8) "db"
,SUBSTR(PT.TSNAME,1,8) "ts"
,PT.PARTITION "part"
,pt.pQty "priQty"
,pt.sQty "secQty"
,r.extents
FROM
SYSIBM.SYSTableSpace ts
join SYSIBM.SYSTABLEPART pt
on pt.dbName = ts.dbName and pt.tsname = ts.name
left join sysibm.sysTableSpaceStats r
on pt.dbNAME = r.DBNAME
AND pt.tsName = r.NAME
AND ts.dbid = r.dbid
AND ts.psid = r.psid
AND pt.partition = r.partition
WHERE (pt.pQty <> -1 or pt.sQty <> -1 or r.extents > 500)
and
$@%[exclude PT L$]
ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
fetch first 1000 rows only
WITH UR;
commit;
--************************************************************
--$'$$' IndexParts mit pri/secQty <> -1 oder vielen extents
--************************************************************
SELECT SUBSTR(Ip.ixCREATOR,1,8) AS CREATOR
,SUBSTR(Ip.ixNAME,1,16) AS IXNAME
,IP.PARTITION
,ip.pQty "priQty"
,ip.sQty "secQty"
,ip.extents
FROM
SYSIBM.SYSINDEXES Ix
join SYSIBM.SYSINDEXPART IP
on ix.creator = ip.ixCreator and ix.name = ip.ixName
left join SYSIBM.SYSINDEXSpaceStats r
on ix.creator = r.creator and ix.name = r.creator
and ix.dbid = r.dbid and ix.isobid = r.isobid
and ip.partition = r.partition
WHERE (ip.pQty <> -1 or ip.sQty <> -1 or r.extents > 300)
and
$@%[exclude IX L$]
order by ix.creator, ix.name, ip.partition
fetch first 1000 rows only
WITH UR;
$/ddlCon1/
$/genDDLCon/
$****************** generate Excludes *********************************
$proc $@/exclude/
$*( exF K nicht Katalogisierte image Copy
L falsche spezifikation LOB usw
F Falsche spezifikation andere
S TecSv SQL nur fuer IIC und FICD#* und IIC#*
T TecSv SQL andere
C Controlsummary
* alle anderen
$*)
parse upper arg , q exF
$@%[setQDbTs - q$]
$=exF=- exF
$@=[
----- begin @proc exclude: excludes --- $exF --------------------------
NOT ($db LIKE 'WKDB%') -- DB2 WORK DATABASE
AND NOT ($db LIKE '%MAREC%') -- marec generated
AND NOT ($db LIKE 'QZ91%') -- test klem 43
AND NOT ($db LIKE 'QZ92%') -- test klem 43
and not translate($db, '999999999AAAAAA', '012345678FISWXY')
= 'DA999999' -- user datenbanken
AND NOT ($db LIKE 'DB2ALA%') -- marec generated
AND NOT ($db LIKE 'DB2POOL%') -- DB2 STOR.POOL WIESI
AND NOT ($db LIKE 'DB2MAPP%') -- REORG MAPPING TABLES
AND NOT ($db LIKE 'DB2PLAN%' -- explain tables
$@[ if q <> 'IX' then $@=[
and translate(left($ts, 7), '999999999AA', '012345678FG')
= 'A999999' -- user explain tables
$] else $@=[
-- cannot exclude user explain tables ONLY for indexes
$] $]
)
$]
if pos($exF, 'FL') > 0 | $isTec then $@=[
AND NOT ($db like 'DSN%')
$] else $@=[
AND NOT ($db like 'DSNDB%') -- DB2 CATALOG
AND NOT ($db LIKE 'DSN8%') -- IBM TEST DB
AND NOT ($db = 'DSNTESQ') -- DB2 CATALOG CLONE
$]
if pos($exF, '*CSTK') > 0 & $q <> 'IX' then $@=[
AND NOT ($db like 'CSQ%' AND $ts like 'TSBLOB%' )
-- M-QUEUE DATENBANK
$]
if pos($exF, 'FL') > 0 then $@=[
AND NOT ($db = 'SYSIBMTA') -- engineering
AND NOT ($db = 'SYSIBMTS') -- engineering
AND NOT ($db like 'IDTA%') -- ibm tools
AND NOT ($db = 'DB2PM') -- PERF.EXPERT DATABASE
AND NOT ($db = 'DB2OSC') -- osc
AND NOT ($db like 'DSQ%') -- qmf databse
AND $db NOT IN ('DUTILTST','XSN8D71L','DB2XML')
$]
if wordPos($dbSys, 'DBTF') > 0 then $@=[
AND NOT ($db LIKE 'DAU%') -- Schulung Gerrit
$]
if wordPos($dbSys, 'DX0G') > 0 then $@=[
AND NOT ($db LIKE '%1P%') -- PROTOTYPEN
AND NOT ($db LIKE 'DXB%') -- PROTOTYPEN
AND NOT ($db LIKE 'DGDB%') -- PROTOTYPEN
$]
if $exF == 'L' then $@=[
AND $db NOT LIKE 'PTDB%'
$@ if q <> 'IX' then $@=[
AND NOT ($db = 'DXB03'
AND $ts in ('LXBH111','LXBH111X')) $]
$@ if $isTec then $@=[
AND $db NOT LIKE 'BMC%'
AND $db NOT LIKE 'DCMN00%' --Hat cloneTable Alter aufwendig
$]
$] else $@/excludeNotL/
if $isTec & $q <> 'IX' & pos($exF, '*CKST') > 0 then $@=[
AND NOT ($db = 'OS80A1P' AND $ts = 'A810A'
$@ if $exF == 'S' then
$** Achtung -- Komentar gibt DBA Fehler
and basTst > current timestamp - 108 hours /* 4.5 tage */
$@ else if $exF == 'C' then
$** Achtung -- Komentar gibt DBA Fehler
and basTst > current timestamp - 84 hours /* 3.5 tage */
) -- IMT1201P macht Load mit ImageCopy
-- aber nachher monatelang nichts mehr
-- ZeitKonflikt mit tecSv |
$]
if $dbSys = 'DP4G' then $@[
if $exF == 'F' then
if $q == IX then $@=[
AND NOT $db = 'DB2PMPDB' -- PMON KITP2
$] else $@=[
AND NOT ($db = 'DB2PMPDB'
AND $ts like 'ACCS%') -- PMON KITD2
$]
$@=[
AND NOT $db in ('DB2PDB', 'DB2PDB2', 'DB2PDB3') -- performance DB
$@ if $exF == 'F' then $@=[
$@ if q \== 'IX' then
AND NOT ($db = 'AC04A1P' AND $ts = 'SAC041A' ) -- ACF Gründler
AND NOT ($db = 'AC05A1P' ) -- ACF Gründler
$]
$]
$]
if $dbSys = 'DBOC' then $@=[
AND NOT ($db = 'DB2PDB') -- performance DB
AND NOT ($db = 'DB2XML') -- performance DB
$]
$** if $isElar & $exF <> 'K' then
if $xDocNS & $q <> 'IX' & pos($exF, '*CST') > 0 then $@=[
and not
$@% xDocPred $db $ts
$]
if pos($exF, 'ST') > 0 & $beSave then $@=[
and not
$@bePred
$]
$/excludeNotL/
$@=[
----- end @proc exclude: excludes --- $exF --------------------------
$]
$/exclude/
$proc $@/excludeCaMdl/
$arg exF
$@% exclude = -S $exF
$| $for li $$- repAll(strip($li, 't'), '%', '%%')
$/excludeCaMdl/
$****************** set vars q, db and ts ******************************
$proc $@/setQDbTs/
parse arg , q
hasQual = \ abbrev(q, '-')
q = strip(translate(q, ' ', '-'))
$= q =- q
quD = copies(q'.', hasQual)
upper q
$=db =- quD'dbName'
if q == 'S' then $@[
$= ts =- quD'name'
$] else if q == 'IX' then $@[
$= ts = ???noTs???
$] else if q == 'VR' then $@[
$= db =- quD'db'
$= ts =- quD'ts'
$] else $@=[
$= ts =- quD'tsName'
$]
$/setQDbTs/
$****************** BE save *******************************************
$proc $@=/bePred/
($db = 'BE01A1P' and $ts like 'A0%' -- beSave QC515* alle 2h
or $db = 'CD02A1P' and $ts = 'A600A')
$/bePred/
$****************** missing fullcopies alt ****************************
$proc $@=/missFullCopies1/
---- begin @proc missFullCopies1: fehlende Fullcopies -----------------
SELECT SUBSTR(PT.DBNAME,1,8) AS DBNAME
,SUBSTR(PT.TSNAME,1,8) AS TSNAME
,PT.PARTITION
,DATE(TS.CREATEDTS) AS CREATEDATE
FROM SYSIBM.SYSTABLESPACE TS,
SYSIBM.SYSTABLEPART PT
WHERE ts.dbNAME = pt.DBNAME
AND TS.NAME = PT.TSNAME
---- end @proc missFullCopies1: fehlende Fullcopies -----------------
$/missFullCopies1/
$proc $@/missFullCopies2/
parse arg , days
$@=[
---- begin @proc missFullCopies2: fehlende Fullcopies -----------------
AND TS.NTABLES <> 0
AND PT.SPACE <> -1 -- define no is only in space not spaceF |
AND NOT EXISTS (
$@%[selFullCopy - PT.DBNAME PT.TSNAME PT.PARTITION arg(2)$]
)
ORDER BY DBNAME, TSNAME, PT.PARTITION
WITH UR;
---- end @proc missFullCopies2: fehlende Fullcopies -----------------
$]
$/missFullCopies2/
$proc $@/selFullCopy/
parse arg , db ts part days
$@=[
---- begin @proc selFUllCopy: select fullcopy etc. --------------------
SELECT ' '
FROM SYSIBM.SYSCOPY CP
WHERE $-[db$] = CP.DBNAME
AND $-[ts$] = CP.TSNAME
AND cp.dsNum in ($-[part$], 0)
-- fullcopy or fullLog
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
) )
---- end @proc selFUllCopy: select fullcopy etc. --------------------
$]
$/selFullCopy/
$proc $@=/genBesenWagen/
$@% genIdCur $lctl - $job BesenWagen $xDocTx
$@xDocUnlErr
$@xDocRecErr
$@ if \ $isElar then $@=[
--*********************************************************************
-- $xDocTx: fehlende Fullcopies/Recoverybases, letzte 8 Tage
--*********************************************************************
select char(db, 8) db, char(ts, 8) ts, pa
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(recSta, 1, 40) recoveryState
, substr(case when basTy <> ' '
then basTy || ' ' || char(basTst) else '' end, 1, 21)
"last fullCopy"
, substr(case when unl <> '' then char(unlTst) else '' end
, 1, 10) "unload"
-- , z.*
$@xDocFromRecovLoad
and ( fun not in ('r', 'l', '-')
or (stage = 'UL' and lok <> 'l'
and staUpd < current timestamp - 1 day ) )
order by db, ts, pa
$@stageInfo
;
$]
--*********************************************************************
--FixBesenwagen fuer $xDocTx
--*********************************************************************
select char(db, 8) db, char(ts, 8) ts, pa
, substr(fqzFmtBin7(spc), 1, 7) spaceBy
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(recSta, 1, 40) recoveryState
, substr( basTy || char(basTst), 1, 20) "baseTst"
$@xDocFromRecovLoad
and ( fun not in ('r', 'l', '-')
and (recover not in ('ok', 'older8d')
or basTst < (current timestamp - 12 days) + 10 hour)
)
order by bastst, db, ts, pa
;
$/genBesenWagen/
$****************** missing Recover Base Version sept 15 **************
$proc $@=/missBaseV2Beg/
$arg txtLim dayLim
SELECT SUBSTR(db, 1, 8) "db"
, SUBSTR(ts,1,8) "ts"
, pa as "part"
, substr(insTxt, 1, 6) "inst"
, case when recover in ('ok', 'older8d')
then '$txtLim' else recover end recover
, basTyTx
, basPa
, basTst
from $vCr.vQz005RecovDelta
WHERE ( not (recover in ('defNo', 'noTb')
or (recover in ('ok', 'older8d')
and basTst >= current timestamp - $dayLim days )))
$*( WHERE ( recover not in ('ok', 'defNo', 'noTb')
or ( recover = 'ok' and basTst
< current timestamp - $dayLim days )
) $*)
$/missBaseV2Beg/
$proc $@=/missBaseV2End/
order by 1, 2, 3
with ur
;
$/missBaseV2End/
$*(**************** predicate to select ts under xDoc housekeeping ****
is also used with a not in front| ************* $*)
$proc $@/xDocPred/
$arg qDb qTs
if $isElar then $@=[
($qDb like 'XB%') -- ELAR Dokumente
$] else $@=[
( ($qDb = 'XC01A1P' and $qTs <> 'A500A'
and ($qTs LIKE 'A2%'or $qTs LIKE 'A5%'))
-- EOS: Armin Breyer
or ($qDb = 'XR01A1P' AND $qTs LIKE 'A2%' )
) -- ERET: Armin Breyer
$]
$/xDocPred/
$*(**************** reovery error of xdoc
summary and details *************************** $*)
$proc $@=/xDocRecErr/
--*********************************************************************
-- $xDocTx: Summary Stages / Recoverybases / Unloads
--*********************************************************************
select substr(fqzFmtBin7(sum(spc))
|| right(' ' || count(*), 8), 1, 15)
"spaceBy count"
, stage
, substr(recSta, 1, 70) recoveryState
$@xDocFromRecovLoad
group by stage, recSta
order by 2, 3
--
-- columns
$@ if \ $isElar then $@=[
-- stage: ' ' non document tables in XC/XR DBs
$] $@ else $@=[
-- stage: '-m' missing in stage tables
-- '-a' registered only in txba201
-- '-w' www tables
$]
-- recoveryState:
-- substr(1, 1) recover by
-- 'r' db2 recovery from imageCopy and db2Log
-- 'l' load unload dsn
-- '?' either is not possible or unreliable
-- substr(3...) recover state / warning / error
;
--*********************************************************************
--$xDocConSum $xDocTx: fehlende Recoverybases / Unloads $xDocConSu2
--*********************************************************************
select char(db, 8) db, char(ts, 8) ts, pa
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(recSta, 1, 40) err
, substr(case when basTy <> ' '
then basTy || ' ' || char(basTst) else '' end, 1, 21)
"last fullCopy"
, substr(case when unl <> '' then char(unlTst) else '' end
, 1, 10) "unload"
-- , z.*
$@xDocFromRecovLoad
and ( fun not in ('r', 'l', '-')
$@ if $isElar then $@=[
or ( stage in ('-w', 'DL', 'UL') and lok <> 'l' )
$] $@ else $@=[
or ( stage in ('UL') and lok <> 'l'
and staUpd < current timestamp - 1 day )
$]
)
order by db, ts, pa
fetch first 1000 rows only
$@stageInfo
;
$/xDocRecErr/
$****************** unload errors Summary and Details *****************
$proc $@=/xDocUnlErr/
--************************************************************
-- $xDocTx: Statistik Stage tables
--************************************************************
$@xDocUnlErrWith
select stage "stage"
, count(*) "#parts"
, smallInt(count(distinct db || '.' || ts)) "#ts"
, substr(err, 1, 75) "error / info"
from uE
group by stage, err
order by case when stage = '-r' then 0 else 1 end, stage, err
;
--************************************************************
--$xDocConSum $xDocTx: Fehler in stageTables $xDocConSu2
--************************************************************
$@xDocUnlErrWith
select db, ts
, substr(right(' ' || pa, 5), 1, 5) part
, stage || ' ' || staTb
, substr(err, 1, 36) err
, substr(unl, 1, 41) unl
from uE
where err <> '' and not (db = '' and pa < -100)
order by case when stage = '-r' then 0 else 1 end, db, ts, pa
fetch first 1000 rows only
$@stageInfo
;
commit;
$/xDocUnlErr/
$*( *************** unload error with *********************************
union of errors from unoad table
and infos/errors about last load of it ******** $*)
$proc $@=/xDocUnlErrWith/
with uE (db, ts, pa, stage, staTb, unl, err) as
(
select db, ts, pa, stage, staTb, unl
, strip(case
$@ if $isElar then $@=[
when stage not in ('RW', 'CL', 'UL', 'DL'
, '-m', '-a', '-w', '-r') then ' badStage=' || stage
when unl <> '' and stage in ('RW')
then ' unloadInStage=' || stage
$] $@ else $@=[
when stage not in ('IN', 'RD', 'RU', 'FZ', 'UL', 'MI', '-r')
or (stage = 'RD'
and not (db = 'XC01A1P' and ts like 'A200A%'))
then ' badStage=' || stage
when unl <> '' and stage in ('RU', 'MI')
then ' unloadInStage=' || stage
$]
else ''
end || ' ' || err) ee
from oa1p.tqz005tecsvUnload u
where db <> ''
union all select db, ts,-101, stage, staTb, unl
, 'refresh from ' || left(char(unlTst), 19)
|| ' to ' || left(char(punTst), 19)
from oa1p.tqz005tecsvUnload u
where db = '' and ts = ''
union all select db, ts,-101, stage, staTb, unl
, 'refresh info ' || info
from oa1p.tqz005tecsvUnload u
where db = '' and ts = ''
union all select db, ts,-101, stage, staTb, unl, err
from oa1p.tqz005tecsvUnload u
where db = '' and ts = '' and err <> ''
union all select db, ts, pa, stage, staTb
, char(unlTst), 'refresh older 3h'
from oa1p.tqz005tecsvUnload
where db='' and ts='' and pa=-99
and unlTst < current timestamp - 3 hours
union all select '', '', -99, '-r', '', '', count(*) ||' refresh rows'
from oa1p.tqz005tecsvUnload
where db='' and ts='' and pa=-99 and stage = '-r'
having count(*) <> 1
)
$/xDocUnlErrWith/
$****************** from recov/Load view with recSta ******************
$proc $@=/xDocFromRecovLoad/
from ( select r0.*
, fun || case when recLR in ('r', '2')
then ' ' || recover else '' end
|| case when recLR in ('l', '2')
then rTrim(' ' || load) else '' end recSta
$@ if $useLgRn then $@=[
from $vCr.vQz005RecovDeltaLoadLgRn r0) r
$] $@ else $@=[
from $vCr.vQz005RecovDeltaLoad r0) r
$]
where
$@% xDocPred db ts
$/xDocFromRecovLoad/
$****************** comment on stageInfo fields ***********************
$proc $@=/stageInfo/
-- stage: substr(1,2) = stage
-- substr(4,2) = stageTables
$@ if $isElar then $@=[
-- i = BUA.TXBI003 segment table
-- a = bua.txba201
-- c = BUA.TXBC021 unload table
-- s = BUA.TXBC021s unload table
$] $@ else $@=[
-- 1 = OA1P.TXC106A1 EOS alt ==> OA1P??.TXC200A1
-- 4 = OA1P.TXC406A1 eRet AFP ==> OA1P.TXC501A1+502A1
-- EOS PDF ==> OA1P.TXC51*A1
-- r = OA1P.TXR106A1 eRet ==> OA1P.TXR200A1+201A1
$]
$/stageInfo/
$****************** tecSave sql ***************************************
$proc $@=/tecSvSql/
$arg tsF aAA
$@ if $tsF == 'i' then $@=[
#HCCD (TS) RTS incremental IMAGE COPY
$@% genId $aAA QGS300${dbC}P tecSv incremental copy
$] $@ else if $tsF == 'f' then $@=[
#HCCD (TS) RTS full IMAGE COPY
$@% genId $aAA QGS400${dbC}P tecSv full copy
$] $@ else $@[
call err 'bad fun tsF' $tsF 'in tecSvSql'
$]
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)
*/
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 Prozent fuer ca dbAnalyser
and
$/tecSvSql/
$proc $@=/copyArc0/
$** currently always empty
$/copyArc0/
$proc $@=/copyArc1/
$= cre =- if($dbSys == 'DBTF', 'OA1T', 'OA1P')
SELECT CURRENT TIMESTAMP - 3 MINUTES,
CHAR(' SUB#ADB1 $cre.TADM62A1 ', 50)
FROM SYSIBM.SYSDUMMY1
;
SELECT C.DBNAME, C.TSNAME, C.DSNUM, C.TIMESTAMP, C.ICTYPE, C.DSNAME,
CHAR(C.COPYPAGESF * 1024 * S.PGSIZE) COPIED
FROM SYSIBM.SYSCOPY C, SYSIBM.SYSTABLESPACE S
WHERE C.ICTYPE IN ('F', 'I')
AND S.DBNAME = C.DBNAME
AND S.NAME = C.TSNAME
$@[ if wordPos($dbSys, 'DX0G DVTB') > 0 then $@=[
AND S.DBNAME = ' no no'
$] $]
ORDER BY 1, 2, 3, 4 DESC
WITH UR
;
$/copyArc1/
$#out 20161005 16:53:50
$#out 20161005 16:53:12
$#out 20161005 16:52:29
fatal error in WSH: bad fun c
in wsh phase run
$#out 20161005 16:50:54
$#out 20161005 16:48:51
$#out 20161005 16:45:32
$#out 20161005 16:34:23
$#out 20161005 16:33:59
fatal error in WSH: bad fun c
in wsh phase run
$#out 20160928 15:36:46
$#out 20160928 15:33:31
$#out 20160928 15:31:36
$#out 20160928 15:27:21
$#out 20160928 15:21:42
$#out 20160927 14:28:25