zOs/REXX/CONSUMGF
$#@
$*( control summary generator Version 2.8 23. 9.15
Achtung: braucht wsh5
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 = c $** c=controlSummary QZT00??0 QZT00??1
$** d=ddlControl QMW71??1
$** r=copyArchive QZT10??0 QZT10??1
$** m=ca2 dba Models FICD? IIC? EXCL? STOP?
$** x=einmalAktion
$=usePlex = 1 $** 1=new plex naming convention, 0=old Rz naming
$=usePlex =- $fun = 'c' $** zurzeit noch nicht weiter ausgebreitet
$=tstOut= - $** - out to productive libs, otherwise to this lib
$=tstOut=- userid()'.tst.tecSv'
$=logDisDelta = 15 minutes
$=useLgRn = 0
$****************** generate all LCTLs for all rz/dbSys ****************
if $tstOut == '-' then $@[
$= outLib = DSN.SOURCE.TECSV.GEN$-[copies(PLEX, $usePlex)$]
$= outAtt = ::f mgmtClas(COM#A069)
$] else $@[
$= outLib = $tstOut
$= outAtt = ::f
$]
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 $@[
if $tstOut == '-' 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
$]
$= funInfo =- subWord($distMbr, 2)
$= distMbr =- word($distMbr, 1)
$=csDist =. jOpen(file($-outLib"("$-distMbr")" $-outAtt), '>')
call jWrite $csDist, $'$#@'
call jWrite $csDist, $'$** wsh script: distribute' $funInfo
$=rzOne= $''
if 0 then $@[
$>. fEdit()
$@%[gen rz2 dvbp QMW0010$]
$;
call err 'tstEnd'
$]
if 0 then $@[
$@%[gen rz1 dbtf QMW0002$]
$@%[gen rz1 dvtb QMW0006$]
$@%[gen rz1 dboc QMW0007$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rz2 dbof QMW0008$]
$@%[gen rz2 dp2g QMW0013$]
$@%[gen rz2 dvbp QMW0010$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rr2 dbof QMW0008$]
$@%[gen rr2 dp2g QMW0013$]
$@%[gen rr2 dvbp QMW0010$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rq2 dbof QMW0008$]
$@%[gen rq2 dp2g QMW0013$]
$@%[gen rq2 dvbp QMW0010$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rz4 dbol QMW0009$]
$@%[gen rz4 dp4g QMW0016$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rzx de0g QMW0026$]
$@%[gen rzx devg QMW0027$]
$@%[gen rzx dpxg QMW0028$]
$@%[gen rzx dx0g QWM0024$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rzy de0g QMW0029$]
$@%[gen rzy devg QMW0030$]
$@%[gen rzy dpyg QMW0031$]
$@rzSQL
$]
if 1 then $@[
$@%[gen rzz de0g QMW0017$]
$@%[gen rzz devg QMW0023$]
$@%[gen rzz dpzg QMW0025$]
$@rzSQL
$]
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 job7
$=rz=- rz
$=rzDsn =- iiRz2Dsn(rz)
$=dbSys=- dbSys
$=isElar=- wordPos($dbSys, 'DVBP DVTB DEVG') > 0
$=hasXDoc =- $dbSys = DBOF | $dbSys = DVBP
if \ $hasXDoc then $@[
$= xDocTx = $''
$= xDocBrTx = $''
$= xDocNoTx = $''
$] else $@[
if $isElar then
$= xDocTx = XB docs
else
$= xDocTx = XC/XR docs
$= xDocNoTx = (nicht $xDocTx)
$= xDocBrTx = ($xDocTx)
$]
$=isTec =- abbrev($dbSys, 'DP') | ( $dbSys == 'DBOC')
$=p2 =- iirz2p(rz)iiDBSys2C(dbSys)
if $usePlex then $@[
$=j2 = $p2
$=d2 = $j2
$=job67 =- '0'iiDBSys2C(dbSys)
$] else $@[
$=j2 =- iirz2c(rz)iiDBSys2C(dbSys)
$=d2 =- iirz2c($rzDsn)iiDBSys2C(dbSys)
$=job67 = $d2
$]
$= qmw00 = QMW00${j2}
$= qmw71 = QMW71${j2}
if word($rzOne, 1) == $rz then
$= rzOne = $rzOne $dbSys
else if $rzOne == '' then do
$= rzOne = $rz $dbSys
call jWrite $csDist, "say 'copying to" $rz "---------------'"
end
else
call err 'rz='rz 'dbSys='dbSys 'but rzOne='$rzOne
say 'gen rz='$rz', dbSys='$dbSys', j7='job7', j2='$j2 'd2='$d2 ,
|| ', isElar='$isElar', isTec='$isTec
if $usePlex then
$=lcLi=DSN.DB2.LCTL
else
$=lcLi=$dbSys.DBAA.LCTL
if $tstOut == '-' then $@[
$=ll=$lcLi
$=outCaR = DSN.CADB2.$rzDsn.P0.CDBAMDL
$] else $@[
$=ll = $tstOut
$=outCaR = $tstOut
$]
if $fun == 'c' then $@[
$** c=controlSummary QZT00??0 QZT00??1
$= gttNdPaDone = 0
if $rz == 'RZ1' then
$= job =- job7'P'
else
$= job = QZT00${job67}P
$= lctl = QZT00${j2}0
$= lcDi = QZT00${d2}0
call jWrite $csDist, "call csmCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lcDi")'"
$;
$>$outLib($lctl)
if $hasXDoc then $@[
$$ %tecSvUnl $dbSys
if $rz = RZ2 then
$$ sub 'dsn.besenwag.$dbSys(qcsBxBFp)'
$]
if $rz = RZZ | $dbSys = DBOC | $dbSys=DBOF | $dbSys = DP4G then
$$ %besenWag $dbSys
$;
$= lctl = QZT00${j2}1
$= lcDi = QZT00${d2}1
$= rzOne = $rzOne $lctl
call jWrite $csDist, "call csmCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lcDi")'"
$;
$>$outLib($lctl)
$@genConSum
$;
if $dbSys = DBOF | $dbSys = DVBP then $@[
$= lctl = QZT00${j2}X
$= lcDi = $lctl
$= job = QCSBX${j2}P
$<>
$>$outLib($lctl)
$@% genBesenWagen
$]
$]
if $fun == 'd' then $@[
$** d=ddlControl QMW71??1
$= gttNdPaDone = 0
$= job = ${qmw71}P
$= lctl = ${qmw71}1
$= lcDi = $lctl
call jWrite $csDist, "call csmCopy '"$outLib"("$lctl")' ,"
call jWrite $csDist, " , '"$rz"/"$ll"("$lctl")'"
$;
$>$outLib($lctl)
$@genDDLCon
$]
if $fun == 'r' then $@[
$** r=copyArchive QZT10??0 QZT10??1
$@copyArc0 $>$outLib(QZT10${d2}0)
$;
$@copyArc1 $>$outLib(QZT10${d2}1)
$;
call jWrite $csDist, "call csmCopy" ,
"'"$outLib"(QZT10"$d2"0)' ,"
call jWrite $csDist, " , '"$rz"/"$ll"(QZT10"$d2"0)'"
call jWrite $csDist, "call csmCopy" ,
"'"$outLib"(QZT10"$d2"1)' ,"
call jWrite $csDist, " , '"$rz"/"$ll"(QZT10"$d2"1)'"
$]
if $fun == 'x1' then $@[
$** x=einmalAktion alte copyArc LCTLs archivieren
call jWrite $csDist, "call csmCopy" ,
"'"$rz"/"$lcLi"(QZT10"$d2"0)' ,"
call jWrite $csDist, " , '"$tstOut"(QZT10"$d2"0)'"
call jWrite $csDist, "call csmCopy" ,
"'"$rz"/"$lcLi"(QZT10"$d2"1)' ,"
call jWrite $csDist, " , '"$tstOut"(QZT10"$d2"1)'"
call jWrite $csDist, "call csmCopy" ,
"'"$rz"/"$lcLi"(QMW10000)' ,"
call jWrite $csDist, " , '"$tstOut"(QMW10"$d2"0)'"
call jWrite $csDist, "call csmCopy" ,
"'"$rz"/"$lcLi"(QMW1000M)' ,"
call jWrite $csDist, " , '"$tstOut"(QMW10"$d2"M)'"
$]
if $fun == 'x' then $@[
$** x=einmalAktion delete old copyArc LCTLs
call jWrite $csDist, "call csmDel" $rz", '"$ll"("$qmw00"0)'"
call jWrite $csDist, "call csmDel" $rz", '"$ll"("$qmw00"M)'"
call jWrite $csDist, "call csmDel" $rz ", '"$ll"(QZT10"$j2"M)'"
$]
if $fun == 'm' then $@[
$** m=ca2 dba Models FICD? IIC? EXCL? STOP?
$= bb =. jBuf()
$;
$>.bb
$@%[exclude = -S T$]
$;
ll = $bb'.BUF'
$** doppelte % fuer ca dbAnalyser
do lx=1 to m.ll.0
m.ll.lx = repAll(strip(m.ll.lx, 't'), '%', '%%')
end
$;
$> $outLib(EXCL#$p2)
$@%[genId3 EXCL$dbSys EXCL#$p2 $]
$$ $' and'
$@<.bb
$;
$> $outLib(STOP#$p2)
$$ #HCCD STOP,STOP
$@%[genId3 STOP$dbSys STOP#$p2 $]
$$ $' and'
$@<.bb
$;
$> $outLib(FICD#$p2)
$@%[tecSvSql f FICD$dbSys FICD#$p2 $]
$@<.bb
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 $]
$@<.bb
$@% mdlDist - $p2, $dbSys
$]
$/gen/
$proc $@/mdlDist/
parse arg , p2, dbSys
call jWrite $csDist, "call csmCopy '"$outLib"(EXCL#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(EXCL"dbSys")'"
call jWrite $csDist, "call csmCopy '"$outLib"(STOP#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(STOP"dbSys")'"
call jWrite $csDist, "call csmCopy '"$outLib"(FICD#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(FICD"dbSys")'"
call jWrite $csDist, "call csmCopy '"$outLib"(IIC#"p2")' ,"
call jWrite $csDist, " , '"$rz"/"$outCaR"(IIC"dbSys")'"
if \ ${?mdlDistRz} then $@[
$=mdlDistRz = $''
$=csDis2 =. jOpen(file($outLib"(##dbaMRZ)" $outAtt), '>')
call jWrite $csDis2, $"$#: $** distribute cDbaMdl to rz"
call jWrite $csDis2, "rz = RZX"
call jWrite $csDis2, $"dst = $rz/dsn.cadb2.$rz.P7.cdbaMdl"
call jWrite $csDis2, ""
call jWrite $csDis2, $"$#@"
call jWrite $csDis2, $"if $rz = 'RZ0' then $@["
call jWrite $csDis2, " call csmCopy ",
$"'DSN.SOURCE.CADB.CDBAMDL', $dst"
$]
if $mdlDistRz <> $rz then $@[
$=mdlDistRz = $rz
call jWrite $csDis2, $"$] else if $rz = '"$rz$"' then $@["
call jWrite $csDis2, " call csmCopy ",
$"'DSN.SOURCE.CADB.CDBAMDL', $dst"
$]
call jWrite $csDis2, " call csmCopy '"$outLib"(EXCL#"p2")' ,"
call jWrite $csDis2, $" , $dst'(EXCL"dbSys")'"
call jWrite $csDis2, " call csmCopy '"$outLib"(STOP#"p2")' ,"
call jWrite $csDis2, $" , $dst'(STOP"dbSys")'"
call jWrite $csDis2, " call csmCopy '"$outLib"(FICD#"p2")' ,"
call jWrite $csDis2, $" , $dst'(FICD"dbSys")'"
call jWrite $csDis2, " call csmCopy '"$outLib"(IIC#"p2")' ,"
call jWrite $csDis2, $" , $dst'(IIC"dbSys")'"
$/mdlDist/
$****************** generate ID: header & select current ... **********
$proc $@=/genId/
$=aTi=- arg(2)
-- $aTi
-- lctl $lctl: sql für job $job für $rz/$dbSys
$@[ if $lctl \== $lcDi then
$$ -- name $lcDi in Library in $rz |||
$]
$@%[genId3 $lctl$]
--************************************************************
-- Identifikation
--************************************************************
set current path oa1p;
select current timestamp "now", current server "currentServer"
from sysibm.sysDummy1
;
$/genId/
$****************** generate ID3: 3 id lines **************************
$proc $@/genId3/
parse arg , mbr diM
if diM \== '' then
diM = ' als' diM
$@=[
-- $-[mbr$] für $rz/$dbSys vom $-[f('%t E um %t t')$]
-- generiert$-[diM$] durch rz4/dsn.source.tecSv(conSumGe)
-- alle Aenderung dortdrin ||||||
$]
$/genId3/
$****************** write rz?Sql from generated LCTLs *****************
$proc $@/rzSQL/ $*( brauchen wir nicht mehr .............
if $rzOne == '' then
call err 'rzSQL empty rzOne'
rz = word($rzOne, 1)
if $fun == 'c' then $@[
say 'rzSQL:' $rzOne '==>' $outLib'('rz'SQL)'
$;
$>- $outLib'('rz'SQL)'
$do wx=2 by 2 to words($rzOne) $@[
$$- '['word($rzOne, wx)']'
$@<--[$outLib'('word($rzOne, $wx+1)')'$]
$]
$;
call jWrite $csDist, "call csmCopy '"$outLib"("rz"SQL)' ,"
if $tstOut == '-' then
call jWrite $csDist, " , '"$rz"/DSN.DB2.LCTL("$rzDsn"SQL)'"
else
call jWrite $csDist, " , '"$rz"/"$outLib"("$rzDsn"SQL)'"
$] $*)
$= rzOne = $''
$/rzSQL/
$****************** generate controlSummary ***************************
$proc $@=/genConSum/
$@%[genId Control Summary$]
--*********************************************************************
$@ if \ $isElar then $@=[
--$'$$'s fehlende Fullcopies Tablespaces, letzte 8 Tage:
$] $@ else $@=[
--$'$$'s DXB - fehlende Fullcopies TS, letzte 8 Tage:
$]
--*********************************************************************
$@missFullCopies1
and
$@%[exclude PT * $]
$@%[missFullCopies2 8$]
commit;
--*********************************************************************
$@ if \ $isElar then $@=[
--$'$$'r fehlende RecoveryBase Tablespaces, letzte 8 Tage:
$] $@ else $@=[
--$'$$'s DXB - fehlende RecoveryBase Tablespaces, letzte 8 Tage:
$]
--*********************************************************************
$@% missBaseV2Beg older8d 8
and
$@% exclude = -vr *
$@% 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 wordPos($dbSys, DBOF) > 0 then $@=/conSuXBS/
--************************************************************
--$'$$'r fehlende Fullcopies XBS Tablespaces, letzte 2 Tage:
--************************************************************
$@% missBaseV2Beg older2d 2
and
$@%[setQDbTs = -vr $]
$@predBE
$@% missBaseV2End
$*( old ???????
with p as
(
select p.dbName db, p.tsName ts, p.partition pa, p.createdTs paCre
from sysibm.sysTablePart p
where
p.space <> -1 -- define=no is in space not spaceF |
AND
$@%[setQDbTs P$]
$@predBE
)
$@%[missFullBase current timestamp - 2 days $]
;
???????? old $*)
commit;
$/conSuXBS/
$@ if $hasXDoc then $@=/conSumXDoc/
$@xDocUnlErr
$@xDocRecErr
$@ if $isElar then $@=[
--************************************************************
--$'$$'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
;
$]
$/conSumXDoc/
$/genConSum/
$****************** generate DDLControl *******************************
$proc $@/genDDLCon/
$@%[genId 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 = '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 = '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'
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'
OR IP.STORTYPE = 'E')
ORDER BY CREATOR, IXNAME
WITH UR;
commit;
$@[ if $isElarCont then
$@gttNdPa
$]
--************************************************************
--$'$$' 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$]
$@[if $isElar then $@/elar7/
$@=[
and (ts.dbName not like 'XB%'
$]
if $isElarCont then $@=[
or ts.dbname in ( select db from session.ndPa )
$]
$@=[
)
$] $/elar7/
$]
ORDER BY pt.DBNAME, pt.tsNAME, PT.PARTITION
fetch first 999 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$]
$@[ if $isElar then $@[
$@=[
and (ix.dbName not like 'XB%' -- bis drop elar alt ???
$]
if $isElarCont then $@=[
or ix.dbname in ( select db from session.ndPa )
$]
$@=[
)
$] $]
$]
order by ix.creator, ix.name, ip.partition
fetch first 999 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
T TecSv SQL
* 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 'DACME%') -- Mail Heinz Bühler
AND NOT ($db LIKE 'QTXDB%') -- test kidi63
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, '*TK') > 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 $isTec then $@=[
AND $db NOT LIKE 'BMC%'
AND $db NOT LIKE 'DCMN00%' --Hat cloneTable Alter aufwendig
$]
$] else $@/excludeNotL/
if wordPos($dbSys, 'DBOF') > 0 & $q <> 'IX',
& pos($exF, '*T') > 0 then $@[
$@=[
AND NOT ($db = 'XC01A1P' and $ts <> 'A500A'
and ($ts LIKE 'A2%'or $ts LIKE 'A5%'))
-- EOS: Armin Breyer
AND NOT ($db = 'XR01A1P' AND $ts LIKE 'A2%' )
-- ERET: Armin Breyer
$]
if $exF = 'T' & $dbSys == 'DBOF' & $rz \== 'RQ2' then $@=[
AND NOT
$@predBE
$]
$]
if wordPos($rz, 'RZ4') > 0 & $exF == 'F' then $@[
if $q == IX then $@=[
AND NOT $db = 'DB2PMPDB' -- PMON KITD2
$] else $@=[
AND NOT ($db = 'DB2PMPDB'
AND $ts like 'ACCS%') -- PMON KITD2
AND NOT ($db = 'AC04A1P' AND $ts = 'SAC041A' ) -- ACF Gründler
$]
$]
if $dbSys = 'DP4G' then $@=[
AND NOT $db in ('DB2PDB', 'DB2PDB2', 'DB2PDB3') -- performance DB
$]
if $dbSys = 'DBOC' then $@=[
AND NOT ($db = 'DB2PDB') -- performance DB
AND NOT ($db = 'DB2XML') -- performance DB
$]
if $isElar then $@[
if $exF == 'K' then $@=[
and not ($q.dsName like 'XB.DIV.P0.%' -- bis drop elar alt ???
and translate(strip($q.dsName), '999999999', '012345678')
like '%.APROC.G9999V99' )
$] else $@=[
AND NOT ($db LIKE 'XB%') -- ELAR Dokumente
$]
$]
$/excludeNotL/
$@=[
----- end @proc exclude: excludes --- $exF --------------------------
$]
$/exclude/
$****************** 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 $@=/predBE/
($db = 'BE01A1P' and $ts like 'A0%' -- BE save
or $db = 'CD02A1P' and $ts = 'A600A')
$/predBE/
$****************** 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/
$****************** missing fullcopies neu ****************************
$@proc $@=/sesCopy/
--- temporary table fuer syscopy -------------------------------------
declare global temporary table session.copy
( db char(8), ts char(8), inst smallint, pa smallInt
, fulTy char(1), fulTst timestamp, fulPa smallInt
, incTy char(1), incTst timestamp, incPa smallInt
) on commit preserve rows;
create unique index session.txIx on session.copy
(db,ts, inst, pa)
include (fulTy, incTy, fulPa, fulTst)
;
select current timestamp from sysibm.sysDummy1;
insert into session.copy
with dsn_inline_opt_hint (table_name, join_method) as
(
values ('L2', 'SMJ')
)
, l1 (db, ts, inst, pa, ful, inc) as
(
select dbName, tsName, instance, dsNum
, max(case when ICTYPE IN ( $icTyBase
, $icTyDisc)
and not (ICTYPE = 'A' and sType <> 'A') -- part added
then char(timestamp) || c.icType || char(c.dsNum)
else '' end )
, max(case when ICTYPE IN ('I')
then char(timestamp) || c.icType || char(c.dsNum)
else '' end )
from sysibm.syscopy c
-- where timestamp > current timestamp - 50 days ?????
group by dbName, tsName, dsNum, instance
)
, l2 (db, ts, pa, inst, ful, inc) as
(
select * from l1
where ful <> '' or inc <> ''
)
, l3 (db, ts, inst, pa, ful, inc) as
(
select l.db, l.ts, l.inst, l.pa
, max(value(l.ful, ''), value(r.ful, '')
, '1111-11-11-11.11.11.111111 -99')
, max(value(l.inc, ''), value(l.ful, '')
, value(r.inc, ''), value(r.ful, '')
, '1111-11-11-11.11.11.111111 -99')
from l2 l
left join l2 r
on l.pa > 0 and r.pa = 0
and l.db = r.db and l.ts = r.ts and l.inst = r.inst
)
, laCo (db, ts, inst, pa, fulTy, fulTst, fulPa, incTy, incTst, incPa) as
(
select db, ts, pa, inst
, substr(ful, 27, 1)
, timestamp(left(ful, 26))
, smallInt(substr(ful, 28))
, substr(inc, 27, 1)
, timestamp(left(inc, 26))
, smallInt(substr(inc, 28))
from l3
)
select * from laCo
;
commit
;
select current timestamp from sysibm.sysDummy1;
$*(
;X;ect count(*) from laCo with ur;
insert into session.copy
with l as
(
select c.dbName db, c.tsName ts, c.instance inst
, c.dsNum, c.icType, c.timestamp tst
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.lowDsNum
end paFr
, case when s.partitions = 0 then 0
when c.lowDsNum <= 0 then c.dsNum
when c.highDsNum <= 0 then c.dsNum
else c.highDsNum
end paTo
from sysibm.sysCopy c
join sysibm.sysTableSpace s
on c.dbName = s.dbName and c.tsName = s.name
where ICTYPE IN ('A' ,'C', 'F', 'S', 'W', 'Y')
and not (ICTYPE = 'A' and sType <> 'A') -- part added
and not (ICTYPE in ('S', 'W', 'Y')
and timestamp > current timestamp - $logDisDelta)
)
, g as
(
select db, ts, inst, paFr, paTo
, max(char(tst) || ictype || dsNum) last
from l
group by db, ts, inst, paFr, paTo
)
select db, ts, inst, paFr, paTo
, smallInt(substr(last, 28)) dsNum
, substr(last, 27, 1) icType
, timestamp(substr(last, 1, 26)) tst
from g
;
$*)
select count(*) "copy count"
, count(distinct db || '.' || ts) "copy TS's"
, count(distinct db ) "copy DB's"
from session.copy
;
commit;
--*********************************************************************
--$'$$'s fehlende Fullcopies/Recoverybases, letzte 8 Tage $xDocNoTx
--*********************************************************************
with p as
(
select p.dbName db, p.tsName ts, p.partition pa, p.createdTs paCre
from sysibm.sysTablePart p
where
p.space <> -1 -- define=no is in space only not spaceF |
and
$@%[exclude P * $]
)
$@%[missFullBase current timestamp - 8 days $]
;
commit;
$/sesCopy/
$proc $@=/missFullB1/
, i(c, s, i, clBa, inTx) as
( select 'N', 1, 1, ' ', '' from sysibm.sysDummy1
union all select 'N', 2, 2, ' ', '2 only' from sysibm.sysDummy1
union all select 'Y', 1, 1, 'b', '1 base' from sysibm.sysDummy1
union all select 'Y', 1, 2, 'c', '2 clone' from sysibm.sysDummy1
union all select 'Y', 2, 1, 'c', '1 clone' from sysibm.sysDummy1
union all select 'Y', 2, 2, 'b', '2 base' from sysibm.sysDummy1
)
, l as
(
select p.*
, case when i.i is not null then i.i
else raise_error(70001, 'bad clone ' || s.clone) end inst
, i.inTx
, value(c.fulTy, ' ') fulTy
, value(c.fulPa, -99) fulPa
, value(c.fulTst, '1111-11-11-11.11.11') fulTst
, $@%[icTyTx value(c.fulTy, ' ') $]
fulTx
, value(c.incTy, ' ') incTy
, value(c.incPa, -99) incPa
, value(c.incTst, '1111-11-11-11.11.11') incTst
, $@%[icTyTx value(c.incTy, ' ') $]
incTx
, s.dbid, s.psid
from p
join sysibm.sysTablespace s
on p.db = s.dbName and p.ts = s.name
and s.ntables <> 0
join i on i.c = s.clone and i.s = s.instance
left join session.copy c
on c.db = p.db and c.ts = p.ts and c.inst = i.i and c.pa = p.pa
)
$/missFullB1/
$proc $@=/missFullBase/
$arg dayLim
$@missFullB1
select substr(db, 1, 8) db
, substr(left(ts, 8) || ' ' || inTx, 1, 16) "ts instanc"
, substr(right(' ' || pa, 5) || right(' ' || dsNum, 5)
, 1, 10) " part dsNu"
, coalesce(iTx, ty) "icType"
, tst
from m
left join ict on iTy = ty
where ty is null or not
((ty = 'F' and tst
> $dayLim)
or (paCre > current timestamp - 24 hours))
order by 1, 2, 3
with ur
$/missFullBase/
$proc $@=/genBesenWagen/
$@% genId BesenWagen $xDocTx
$@xDocUnlErr
$@xDocRecErr
--*********************************************************************
--FixBesenwagen fuer $xDocTx
--*********************************************************************
with x as -- without with sql -101 sql too complex .....
(
select db, ts, pa, stage, staTb, conSum, basTst, recFun
, max(pSpc, rSpc, 0) spc, recov
$@xDocVRecovLoad
order by value(pSpc, 0), db, ts, pa
)
select char(db, 8) db, char(ts, 8) ts, pa
, substr(fosFmte7(spc * 1024.0), 1, 7) spaceB
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(conSum, 1, 40) recoveryState
from x
where recFun = '?'
and not ( (recov='ok' and basTst > current timestamp - 14 days)
$@ if $isElar then $@=[
or stage in ('-w', 'UL', 'DL')
-- or erRec like '%notInDB2%'
-- or erRec like '%dataChangeV11%' -- only if dataChange>
$] $@ else $@=[
-- or erRec like '%copyUpdate>incTst%' -- only if >fulTst
-- or erRec like '%dataChangeV11>unl%'--only if dataCange>
-- or erRec like '%inc180515>unl%' -- err in tecSv
or conSum like '? inc180515>unl%'
$]
)
;
$/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
, case when recov = 'ok' then '$txtLim' else recov end recov
, basTyTx
, basPa
, basTst
from oa1p.vQz005Recover
WHERE ( recov not in ('ok', 'defNo', 'noTb')
or ( recov = 'ok' and basTst
< current timestamp - $dayLim days )
)
$/missBaseV2Beg/
$proc $@=/missBaseV2End/
order by 1, 2, 3
with ur
;
$/missBaseV2End/
$proc $@=/xDocRecErr/
--*********************************************************************
-- $xDocTx: Summary fehlende Recoverybases / Unloads
--*********************************************************************
with z as
(
select r.*
, case when recLR = '2' and recFun = 'r'
then conSum || ' ' || recUnl else conSum end conSu2
, max(pSpc, rSpc, 0) spc
$@xDocVRecovLoad
)
select substr(fosFmtE7(sum(spc) * 1024.0)
|| right(' ' || count(*), 8), 1, 15)
"spaceBy count"
, stage
, substr(conSu2, 1, 70) recoveryState
from z
group by stage, conSu2
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
-- '?' recovery not possible / doubtful
-- substr(3...) recover state / warning / error
;
--*********************************************************************
--$'$$'s fehlende Fullcopies/Recoverybases, letzte 8 Tage $xDocTx
--*********************************************************************
with z as
(
select r.*
, case when recLR = '2' and recFun = 'r'
then conSum || ' ' || recUnl else conSum end conSu2
$@xDocVRecovLoad
order by db, ts, pa
)
select char(db, 8) db, char(ts, 8) ts, pa
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(conSu2, 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.*
from z
where recFun = '?'
$@ if $isElar then $@=[
and stage not in ('-w')
$]
$@stageInfo
;
$/xDocRecErr/
$proc $@=/xDocUnlErr/
--************************************************************
-- Statistik unload table $xDocBrTx
--************************************************************
$@xDocUnlUE
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
;
$@ if $isElar then $@=[
--- elar NDBS: neuer Elar Design seit 2013/14 -------------------------
$] $@ else $@=[
--- XC/XR Kontrolle AuditPendenz 2015 ---------------------------------
$]
--************************************************************
--$'$$'r $xDocTx Fehler in stageTables
--************************************************************
$@xDocUnlUE
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
$@stageInfo
;
commit;
$/xDocUnlErr/
$proc $@=/xDocUnlUE/
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', 'RU', 'FZ', 'UL', 'MI', '-r')
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
)
$/xDocUnlUE/
$proc $@=/xDocVRecovLoad/
$@ if $useLgRn then $@=[
from oa1p.vQz005RecovLoadLgRn r
$] $@ else $@=[
from oa1p.vQz005RecovLoad r
$]
$@ if $isElar then $@=[
where db like 'XB%'
$] $@ else $@=[
where (db like 'XC%' or db like 'XR%')
$]
$/xDocVRecovLoad/
$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/
$proc $@=/missFullBaUnl/
with p as
(
select db, ts, pa, stage, staUpd, staTb
, unlTst, unl, punTst pun
, info infoP
, err errUnl
, p.space pSpc
from oa1p.tqz005TecSvUnload u
join sysibm.sysTablePart p
on u.db = p.dbName and u.ts = p.tsName and u.pa = p.partition
where pa >= 0
$@ if $isElar then $@=[
and ts not in -- gestoppte TS, im Loeschprozess
( 'SF710141'
, 'SF710142'
, 'SF710143'
, 'SF71014H'
, 'SF760141'
, 'SF760142'
, 'SF760143'
, 'SF76014H'
)
$]
)
$@missFullB1
, eR as -- error for db2Recovery oder unloadRecovery
(
select
case when l.pSpc = -1 then '' -- define no = no vsam
when fulTy not in ($icTyBase)
then 'lastFul=' || fulTx
when fulTst < current timestamp - 15 days
then 'fulTst<-15d'
when fulTst < current timestamp - 8 days
then 'fulTst<-8d'
when r.dbName is null then 'noRTS'
else ''
end erReD
, strip(case
when unl is null or unl = '' then 'noUnload'
when unlTst is null or unlTst < current timestamp - 100 years
then 'unlTstNull'
when r.dbName is null then 'noRTS'
when r.lastDataChange > l.unlTst then 'dataChange>unl'
when r.copyUpdatetime > unlTst then 'copyUpdate>unlTst'
when fulTst > unlTst
$@ if \ $isElar then $@=[
and (date(incTst) <> '18.05.2015' or fulTy <> 'F')
$]
then 'unlTst<ful='||fulTx
when incTst > unlTst and incTy='I'
$@ if \ $isElar then $@=[
and date(incTst) <> '18.05.2015'
$]
then 'unlTst<inc='||incTx
when r.copyChanges <> 0 then 'copyChanges<>0'
when r.copyUpdatedPages <> 0 then 'updatedPages<>0'
$*(
when r.copyUpdatetime > fulTst and fulTy = 'F'
then 'copyUpdate>ful='||fulTx
when r.copyUpdatetime > incTst and incTy = 'I'
then 'copyUpdate>inc='||incTx
$*)
when r.copyUpdatetime is not null then 'copyUpdateNotNull'
when r.lastDataChange > l.fulTst and l.fulTy not in('A',' ')
$@ if \ $isElar then $@=[
and unlTst > '2015-09-12-12.00.00'
$]
then 'dataChange>ful='||fulTx
when fulTy <> 'F' then 'lastFul=' || fulTx
$*(
$@ if $isElar then $@=[
when incTy = 'I' and incTst > '2015-09-12-12.00.00'
then 'lastInc1509=' || incTx
$]
$*)
when incTst > unlTst
$@ if \ $isElar then $@=[
and date(incTst) <> '18.05.2015'
$]
then 'incTst>unlTst'
when r.lastDataChange is null and unlTst
< '2015-04-15-00.00.00' then 'dataChangeV11>unl'
when lastDataChange is null
and l.incTst < '2015-04-15-00.00.00'
then 'dataChangeV11>inc=' || incTx
$@ if \ $isElar then $@=[
when incTst > unlTst
and date(incTst) = '18.05.2015'
then 'inc180515>unl'
$]
else ''
$@ if $useLgRn then $@=[
end || case
when unl is null or unl = '' or unlTst is null
or unlTst < current timestamp - 100 years then ''
when lr.start > unlTst then ' lgRn>unl'
when lr.start is null then ' lgRnNone'
else ''
$]
end) erReU, l.*, r.*
from l left join sysibm.sysTableSpaceStats r
on l.dbId = r.dbId and l.psId = r.psId
and l.pa = r.partition and l.inst = r.instance
and l.db = r.dbName and l.ts = r.name
$@ if $useLgRn then $@=[
left join oa1p.tqz004TecSvLgRn lr
on l.db = lr.db and l.ts = lr.ts and l.pa = lr.pa
$]
)
, e as
(
select eR.*
, strip(case
$@ if $isElar then $@=[
-- when stage = ' w' then ''
when stage in ('UL', 'DL', ' w') and erReU <> ''
then erReU
when stage in ('UL', 'DL', ' w') then ''
when erReD <> '' then erReD
$] $@ else $@=[
when stage in ('IN', 'UL')
and erReD <> '' and erReU <> ''
then erReU || ' ' || erReD
when stage = 'IN' and unl <> '' and (staUpd is null
or staUpd < current timestamp - 24 hour)
then 'stillUnlAft24h'
when stage not in ('IN', 'UL') and erReD <> '' then erReD
$]
else ''
end ) erRec
from eR
)
$/missFullBaUnl/
$proc $@=/xDocRecErrV1/
--*********************************************************************
--Summary fehlende Fullcopies/Recoverybases, letzte 8 Tage $xDocBrTx
--*********************************************************************
$@missFullBaUnl
select count(*), stage
, substr(strip(erRec || ' ' || errUnl), 1, 70) err
-- , min(err), max(err)
-- , min(unl), max(unl)
from e
group by stage, strip(erRec || ' ' || errUnl)
order by 2, 3
;
--*********************************************************************
--$'$$'s fehlende Fullcopies/Recoverybases, letzte 8 Tage $xDocBrTx
--*********************************************************************
$@missFullBaUnl
select db, ts, pa
, substr(left(stage, 2) || ' ' || staTb, 1 , 5) stage
, substr(strip(erRec || ' ' || errUnl), 1, 40) err
, substr(case when fulTy <> ' '
then fulTy || ' ' || char(fulTst) else '' end, 1, 21)
"last fullCopy"
, substr(case when unl <> '' then char(unlTst) else '' end
, 1, 10) "unload"
-- , e.*
from e
where not (erRec = ''
$@ if $isElar then $@=[
or stage in (' w')
or erRec like '%dataChangeV11%' -- only if dataCange>
or ( stage in ('UL', 'DL')
and ( erRec like '%lastFul= =missi%'
or erRec like '%lastFul=A=addPa%'
or erRec like '%lastFul=S=LoaRp%'
or erRec like '%lastFul=Y=LoaRs%'
) )
$] $@ else $@=[
or erRec like '%copyUpdate>incTst%' -- only if >fulTst
or erRec like '%dataChangeV11>unl%' -- only if dataCange>
or erRec like '%inc180515>unl%' -- err in tecSv
$]
)
order by db, ts, pa
--
-- columns
$@stageInfo
-- last fullcopy: icType und timestamp
-- unload : Datum
;
$*( ???????????? altes ndbs
--*********************************************************************
--$'$$'r XB ndbs - fehlende Fullcopi/Recoverybase, letzte 8 Tage:
--*********************************************************************
with p as
(
select n.*, p.createdTs paCre
from session.ndPa n
join sysibm.sysTablePart p
on n.db = p.dbName and n.ts = p.tsName and n.pa = p.partition
and p.space <> -1 -- define=no is in space not spaceF |
where stage not in ('UL', 'DL')
)
$@%[missFullBase 1 current timestamp - 8 days $]
;
commit;
--*********************************************************************
-- ndbs: temporary table für unloads
declare global temporary table session.unl
( db char(8), ts char(8), pa smallint, unl char(44), err varChar(30)
) on commit preserve rows;
create unique index session.unlIx on session.unl (db,ts, pa)
include (unl)
;
insert into session.unl
with f as
(
select substr(earess, 4, 8) db
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13)) ts
, partNumber pa, eaRess, '1' ptb
from BUA.TXBC021 t
where EYRESS = 5000 and ESRESS = 0
union all select substr(earess, 4, 8) db
, substr(earess, 13
, min(8, locate('.', earess || '.', 13) - 13)) ts
, partNumber pa, eaRess, 's' ptb
from BUA.TXBC021s t
where EYRESS = 5000 and ESRESS = 0
)
, g as
(
select db, ts, pa, min(eaRess) eaRess, count(*) cnt
, min(pTb) || '+' || max(pTb) pTb
from f
group by db, ts, pa
)
select db, ts, pa, eaRess
, case when earess not like 'XB.XB%'
then 'eaRess not XB.XB% ' || pTb
when locate('.', earess, 4) <> 12
then 'eaRess db len ' || pTb
when locate('.', earess, 13) not between 14 and 21
then 'eaRess ts len ' || pTb
when cnt <> 1 then 'duplicates ' || cnt || ' ' || pTb
else '' end err
from g
with cs
;
commit;
--************************************************************
--$'$$'r XB ndbs - ungueltige Einträge in BUA.TXBC021/S
--************************************************************
select *
from session.unl
where err <> ''
order by db, ts, pa
;
--************************************************************
--$'$$'r XB ndbs - fehlende unloads fuer stage UL
--************************************************************
select p.*
from session.ndPa p
left join session.unl u
on p.db = u.db and p.ts = u.ts and p.pa = u.pa
where stage = 'UL' and u.db is null
order by db, ts, pa
;
--************************************************************
--$'$$'r XB ndbs - nicht katalogisierte Unloads
--************************************************************
select p.db, p.ts, p.pa, p.stage, u.unl
from session.ndPa p
join session.unl u
on p.db = u.db and p.ts = u.ts and p.pa = u.pa
and p.stage = 'UL'
where s100447.dslocate(unl) is null
order by db, ts, pa
;
commit;
????????? altes NDBS $*)
$/xDocRecErrV1/
$proc $@=/icTyTx/
$arg tyCo
$ct icTyBase = 'A','C','F','R','X' $** recovery base
$ct icTyDisc = 'P','S','W','Y' $** recovery discontinuty
value($tyCo || '='
|| case $tyCo
when ' ' then 'missing'
when 'A' then 'addPart'
when 'C' then 'create'
when 'F' then 'fulCopy'
when 'I' then 'incCopy'
when 'P' then 'recPIT'
when 'R' then 'LoaRpLog'
when 'S' then 'LoaRpLoNo'
when 'W' then 'ReorgLoNo'
when 'X' then 'ReorgLog'
when 'Y' then 'LoaRsLoNo'
else '???'
end, '-=null')
$/icTyTx/
$****************** create and fill gtt if not done yet ***************
$proc $@/gttNdPa/
if \ $gttNdPaDone then $@=/gttNdPaSql/
$= gttNdPaDone = 1
--- global table fuer Partitionen, stage, segment ---------------------
declare global temporary table session.ndPa
( db char(8), ts char(8), pa smallInt, stage char(2), seg char(6)
) on commit preserve rows;
create unique index session.ndPaIx on session.ndPa (db,ts, pa)
include (stage, seg)
;
insert into session.ndPa
select t.dbName, t.tsName
, r.partNumber, r.stage, r.storageArea || r.segment
FROM sysibm.systables t
join BUA.TXBI003 R
on substr(t.name, 3, 3) = r.storageArea
and substr(t.name, 6, 3) = r.segment
where t.creator = 'BUA'
and t.name like 'XB%'
;
commit
;
--- counts fuer ndbs --------------------------------------------------
select count(*) "ndbs Parts"
, count(distinct db || '.' || ts) "ndbs TS's"
, count(distinct db ) "ndbs DB's"
from session.ndPa
;
commit
;
$/gttNdPaSql/
$/gttNdPa/
$****************** tecSave sql ***************************************
$proc $@=/tecSvSql/
$@[
parse arg , tsF tit
$=tsF=- tsF
if tsF == 'i' then $@[
$=tsTxt = incremental
$] else if tsF == 'f' then $@[
$=tsTxt = full
$] else $@[
call err 'bad fun tsF' tsF 'in tecSvSql'
$]
$]
#HCCD (TS) RTS $tsTxt IMAGE COPY
$@%[genId3 - tit$]
SELECT 'DI,PI,PA,IN' , DBID , PSID , PARTITION , INST
from
( select ts.dbName, ts.name, p.partition
, c.inst, ts.dbid, ts.psid
, overlay(case
when c.inst is null
then raise_Error(70001, 'c.inst null '
|| ts.dbName || '.' || ts.name)
when ts.nTables < 1 then 'n noTables ' || ts.nTables
when p.space = -1 then 'n defineNo ' || p.space
$** let utility figure out define no or yes
$** but dbAnalyzer always produces RTS not found messages
$** ==> unfortunately not a good idea |
when f.icType is null then 'f f.icType null'
when f.icType <> 'F' then 'f f.icType ' || f.icType
when f.dsNum <> p.partition then 'f multiPart'
when f.timestamp < 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 i.timestamp < r.copyLastTime - 60 seconds
then 'f i << r.copyLast'
when r.nactive * 0.1 <= r.copyupdatedpages
then 'f updates'
when i.icType is null then 'f i.icType null'
when i.icType not in ('I','F') then 'i i.icType '||i.icType
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, case when ts.clone <> 'Y' then ' '
when ts.instance = c.inst then ' base '
else ' clone'
end, 2, 0, octets) what
from sysibm.sysTablespace ts
left join -- clone handling: add instances
( select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
) c (inst)
on ts.instance = c.inst or ts.clone = 'Y'
join sysibm.sysTablePart p
on ts.dbName = p.dbName and ts.name = p.tsName
left join sysibm.sysTableSpaceStats r
on ts.dbName = r.dbName and ts.name = r.name
and ts.dbid = r.dbid and ts.psid = r.psid
and p.partition = r.partition and r.instance = c.inst
left join -- newest incremental or full copy or log discontinuity
( select c.*
, row_number() over(partition by dbName, tsName, dsNum
, instance
order by timestamp desc) rn
from sysibm.sysCopy c
where c.icType not IN ('A', 'B', 'C', 'D', 'M', 'Q')
) i on i.rn = 1
and ts.dbName = i.dbName and ts.Name = i.tsName
and p.partition = i.dsNum
and i.instance = c.inst
left join -- newest full copy or log discontinuity
( select c.*
, row_number() over(partition by dbName, tsName, dsNum
, instance
order by timestamp desc) rn
from sysibm.sysCopy c
where c.icType not IN ('A', 'B', 'C', 'D', 'I', 'M', 'Q')
) f on f.rn = 1
and ts.dbName = f.dbName and ts.Name = f.tsName
and p.partition = f.dsNum
and f.instance = c.inst
) s
where what like '$tsF%%' $** doppelte % 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 20150923 09:11:33