zOs/SQL/TECSVDD3
$#@
$>. fEdit()
$=useLgRn = 0
$=dropCreTb = 0
call iiIni
if 0 then $@=[
set current sqlid = 'S100447';
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 DP4G') > 0
$= isElar =- $dbSys = 'DVBP'
$@jobSql
set current sqlid = 'S100447';
drop view oa1p.vqz005Recover;
$@ 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
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, p2 as
(
select s.dbName db, s.name ts, p.partition pa
, s.instance instBa
$*(, two.i inst
, case when s.instance = 1 and s.clone = 'N' then '1'
when s.clone = 'N' then '' || s.instance || 'only'
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clone' end insTxt
$*)
, s.pgSize pgSize
, s.nTables nTb
, value(p.space, 0) pSpc, s.dbId, s.psId
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then 'only' || s.instance
else 'base' || s.instance end instBaTx
, ( 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 s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) icPa
, ( 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 s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) ic0
from sysibm.sysTableSpace s
-- join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, p3 as
(
select db, ts, pa, instBa, instBaTx
, pgSize, nTb, dbId, psId, pSpc
, 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
)
, p as
(
select p4.*
, case when pSpc = -1 then 'defNo'
when nTb = 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
)
select db, ts, pa, recov
, basTyTx, basPa, basTst, basTy
, incTy, incPa, incTst
, instBa, instBaTx
, pgSize, pSpc, dbId, psId
from p
;
$/vRecover/
$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 strip(substr(recov, 3) || ' older8d')
else recov
end rec8d
, 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 recLR = '-' then '-'
when recLR in ('r', '2')
and recov in ('defNo', 'noTb') then '-'
when recLR = 'r' and recov = 'ok' then 'r'
when recLR = '2' and rec8d = 'ok' then 'r'
when recLR = 'r' then '?'
when recUnl in ( 'ok'
, 'dataChangeV11>unl'
, 'dataChangeV11>incCopy'
$@ if $isElar then $@=[
, 'lastFul=A=addPart'
, 'lastFul=C=create'
, 'lastFul=S=LoaRpLoNo'
, 'lastFul=Y=LoaRsLoNo'
$]
) then 'l'
else '?'
end recFun
from e2
)
, e as
(
select e3.*
, 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
from e3
)
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 lgFun
from l2
)
select db, ts, pa, stage, staUpd, staTb, pSpc, rSpc, basTy, basTst
, recLr, recov, lgRn
, unl, unlTst
, strip(recUnl || ' ' || lgRn) recUnl
, lgFun recFun
, strip(lgFun || substr(conSum, 2)
|| case
when recFun = 'l' or (recFun = '?' and recLR <> 'r')
then ' ' || lgRn else '' end) conSum
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/