zOs/REXX/QZT09X1
$#=
$*( für wsh test ..................
$>. fEdit()
$= rz = RZZ
$= dbSys = DE0G
$= jP =- iiRz2P($rz)iidbSys2c($dbSys)
$= ab = xDoc
$= now =- f('%t S')
$= p0 = A540769.TST.XDOC
$= pref =- $p0'.'f('%tSY', $now)
$= d = $rz/$p0.ABLF
$*)
$=job=QZT09X${jP}
$= isElar =- $dbSys = 'DVBP' | $dbSys = 'DEVG'
$** until Elar implements its own jobs|
$=doXbaCopy =- $dbSys == 'DVBP' & $rz = RZ2 & $now < '2016-05-10'
$= useLgRn =- wordPos($dbSys, 'DVBP DBOF') > 0 $*+
| ($rz=RZZ & wordPos($dbSys, 'DE0G DEVG') > 0)
$@jobHead
//*** submit to rz $rz ***********************************************
//SUB$rz EXEC PGM=IEBGENER
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD SUBSYS=(CSM,'SYSTEM=$rz,SYSOUT=(A,INTRDR)')
//SYSUT1 DD DATA,DLM='}{'
$@jobHead
//*** load tecsv unload table ****************************************
//TECSVUNL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSEXEC DD DSN=DSN.DB2.EXEC,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
%tecSvUnl $dbSys
// IF TECSVUNL.RUN AND TECSVUNL.RC <= 4 THEN
$@ if $useLgRn then $@=[
//*** load lgrn table ************************************************
//LGRNLOA EXEC PGM=DSNUTILB,PARM='$dbSys,$job.LGRNLOA'
//SYSMAP DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSERR DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTEMPL DD DSN=$dbSys.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD *
EXEC SQL
DECLARE CUR1 CURSOR FOR
with s as
(
select s.DBNAME db, s.NAME TS, p.partition PA
, value( ( select
max( max(value(timestamp(substr(max(LGRSLRSN), 2, 8))
, '1111-11-11-11.11.11')
, value(timestamp(substr(max(LGRELRSN), 2, 8))
, '1111-11-11-11.11.11')) + 7174 seconds
-- max(sommer, winterzeit) - 26 leapSeconds
-- do not use current timeZone there was a winter once
, value(timestamp(max(
TRANSLATE('20YZ-MN-DE-', LGRUCDT, 'MNDEYZ')
|| TRANSLATE('HI.MN.ST.UV', LGRUCTM, 'HIMNSTUV')))
, '1111-11-11-11.11.11')
)
from sysibm.sysLgRnX l
where l.lgrdbid = oa1p.fqzCastSmall2C(s.dbid)
and l.lgrpsid = oa1p.fqzCastSmall2c(s.psid)
and l.lgrpart = p.partition
), '1111-11-11-11.11.11') endTst
from sysibm.sysTableSpace s
join sysibm.sysTablePart p
on s.dbName = p.dbname and s.name = p.tsname
)
select *
from s
where endTst > '1919-01-01-00.00.00'
WITH UR
ENDEXEC
LOAD DATA INCURSOR CUR1 LOG NO RESUME NO REPLACE
COPYDDN TCOPYS STATISTICS INDEX ALL KEYCARD
SORTDEVT DISK
WORKDDN(TSYUTS,TSOUTS)
INTO TABLE OA1P.TQZ004TECSVLGRN
// ENDIF
// IF LGRNLOA.RUN AND LGRNLOA.RC <= 4 THEN
$]
//*** report statistics and list of xDoc partitions ******************
//REPORT EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99
//SYSTSIN DD *
DSN SYSTEM($dbSys)
RUN PROGRAM(DSNTIAUL) PARMS('SQL')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTERM DD SYSOUT=*
//SYSPUNCH DD SYSOUT=*,RECFM=FB,LRECL=100
//SYSREC00 DD DISP=(NEW,CATLG,DELETE),RECFM=FB,LRECL=133,
// SPACE=(CYL,(1,50),RLSE),MGMTCLAS=COM#A031,
// DSN=$pref.XSTATS
//SYSREC01 DD DISP=(NEW,CATLG,DELETE),RECFM=FB,LRECL=133,
// SPACE=(CYL,(1,50),RLSE),MGMTCLAS=COM#A031,
// DSN=$pref.XLIST
//SYSIN DD *
set current path oa1p;
set current application compatibility 'V11R1';
$**-- summary
$@repWith
, u (spc, spcU, parts, redo, stage, recState
, unlFrom, unlTo, staUpdFrom,staUpdTo) as
(
select 'spaceBy', 'spaceUn', ' parts', 'redo', 'stage'
, 'recoveryState', 'unlFrom', 'unlTo', 'staUpdFrom', 'staUpdTo'
from sysibm.sysDummy1
union all ( select fqzFmtBin7(sum(spc)), fqzFmtBin7(sum(spcU))
, ' ' || count(*)
, redo, stage, recState
, char(min(unlTst)), char(max(unlTst))
, char(min(staUpd)), char(max(staUpd))
from r
group by grouping sets ((), (redo), (redo, stage, recState) )
order by redo, stage, recState
)
)
select char(value(spc, ''), 8)
, char(value(spcU, ''), 8)
, char(right(value(parts, ''), 7), 8)
, char(value(redo, '***'), 9)
, char(value(stage, '***'), 6)
, char(left(value(recState, '***'), 120), 120)
, char(value(unlFrom, ''), 27)
, char(value(unlTo , ''), 27)
, char(value(staUpdFrom, ''), 27)
, char(value(staUpdTo, ''), 27)
from u
with ur
;
$**-- listing of parts
$@repWith
, u (db, ts, pa, redo, stage, spc, spcU, recState, staUpd, unlTst
,lastDataChange, basTyTx, basTst, lgrnEnd) as
(
select 'db', 'ts', ' part', 'redo', 'stage', 'spaceBy', 'spaceUn'
, 'recoveryState', 'staUpd', 'unlTst', 'lastDataChange'
, 'basText', 'basTst', 'lgRnEnd'
from sysibm.sysDummy1
union all ( select db, ts, ' ' || pa
, redo, stage, fqzFmtBin7(spc), fqzFmtBin7(spcU)
, recState, char(staUpd), char(unlTst), char(lastDatachange)
, basTyTx, char(basTst), char(lgrnEnd)
from r
where redo <> ''
order by r.db, r.ts, r.pa
)
)
select char(value(db, '') , 9)
, char(value(ts, '') , 9)
, char(right(value(pa, '') , 5), 6)
, char(value(redo, '') , 9)
, char(value(stage, '') , 6)
, char(left(value(recState, '') , 120), 120)
, char(value(staUpd, '') ,27)
, char(value(unlTst, '') ,27)
, char(value(lastDataChange, '') ,27)
, char(value(basTyTx, '') , 15)
, char(value(basTst, '') ,27)
, char(value(lgrnEnd, '') ,27)
, char(value(spc, '') , 8)
, char(value(spcU, '') , 8)
from u
fetch first 10000 rows only
with ur
;
// ENDIF
// IF REPORT.RUN AND REPORT.RC <= 4 THEN
//OK EXEC PGM=IEFBR14
//OK DD DISP=(NEW,CATLG,DELETE),RECFM=FB,LRECL=133,
// SPACE=(TRK,(1,1),RLSE),MGMTCLAS=COM#A031,
// DSN=$pref.OK
// ENDIF
$@ if $doXbaCopy then $** until Elar implements its own jobs|
$@xbaCopy
}{
$proc $@=/repWith/
$**-- list of partition recovery state --------------------------------
with q as
(
select qq.*
, fun || case when recLr in ('r', '2')
then ' ' || recover else '' end
|| case when recLr in ('l', '2')
then rtrim(' ' || load) else '' end recState
from oa1p.vQz005RecovDeltaLoadLgRn qq
$@ if $isElar then $@=[
where db like 'XB%'
)
, r as
(
select case when stage not in ('-a', '-r', '-w'
, 'CL', 'DL', 'RW', 'UL') then 'fixMeta'
when stage in ('UL', 'DL') and lok <> 'l'
and '' = replace(replace(replace(replace(replace(
replace(' ' || substr(recState, 2) || ' '
, ' base=A=addPart ', ' ')
, ' base=S=LoaRpLoNo ', ' ')
, ' dataChange>base=A=addPart ', ' ')
, ' dataChangeV11>unl ', ' ')
, ' lgRnNone ', ' '), ' lgRn>base ', ' ')
then 'redoCopy'
when stage in ('UL', 'DL') and lok <> 'l' then 'redoUnl'
$] $@ else $@=[
where (db like 'XC%' or db like 'XR%')
and (ts like 'A2%' or ts like 'A5%')
)
, r as
(
select case when posStr(recState, 'stillUnl') > 0 then 'cleanup'
when posStr(recState, 'punNotSo') > 0 then 'redoUnl'
when stage in ('UL', 'RD') and lok <> 'l'
and '' = replace(replace(
replace(' ' || substr(recState, 2) || ' '
, ' dataChangeV11>unl ', ' ')
, ' lgRnNone ', ' '), ' lgRn>base ', ' ')
then 'redoCopy'
when stage in ('UL', 'RD') and lok <> 'l' then 'redoUnl'
$]
when left(recState, 1) not in ('r', 'l', '-')
then 'recErr'
else '' end redo
, q.*
from q
)
$/repWith/
$proc $@=/xbaCopy/
//*
//* copy partitions in state -a ==> only in xba201
//*
//* delete old dsn
//XBADEL EXEC PGM=IEFBR14
//PL DD DSN=$pref.XBAUTIL,
// DISP=(MOD,DELETE)
//*
//XBASQL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM($dbSys)
RUN PROGRAM(DSNTIAUL) PARMS('SQL')
END
//SYSPUNCH DD SYSOUT=*
//SYSREC00 DD DSN=$pref.XBAUTIL,
// SPACE=(TRK,(1,1),RLSE),
// MGMTCLAS=COM#A013,
// RECFM=FB,LRECL=80,
// UNIT=DISK,DISP=(NEW,CATLG)
//SYSIN DD *
with a as
( /***** select segment from txba201 */
select enStorAr n , right('000' || enSeg, 3) seg
from bua.txba201 a
group by enStorAr, enSeg
)
, b as
( /***** exclude txbi003 */
select *
from a
where not exists (select 1
from BUA.TXBI003 i
where i.storageArea_N = a.n
and i.segment = a.seg
and i.partNumber = 1
)
)
, c as
( /***** compute alpha storage area from numeric */
select n, seg
, case when n <= 999 then right('000' || n, 3)
when n <= 35657 /* 1296 = 36**2 */
then substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, (n + 10998) / 1296 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 1296) / 36 + 1, 1)
|| substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, mod(n + 10998, 36) + 1, 1)
end stoAr
from b
)
, t as
( /***** join sysTables */
select c.*, t.dbName db, t.tsName ts, t.creator cr, t.name tb
from c join sysibm.sysTables t
on left(t.name, 8) = 'XB' || c.stoAr || c.seg
)
, i0 as
( /***** create include statement and row number */
select ' INCLUDE TABLESPACE '
|| db || '.' || ts || ' PARTLEVEL 1 -- ' || tb li
, row_number() over(order by db, ts) rn
from t
)
, i as
( /***** add group number */
select 1 + floor(rn / 50) gr, rn, li
from i0
)
, g as
( /***** groups only */
select gr from i group by gr
)
, ut (gr, rn, li) as
( /***** union all of utility statements */
select 0 , -5, 'OPTIONS(EVENT ITEMERROR, SKIP)'
from sysibm.sysDummy1
/* on all select 0 , -4, 'OPTIONS PREVIEW'
from sysibm.sysDummy1 */
union all select gr , -1, 'LISTDEF LIST#' ||gr from g
union all select gr , rn, li from i
union all select gr+1, -9
, 'COPY LIST LIST#' || gr || ' COPYDDN(TCOPYD)' from g
union all select gr+1, -8
, ' FULL YES PARALLEL SHRLEVEL CHANGE' from g
)
select char(value(li, ' -- null'), 80)
from ut
order by gr, rn
;
//XBACOPY EXEC PGM=DSNUTILB,
// PARM='$dbSys,$job.COPY'
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTEMPL DD DSN=$dbSys.DBAA.LISTDEF(TEMPL),DISP=SHR
//SYSIN DD DSN=$pref.XBAUTIL,
// DISP=SHR
$/xbaCopy/
$proc $@=/jobHead/
//$job JOB (CP00,KE50),'db2 abub xDoc',
// MSGCLASS=E,TIME=1440,
// NOTIFY=&SYSUID,REGION=0M,SCHENV=DB2ALL,CLASS=M1
//*
//* weekly xDocs control for $rz/$dbSys/$ab job1
//* generated by abub at $now
//* source: rz4/dsn.abub.a.skels(qzt09X1)
//* version: 28.9.16 ohne lastEq mit lgrnEnd/endTst und spaceUn
//* p0 =$p0
//* pref=$pref
//* d =$d
//* =$rz/$dbSys
$/jobHead/