zOs/REXX.O13/DODREORG
/* rexx ****************************************************************
DodReorg Rexx for monthly ReorgJob for dod-log-Tabel tid150a1
********
Function:
generate 6 sysin files for db2 load jobs
to drop partitions (with old data) from dod-log-table tid150a1
the following partitions are selected:
* not empty and
* tid150tst older than 270 days and
* partition not used between yesterday and next 5 days
Input:
Arguments: none
dd partIn
an sql report (=> dod...parm(sqlPart)) generated by dsntiaul
the following columns are used (extracted with word(line,x))
xNam = 1 name of the table --+
xCre = 2 creator of the table +-- catalog info
xPar = 5 partition number --+
xMD = 6 tid150md --+
xCnt = 7 number of rows +-- table data
xTst1 = 11 minimum tid150tst !
xTst2 = 12 maximum tid150tst --+
Output:
return code:
0: ok, 1-6 partitions selected for drop
4: ok, 0 partitions selected for drop
>=8: error
dd ddUtil1, ddUtil2, ..., ddUtil6
each file contails
either input statements for DSNUTILB
to load on partition xPar of xCre.xNam
(with an empty inputfile this empties the partition|)
or an empty line (so DSNUTILB does nothing with rc=4)
dd SYSREC00: a copy of the sql report (dd partIn)
dd SYSPRT: some messages
History:
13.01.04 created Walter Keller, KPCO4
***********************************************************************/
say date('e') time() 'start rexx dodReorg'
call analysePartRep 'partIn', 'sysRec00', 'ddUtil'
say 'written' uCnt 'utilFile for delete of a partition'
u.1 = ''
do i=uCnt+1 to 6
call adrTso 'execio 1 diskW ddUtil'i '(stem u.)'
end
say date('e') time() 'end rexx dodReorg'
if uCnt = 0 then
exit 4
else
exit 0
exit
analysePartRep:
parse arg paIn, paOut, util
/*----------------------------------------------------------------------
analyse the sql partition report in dd paIn and copy it to dd paOut
write dd DSNUTILB statements to dd util'1', util'2' etc
----------------------------------------------------------------------*/
xNam = 1 /* report layout */
xCre = 2
xPar = 5
xMD = 6
xCnt = 7
xTst1 = 11
xTst2 = 12
call makeDates date('s') /* comput dates dKeep pKeepV pKeepB */
/* read an write partition report */
call adrTso 'execio * diskr' paIn '(stem pr.)'
say 'read' pr.0 'lines'
call adrTso 'execio' pr.0 'diskW' paOut '(stem pr.)'
uCnt = 0
do i=1 to pr.0 /* each line of the report */
pr.i = strip(pr.i, 't', '00'x) /* remove zeros */
md = word(pr.i, xMD)
r = ''
if pKeepV < pKeepB then do /* check partition limit */
if pKeepV <= md & pKeepB >= md then
r = '<md>'
end
else if pKeepV <= md | pKeepB >= md then
r = '>md<'
if word(pr.i, xCnt) = 0 then
say 'empty' r' partition' md,
word(pr.i, xCre)'.'word(pr.i, xNam)'.'word(pr.i, xPar)
else do
if word(pr.i, xTst2) > dKeep then
r = r 'tst2' /* data too young */
say r 'partition' md,
word(pr.i, xCre)'.'word(pr.i, xNam)'.'word(pr.i, xPar),
'cnt' word(pr.i, xCnt),
word(pr.i, xTst1) '-' word(pr.i, xTst2)
if r = '' then do /* delete partition */
uCnt = uCnt + 1
call writeUtil util || uCnt, ,
word(pr.i, xCre)'.'word(pr.i, xNam), ,
word(pr.i, xPar)
end
end
end
return /* end analysePartRep */
writeUtil: procedure
parse arg dd, table, part
/*----------------------------------------------------------------------
write to dd dd the statement to load part part of table table
----------------------------------------------------------------------*/
say '>>> write dd' dd 'for drop table' table 'partition' part
u.1 = 'LOAD DATA LOG YES'
u.2 = ' INTO TABLE' table 'PART' part
u.3 = ' INDDN SYSREC00 REPLACE'
call adrTso 'execio 3 diskW' dd '(stem u.)'
return /* end util */
makeDates:
parse arg dt
/*----------------------------------------------------------------------
compute the 3 dates
dKeep: keep date younger than this date, db2-timestamp format
pKeepV: yesterday in format mmdd
pKeepB: today+21 in format mmdd
----------------------------------------------------------------------*/
pKeepV = substr(dateAdd(dt, -1), 5, 4)
pKeepB = substr(dateAdd(dt, 21), 5, 4) /* ensure next partition| */
dKeep = dateAdd(dt, -270)
dKeep = left(dKeep, 4)'-'substr(dKeep, 5, 2)'-'right(dKeep,2)
say dKeep 'from' dt 'part' pKeepV '-' pKeepB
say 'criteria TST <=' dKeep ,
'and limit (MD) not between' pKeepV 'and' pKeepB
return /* end makeDates */
dateAdd: procedure
parse arg dt, nu
/*----------------------------------------------------------------------
add nu days to date dt in format yyyymmdd, nu may be negative
----------------------------------------------------------------------*/
return date('s', (date('b', dt, 's') + nu), 'b')
testDateAdd: procedure
parse arg d
say d '+1' dateAdd(d, 1) '+40' dateAdd(d, 40) '+70' dateAdd(d, 70) ,
'+365' dateAdd(d,365) '+366' dateAdd(d, 366)
say d '-1' dateAdd(d,-1) '-40' dateAdd(d,-40) '-70' dateAdd(d,-70) ,
'-365' dateAdd(d,-365) '-366' dateAdd(d,-366)
return
err: procedure expose m.
parse arg txt
say 'fatal error' txt
if m.pipe.errDump = '1' then
call pipeDump
say 'exiting rexx dodreorg'
exit 8
adrTsoRc:
parse arg tsoCmd
address tso tsoCmd
return rc /* end adrTsoRc */
adrTso:
parse arg tsoCmd
address tso tsoCmd
if rc <> 0 then
call err 'adrTso rc' rc 'for' tsoCmd
return /* end adrTso */
adrIspRc:
parse arg ispCmd
address ispexec ispCmd
return rc /* end adrIspRc */