zOs/SQL/TECSVDDY
$#@
$*( Versuch mit einer View mit/ohne Delta
geht zwar aber zu komplex |||
create views
oa1p.vQZ005Recover recover Info
if xDocs additionally
oa1p.tQZ005TecSvUnload table for unloads of Elar, EOS, eRet
oa1p.vQZ005RecovLoad recovery incl. load of xDoc tables
oa1p.vQZ005RecovLoadLgRn plus lgRn check
24. 9.15 Walter neu
$*)
$>. fEdit()
$=useLgRn = 1
$=useInst = 0 $** 1 = if instance in index of sysCopy otherwise 0
$=dropCreTb = 0
$=logDisDelta = 10 minutes
call iiIni
if 0 then $@=[
set current sqlid = 'S100447';
drop view oa1p.vqz002Recover;
drop view oa1p.vqz005ConSum;
drop view oa1p.vqz005Recover;
$=isElar = 0
$=dbSys=DBOF
$@vRecover
$@vRecovLoad
$] else $@=/allRZ/
$@jobHead
$@ oldRz = ''
$do ix=1 while iiIxvPut(ix) $@=/envLoop/
$@ if $rz <> oldRz then $@[
if oldRz <> '' then
$$ }{
$@jobSub
oldRz = $rz
$]
$= xDocs =- wordPos($dbSys, 'DVBP DBOF') > 0 $*+
| ($dbSys = DEVG & $rz = RZZ)
$= isElar =- $dbSys = 'DVBP'
$@jobSql
set current sqlid = 'S100447';
drop view oa1p.vqz005Recover;
drop view oa1p.vqz005ConSum;
$@ if $dropCreTb then $@[
if $xDocs then
$@tUnloadDrop
if $useLgRn then
$@tLgRnDrop
if $xDocs | $useLgRn then
$$ commit;
$]
--#SET MAXERRORS 0
$@ if $dropCreTb then $@[
if $xDocs then
$@tUnload
if $useLgRn then
$@tLgRn
$]
$@vRecover
$@ if $xDocs then
$@vRecovLoad
$@ if $dropCreTb & $xDocs then
$@loadUnload
$/envLoop/
$/allRZ/
$proc $@=/vRecover/
create view oa1p.vqz005Recover as
with two (i) as
( -- do not use sysDummy1 and union all
-- optimizer would choose very bad accessPath |||
select row_number() over () from sysibm.sysDatabase
fetch first 2 rows only
)
, s2 as
(
select s.dbName db, s.name ts
, s.instance instBa
, two.i inst
, s.pgSize, s.dbId, s.psId , s.nTables
, clone
from sysibm.sysTableSpace s
join two on s.clone = 'Y' or two.i = s.instance
)
, s as
(
select s2.*
$@ if $useInst then $@=[
, substr(case
when inst = 1 and clone = 'N' then '1'
when clone = 'N' then '' || inst || 'only'
when instBa = inst then '' || inst || 'base'
else '' || inst || 'clone' end, 1, 6) insTxt
$] $@ else $@=[
, substr(case
when inst = 1 and clone = 'N' then '1'
when clone = 'N' then '' || inst || 'only?'
when instBa = inst then '' || inst || 'base?'
else '' || inst || 'clon?' end, 1, 6) insTxt
$]
, (
$@% selCopy s2.db s2.ts 0 s2.inst $logDisDelta
) ic0
from s2
)
, p2 as
(
select s.*, p.partition pa
, value(p.space, 0) pSpc
, (
$@% selCopy s.db s.ts p.partition two.i $logDisDelta
) icPa
from s
join sysibm.sysTablePart p
on s.db = p.dbName and s.ts = p.tsName
)
, p3 as
(
select db, ts, pa, inst, insTxt
, pgSize, nTables, dbId, psId, pSpc, instBa
, max(value(substr(icPa, 33, 32), '1111-11-11-11.11.11.111111- ')
,value(substr(ic0 , 33, 32), '1111-11-11-11.11.11.111111- ')
, '1111-11-11-11.11.11.111111- ') basTTP
, max(value(substr(icPa, 1, 32), '1111-11-11-11.11.11.111111- ')
,value(substr(ic0 , 1, 32), '1111-11-11-11.11.11.111111- ')
, '1111-11-11-11.11.11.111111- ') incTTP
from p2
)
, p4 as
(
select p3.*
, substr(basTTP, 27, 1) basTy
, substr(basTTP, 28, 5) basPa
, timestamp(substr(basTTP, 1, 26)) basTst
, substr(incTTP, 27, 1) incTy
, substr(incTTP, 28, 5) incPa
, timestamp(substr(incTTP, 1, 26)) incTst
from p3
)
, p5 as
(
select p4.*
, case when pSpc = -1 then 'defNo'
when nTables = 0 then 'noTb'
when basTy not in ('A','C','F','R','X') then 'logDisc'
when basTst < current timestamp - 23 days then 'tooOld'
else 'ok' end recov
, basTy || case basTy
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 basTyTx
from p4
)
, d2 as
(
select p5.*
, case when recov <> 'logDisc'
or basTst < current timestamp - 10 minutes then basTTP
else value ( (
$@% selCopy p5.db p5.ts p5.pa p5.inst 10 minutes
) , '1111-11-11-11.11.11.111111- ') end deltaTTP
from p5
)
, d3 as
(
select d2.*
, substr(deltaTTP, 27, 1) deltaTy
, substr(deltaTTP, 28, 5) deltaPa
, timestamp(substr(deltaTTP, 1, 26)) deltaTst
, case when deltaTTP = basTTP
then basTyTx else substr(deltaTTP, 27, 1) end deltaTyTx
from d2
)
, d4 as
(
select d3.*
, case when basTTP = deltaTTP then recov
when deltaTy not in ('A','C','F','R','X') then 'logDisc'
when deltaTst < current timestamp - 23 days then 'tooOld'
else 'ok' end delta
from d3
)
select db, ts, pa, inst, recov, delta
, basTyTx, basPa, basTst, basTy
, incTy, incPa, incTst
, deltaPa, deltaTst, deltaTy, deltaTyTx
, pgSize, pSpc, dbId, psId, insTxt, instBa
from d4
;
$/vRecover/
$proc $@=/selCopy/
$arg selCopyDb selCopyTs selCopyPa selCopyInst selCopyDelta
select max( substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
|| max(case when icType <> 'I'
then substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5) else '' end )
from sysibm.sysCopy c
where $selCopyDb = c.dbName
and $selCopyTs = c.tsName
$@ if $selCopyPa = 0 then $@=[
and c.dsNum = $selCopyPa
$] $@ else $@=[
and c.dsNum = $selCopyPa and $selCopyPa > 0
$]
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
$@ if $useInst then $@=[
and (c.instance = $selCopyInst or icType in ('A', 'C'))
$]
$@ if $selCopyDelta <> '' then $@=[
and not (ICTYPE in('P','S','W','Y')
and c.timestamp > current timestamp
- $selCopyDelta)
$]
$/selCopy/
$proc $@=/vRecovLoad/
create view oa1p.vQz005RecovLoad as
with u as
(
select p.*
, value(stage, '') stage
, value(staUpd, '1111-11-11-11.11.11') staUpd
, value(staTb, '') staTb
, value(unlTst, '1111-11-11-11.11.11') unlTst
, value(case when trunc_timestamp(unlTst, 'ddd') = unlTst
then (unlTst + 1 day) - 0.000001 seconds else unlTst end
, '1111-11-11-11.11.11') unlTsUp
, value(unl, '') unl
, value(punTst, '1111-11-11-11.11.11') punTst
, value(pun, '') pun
, value(u.info, '') infoUnl
, value(u.err, '') errUnl
from oa1p.vQZ005Recover p
left join oa1p.tqz005TecSvUnload u
on u.db = char(p.db, 8)
and u.ts = char(p.ts, 8)
and u.pa = p.pa and u.pa >= 0
)
, e2 as
(
select u.*
, value(real(r.nActive) * u.pgSize, 0) rSpc
, case when recov = 'ok' and basTst < current timestamp - 8 day
then 'older8d' else recov end rec8d
, case when delta = 'ok' and deltaTst < current timestamp - 8 day
then 'older8d' else delta end delta8d
, case when stage = '' then 'r'
$@ if $isElar then $@=[
when ts in -- gestoppte TS, im Loeschprozess
( 'SF710141'
, 'SF710142'
, 'SF710143'
, 'SF71014H'
, 'SF760141'
, 'SF760142'
, 'SF760143'
, 'SF76014H'
) then '-'
-- when stage = '-w' then '-m'
when stage in ('UL', 'DL', '-w') then 'l'
$] $@ else $@=[
when stage in ('IN', 'UL') then '2'
$]
else 'r'
end recLR
, case
when unl = '' then 'noUnload'
when unlTst < current timestamp-100 year
then 'unlTstNull'
when r.dbName is null then 'noRTS'
when r.lastDataChange > u.unlTsUp then 'dataChange>unl'
when r.copyUpdatetime > unlTsUp then 'copyUpdate>unlTst'
when BasTst > unlTsUp
$@ if \ $isElar then $@=[
and (date(incTst) <> '18.05.2015' or basTy <> 'F')
$]
then 'unlTst<ful='||basTyTx
when incTy='I' and unlTsUp < incTst
$@ if \ $isElar then $@=[
and date(incTst) <> '18.05.2015'
$]
then 'unlTst<incCopy'
when r.copyChanges <> 0 then 'copyChanges<>0'
when r.copyUpdatedPages <> 0 then 'updatedPages<>0'
$*(
when r.copyUpdatetime > BasTst and basTy = 'F'
then 'copyUpdate>ful='||basTyTx
when r.copyUpdatetime > incTst and incTy = 'I'
then 'copyUpdate>incCopy'
$*)
when r.copyUpdatetime is not null then 'copyUpdateNotNull'
when r.lastDataChange > u.BasTst and u.basTy not in('A','-')
$@ if \ $isElar then $@=[
and unlTst > '2015-09-12-12.00.00'
$]
then 'dataChange>ful='||basTyTx
when basTy <> 'F' then 'lastFul=' || basTyTx
when incTy = 'I' and incTst > unlTsUp
$@ 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 u.incTst < '2015-04-15-00.00.00'
then 'dataChangeV11>incCopy'
$@ if \ $isElar then $@=[
when incTy = 'I' and incTst > unlTsUp
and date(incTst) = '18.05.2015'
then 'inc180515>unl'
$]
else 'ok'
end recUnl
from u
left join sysibm.sysTableSpaceStats r
on u.dbId = r.dbId and u.psId = r.psId
and u.pa = r.partition -- and u.inst = r.instance
and u.db = r.dbName and u.ts = r.name
)
, e3 as
(
select e2.*
, case when recUnl in ( 'dataChangeV11>unl'
, 'dataChangeV11>incCopy'
$@ if $isElar then $@=[
, 'lastFul=A=addPart'
, 'lastFul=C=create'
, 'lastFul=S=LoaRpLoNo'
, 'lastFul=Y=LoaRsLoNo'
$]
) then 'ok ' || recUnl else recUnl end
|| case when stage = 'IN' and unl <> '' and
staUpd < current timestamp - 24 hour
then ' stillUnlAft24h' else '' end okUnl
from e2
)
, e4 as
(
select e3.*
, case when recLR = '-' then '-'
when recLR in ('r', '2')
and recov in ('defNo', 'noTb') then '-'
when recLR in ('r', '2') and rec8d = 'ok' then 'r'
when recLR in ('2', 'l') and left(okUnl,2)='ok' then 'l'
else '?'
end recFun
from e3
)
, e5 as
(
select e4.*
, case when recLr in ('r', '2') and delta8d = 'ok'
then 'r' else recFun end deltaFun
from e4
)
, e as
(
select e5.*
, strip(recFun || ' ' || case
when recFun = '-' then ''
when recFun = 'r' then rec8d ||':'|| basTyTx
when recFun = 'l' then recUnl
when recFun <> '?' then '???recFun???'
when recLR = '2' then recUnl || ' ' || rec8d||':'|| basTyTx
when recLR = 'r' then rec8d
when recLR = 'l' then recUnl
else recLR ||'?'|| recUnl ||'?' || rec8d ||':'|| basTyTx
end
|| case when stage = 'IN' and unl <> '' and
staUpd < current timestamp - 24 hour
then ' stillUnlAft24h' else ''
end || ' ' || errUnl) conSum
, recFun || case when recLR in ('r', '2')
then ' ' || rec8d || ':' || basTyTx else '' end
|| case when recLR in ('l', '2') then ' ' || okUnl
else '' end || ' ' || errUnl recErr
, deltaFun || case when recLR in ('r', '2')
then ' ' || delta8d || ':' || deltaTyTx
else '' end
|| case when recLR in ('l', '2') then ' ' || okUnl
else '' end || ' ' || errUnl deltaErr
from e5
)
select *
from e
;
$@ if $useLgRn then $@=[
create view oa1p.vqz005RecovLoadLgRn as
with l2 as
(
select r.*
, case when unl = ''
or unlTst < current timestamp - 100 years then ''
when l.start > unlTst then 'lgRn>unl'
when l.start is null then 'lgRnNone'
else ''
end lgRn
from oa1p.vqz005RecovLoad r
left join oa1p.tqz004TecSvLgRn l
on r.db = l.db and r.ts = l.ts and r.pa = l.pa
)
, l3 as
(
select l2.*
, case when recFun = 'l' and lgRn not in ('', 'lgRnNone')
then '?' else recFun end lgRecFun
, case when deltaFun = 'l' and lgRn not in ('', 'lgRnNone')
then '?' else deltaFun end lgDeltaFun
from l2
)
select db, ts, pa, stage, staUpd, staTb, pSpc, rSpc, basTy, basTst
, recLr, recov, lgRn
, unl, unlTst
, strip(recUnl || ' ' || lgRn) recUnl
, lgRecFun recFun
, lgDeltaFun deltaFun
, strip(lgRecFun || substr(recErr, 2)
|| case
when recFun = 'l' or (recFun = '?' and recLR <> 'r')
then ' ' || lgRn else '' end) recErr
, strip(lgDeltaFun || substr(deltaErr, 2)
|| case
when deltaFun = 'l' or (deltaFun='?' and recLR <> 'r')
then ' ' || lgRn else '' end) deltaErr
from l3
;
$]
$/vRecovLoad/
$proc $@=/tUnloadDrop/
alter table oa1p.tQz005TecSvUnload drop restrict on drop;
drop tablespace qz01a1p.a005a;
$/tUnloadDrop/
$proc $@=/tUnload/
----- ddl tecSv: xDocs: unload DSNs fuer ControlSummary ---------------
CREATE TABLESPACE a005a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255;
CREATE TABLE OA1P.tqz005tecSvUnload
( db char(8) not null
, ts char(8) not null
, pa smallint not null
, stage char(2) not null
, staUpd timestamp not null
, staTb char(2) not null
, unlTst timestamp not null
, unl char(44) not null
, punTst timestamp not null
, pun char(44) not null
, info varchar(70) not null
, err varchar(70) not null
)
in qz01a1p.a005a
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
WITH RESTRICT ON DROP
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ005a1
ON oa1p.tqz005TecSvUnload
(db, ts, pa)
include(stage)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
$/tUnload/
$proc $@=/tLgRnDrop/
alter table oa1p.tQz004TecSvLgRn drop restrict on drop;
drop tablespace qz01a1p.a004a;
$/tLgRnDrop/
$proc $@=/tLgRn/
CREATE TABLESPACE a004a
IN QZ01A1P
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 5
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID unicode
DEFINE YES
MAXROWS 255;
;
CREATE TABLE OA1P.tqz004tecSvLgRn
( db char(8) not null
, ts char(8) not null
, pa smallint not null
, start timestamp not null
, sLrsn timestamp not null
, cnt int not null
)
in qz01a1p.a004a
AUDIT NONE
DATA CAPTURE changes
CCSID unicode
WITH RESTRICT ON DROP
NOT VOLATILE;
CREATE UNIQUE INDEX oa1p.iQZ004a1
ON oa1p.tqz004TecSvLgRn
(db, ts, pa)
include(start)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
$/tLgRn/
$proc $@=/loadUnload/
//L$dbSys EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99,
// PARM='%tecSvUnl $dbSys'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSPROC DD DISP=SHR,DSN=DSN.DB2.EXEC
//SYSTSIN DD DUMMY
//U$dbSys EXEC PGM=DSNUTILB,TIME=1440,REGION=0M,
// PARM=($dbSys,'TECSVDDL.RUNSTA')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
LISTDEF LST INCLUDE TABLESPACE QZ01A1P.A005A PARTLEVEL
RUNSTATS TABLESPACE LIST LST
INDEX(ALL)
REPORT NO UPDATE ALL
SHRLEVEL CHANGE
$/loadUnload/
$proc $@=/jobHead/
//TECSVDDL JOB (CP00,KE50),'DB2 DDL',
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2
//*MAIN CLASS=LOG
$/jobHead/
$proc $@=/jobSub/
//***** submit job to $rz *****************************************
//SUB$rz EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD SUBSYS=(CSM,'SYSTEM=$rz,SYSOUT=(A,INTRDR)')
//SYSUT1 DD *,DLM='}{'
$@jobHead
$/jobSub/
$proc $@=/jobSql/
//*---- execute ddl in $rz/$dbSys isElar=$isElar ------------------
//S$dbSys EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN DD *
DSN SYSTEM($dbSys)
RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD DUMMY
//SYSIN DD *
$/jobSql/