zOs/SQL/TECSVDDL
$#@
$*(
create views
oa1p.vQZ005Recover current recover Info
oa1p.vQZ005RecovDelta same but ignore logDiscontinuities
in last 10 minutes
if xDocs additionally
oa1p.tQZ005TecSvUnload table for unloads of Elar, EOS, eRet
oa1p.vQZ005RecovLoad recovery incl. load of xDoc tables
oa1p.vQZ005RecovDeltaLoad incl. load of xDoc tables
if useLgRn additionally
OA1P.tqz004tecSvLgRn table for sysLgRnX extract
oa1p.vQZ005RecovDeltaLoadLgRn incl. lastlgRn
28. 9.16 Walter cols spc=size in bytes, spcU=estimate of unload size
22. 9.16 Walter allow copies in stage UL, remove rtsLastEq, cleanup
8. 4.16 Walter Cleanup
8.12.15 Walter Nov Zuegelschub EOS/eRet
24. 9.15 Walter neu
$*)
$>. fEdit()
$=cr=OA1P
$=dropCreTb = 0
$=creDb = 0
$=deltaTime = 10 minutes
$= lEnd = sLrsn
$= lEnd = endTst
call iiIni
if 0 then $@[
call iiPut 'RZ2/DVBP'
$@ddlOne
$] else $@/allRZ/
$@jobHead
oldRz = ''
$do ix=1 while iiIxPut(ix) $@/envLoop/
if $rz <> oldRz then $@[
if oldRz <> '' then
$$ }{
$@jobSub
oldRz = $rz
$]
$@jobSql
$@ddlOne
$@ if $dropCreTb & $xDocs then
$@loadUnload
$/envLoop/
$/allRZ/
$****** generate ddl for one rz/dbSys *********************************
$proc $@=/ddlOne/
$= xDocs =- wordPos($dbSys, 'DVBP DBOF') > 0 $*+
| ($rz=RZZ & wordPos($dbSys, 'DE0G DEVG') > 0)
$** is instance in index of sysCopy ?
$=useLgRn = $xDocs
$=useInst =- wordPos($rz, 'RZ2 RZ4') < 1
--- ddl for tecsv recovery for $rz/$dbSys ----------------------------
-- generated by rz4/DSN.SOURCE.TECSV(TECSVDDL) alle Aenderungen dort
-- xDocs=$xDocs isElar=$dbSysElar useLgRn=$useLgRn useInst=$useInst
----------------------------------------------------------------------
set current sqlid = 'S100447';
drop view $cr.vqz005Recover;
drop view $cr.vqz005RecovDelta;
$@ if $xDocs & $cr == 'OA1P' then
$@tLastEqDrop
$@ if $dropCreTb then $@/dropCreate/
if $xDocs then
$@tUnloadDrop
if $useLgRn then
$@tLgRnDrop
if $xDocs | $useLgRn then
$$ commit;
if $creDb & ($xDocs | $useLgRn) then $@=[
CREATE DATABASE QZ01A1P
BUFFERPOOL BP2
INDEXBP BP1
STOGROUP GSMS
CCSID EBCDIC
;
commit;
$]
$/dropCreate/
--#SET MAXERRORS 0
$@ if $dropCreTb then $@[
if $xDocs then
$@tUnload
if $useLgRn then
$@tLgRn
$]
$@% vRecover vqz005Recover
$@% vRecover vqz005RecovDelta $deltaTime
$@ if $xDocs then $@[
$@% vRecovLoad vqz005RecovLoad vqz005Recover
$@% vRecovLoad vqz005RecovDeltaLoad $*+
vqz005RecovDelta
$@ if $useLgRn then $@[
$@% vRecovLoadLgRn vqz005RecovLoadLgRn $*+
vqz005RecovLoad
$@% vRecovLoadLgRn vqz005RecovDeltaLoadLgRn $*+
vqz005RecovDeltaLoad
$]
$]
$/ddlOne/
$****** recoverView: pro partition/instance/newest sysCopy ***********
$proc $@=/vRecover/
$arg view logDisMin
create view $cr.$view 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 -- tablespace with 1 or 2 instances
(
select char(s.dbName, 8) db, char(s.name, 8) ts
, s.instance instBa
, smallInt(value(two.i, s.instance)) inst
, char(value(case
when s.clone = 'N' and s.instance = 1 then '1'
$@ if $useInst then $@=[
when s.clone = 'N' then '2only'
when s.instance = two.i then two.i || 'base'
else two.i || 'clone'
$] $@ else $@=[
when s.clone = 'N' then '2only?'
when s.instance = two.i then two.i || 'base?'
else two.i || 'clon?'
$]
end, '?clo??'), 6) insTxt
, s.pgSize, s.dbId, s.psId , s.nTables
, smallInt(value(case
when dssize <> 0 then real(dssize) / 1048576
when type in ('G', 'O', 'P', 'R', 'L') then 4
when partitions = 0 then 64
when partitions > 254 then pgSize
when partitions > 64 then 4
when partitions > 32 then 1
when partitions > 16 then 2
else 4
end, -99)) dsGB
from sysibm.sysTableSpace s
left join two on s.clone = 'Y'
)
, s as -- tableSpace/instance + lastCopy of partition 0
(
select s2.*
-- lastCopy of partition 0
$@% selCopy s2.db s2.ts 0 s2.inst ic0 $logDisMin
from s2
)
, p2 as -- ts/instance/partition + lastCopy of pa
(
select s.*, p.partition pa
, value(p.space, 0) pSpc
, value(p.pageSave, 0) pgSv
-- last copy of partition pa
$@% selCopy s.db s.ts p.partition s.inst icPa $logDisMin
from s
join sysibm.sysTablePart p
on s.db = p.dbName and s.ts = p.tsName
)
, p3 as -- newer of lastcopy of partition pa or 0
(
select db, ts, pa, inst, insTxt
, pgSize, nTables, dbId, psId, dsGb, pSpc, pgSv, instBa
, max(value(substr(icPa, 33, 32),'1111-11-11-11.11.11.111111--1')
,value(substr(ic0 , 33, 32),'1111-11-11-11.11.11.111111--1')
, '1111-11-11-11.11.11.111111--1') basTTP
, max(value(substr(icPa, 1, 32),'1111-11-11-11.11.11.111111--1')
,value(substr(ic0 , 1, 32),'1111-11-11-11.11.11.111111--1')
, '1111-11-11-11.11.11.111111--1') incTTP
from p2
)
, p4 as -- decode TTP into timestamp, type and partition
(
select p3.*
, substr(basTTP, 27, 1) basTy
, smallInt(substr(basTTP, 28, 5)) basPa
, timestamp(substr(basTTP, 1, 26)) basTst
, substr(incTTP, 27, 1) incTy
, smallInt(substr(incTTP, 28, 5)) incPa
, timestamp(substr(incTTP, 1, 26)) incTst
from p3
)
, p as -- add recover state and text for baseType
(
select p4.*
, char(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'
when basTst < current timestamp - 8 days then 'older8d'
else 'ok' end, 8) recover
, 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, inst, recover
$@vRecCols
from p
;
$/vRecover/
$****** column list for recovery info ********************************
$proc $@=/vRecCols/
, basTyTx, basPa, basTst, basTy
, incTy, incPa, incTst
, pgSize, pSpc, pgSv, dsGB, dbId, psId, insTxt, instBa
$/vRecCols/
$****** select newest copy for part/inst *****************************
$proc $@=/selCopy/
$arg selCopyDb selCopyTs selCopyPa selCopyInst selCopyAl selCopyDe
-- 1) encode timestamp, type, part in one string (sortable)
-- 2) for a=lastIncremental and b=recover base
, ( 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 $selCopyDe <> '' then $@=[
-- ignore discontinuities in the last few minutes
and not (ICTYPE in('P','S','W','Y')
and c.timestamp > current timestamp - $selCopyDe )
$]
) $selCopyAl
$/selCopy/
$****** view for recover by copy/log OR load unload ******************
$proc $@=/vRecovLoad/
$arg view vRec gg
create view $cr.$view as
with u as -- join unloads
(
select p.*
, char(value(stage, ''), 2) stage
, value(staUpd, '1111-11-11-11.11.11') staUpd
, char(value(staTb, ''), 2) 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, '') unlInfo
, strip(value(u.err, '')
|| case when stage = 'IN' and unl <> ''
and staUpd < current timestamp - 24 hour
then ' stillUnlAft24h'
when stage = 'RD' and unl <> ''
and staUpd < current timestamp - 48 hour
then ' RDolder48h'
else '' end) unlErr
from $cr.$vRec 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.*
, 1024.0 * min(1048576.0
* case when dsGb > 0 then dsGb else 64 end
, max(0, value(pSpc, 0)
, value(real(r.nActive) * u.pgSize, 0))) spc
, char(case when stage = '' then 'r' -- load, recovery or both?
$@ if $dbSysElar then $@=[
when stage in ('UL', 'DL', '-w') then 'l'
$] $@ else $@=[
when stage in ('IN', 'RD', 'UL') then '2'
$]
else 'r'
end, 1) recLR
, case -- is load the unload valid or not, check metadata/syscopy
when unl = '' then 'noUnload'
when unlTst < '1919-01-01-00.00.00' then 'unlTstNull'
$*( --------------- ok, we now allow copies after unload --------------
when BasTst > unlTsUp
$@ if $dbSysElar then $@=[
and not (BasTy = 'F' and basTst > '2015-12-01-00.00.00')
$] $@ else $@=[
and (basTy <> 'F' or (date(incTst) <> '18.05.2015'
and basTst <= '2015-12-01-00.00.00') )
$]
then 'unl<base=' || basTyTx
--------------------------------------------------------------------$*)
when incTy='I'
$@ if \ $dbSysElar then
and date(incTst) not in ('18.05.2015','19.05.2015','10.12.2015')
then 'incCopy'
when basTy <> 'F' then 'base=' || basTyTx
else ''
end recLoad
, case -- is load the unload valid or not, check rts
when unl = '' then ''
when r.dbName is null then 'noRTS'
when r.lastDataChange > u.unlTsUp then 'dataChange>unl'
when r.copyChanges <> 0 then 'copyChanges<>0'
when r.copyUpdatedPages <> 0 then 'updatedPages<>0'
when r.copyUpdatetime is not null then 'copyUpdateNotNull'
when r.lastDataChange >u.BasTst
$@ if \ $dbSysElar then
and basTst > '2015-09-12-12.00.00'
then 'dataChange>base=' || basTyTx
when r.lastDataChange is null and unl <> '' and unlTst
< '2015-04-15-00.00.00' then 'dataChangeV11>unl'
else ''
end rtsLoad
, lastDataChange, copyUpdateTime, copyChanges, copyUpdatedPages
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.*
, spc * 100 / min(max(1, 100 - value(pgSv, 0)), 300) spcU
, char(case when ( recLoad = ''
$@ if $dbSysElar then $@=[
or (recLoad in ( 'base=A=addPart'
, 'base=C=create'
, 'base=S=LoaRpLoNo'
, 'base=Y=LoaRsLoNo'
) and basTst < '2014-05-01-00.00.00' )
$]
) and rtsLoad in ('', 'dataChangeV11>unl')
then 'l' else '?' end, 1) recLok
from e2
)
select db, ts, pa, inst, recover
, char(case when recLR in ('r', '2')
and recover in ('defNo', 'noTb') then '-'
when recLR in ('r', '2') and recover = 'ok' then 'r'
when recLR in ('l', '2') and recLok = 'l' then 'l'
else '?' end, 1) fun
, recLok lok
, trim(trim(recLoad || ' ' || rtsLoad) || ' ' || unlErr) load
$@vRecLoadCols
from e3
;
$/vRecovLoad/
$proc $@=/vRecLoadCols/
$@vRecCols
, stage, staUpd, staTb, unlTst, unlTsUp, unl, punTst, pun
, unlInfo, unlErr, recLR
, lastDataChange, copyUpdateTime, copyChanges, copyUpdatedPages
, spc, spcU
$/vRecLoadCols/
$****** view for recover use rts plus sysLgRnX ************************
$proc $@=/vRecovLoadLgRn/
$arg view vRec gg
create view $cr.$view as
with l2 as
(
select r.*
, case when unl = '' -- lgRn after unload?
or unlTst < '1919-01-01-00.00.00' then ''
when l.$lEnd > unlTst then 'lgRn>unl'
when l.$lEnd > basTst then 'lgRn>base'
when l.$lEnd is null then 'lgRnNone'
else ''
end lgRnLoad
, l.$lEnd lgRnEnd
from $cr.$vRec 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.*
, char(case when lgRnLoad in ('', 'lgRnNone')
or (lgRnLoad ='lgRn>base' and lgRnEnd <'2015-06-01-00.00.00')
then lok else '?' end, 1) lgRnLok
from l2
)
select db, ts, pa, inst, recover
, case when fun <> 'l' then fun else lgRnLok end fun
, lgRnLok lok
, trim(load || ' ' || lgRnLoad) load
$@vRecLoadCols
, lgRnEnd
from l3
;
$/vRecovLoadLgRn/
$****** drop tqz005TecSvUnload ****************************************
$proc $@=/tUnloadDrop/
alter table oa1p.tQz005TecSvUnload drop restrict on drop;
drop tablespace qz01a1p.a005a;
$/tUnloadDrop/
$****** create tqz005TecSvUnload **************************************
$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 DSSIZE 4G MAXPARTITIONS 10
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/
$****** create tqz004TecSvLgRn ****************************************
$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 DSSIZE 4G MAXPARTITIONS 10
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
, $lEnd timestamp 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($lEnd)
USING STOGROUP GSMS
ERASE NO
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP1
CLOSE YES
COPY NO
DEFINE YES
;
$/tLgRn/
$****** drop tqz005TecSvRtsLastEq *************************************
$proc $@=/tLastEqDrop/
----- ddl tecSv: xDocs: last rts timestamp with eq data ---------------
ALTER TABLE OA1P.tqz005tecSvRtsLastEq DROP RESTRICT ON DROP ;
DROP TABLESPACE QZ01A1P.a005r;
$/tLastEqDrop/
$****** load tqz004TecSvUnload ****************************************
$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/
$****** output job header *********************************************
$proc $@=/jobHead/
//TECSVDDL JOB (CP00,KE50),'DB2 DDL',
// MSGCLASS=T,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL
$/jobHead/
$****** step for submit job to rz *************************************
$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 DATA,DLM='}{'
$@jobHead
$/jobSub/
$****** step for sql execution ****************************************
$proc $@=/jobSql/
//*---- execute ddl in $rz/$dbSys isElar=$dbSysElar ------------------
//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/