Re: [Purging PPAT Message Control File entries from F00165 Media

b_gilmore

Well Known Member
Re: [Purging PPAT Message Control File entries from F00165 Media

I use this script to purge once a week from the QSHELL environment on the
AS/400. QSH uses the bash shell so you can hit bash tutorial sites or the
ISeries Info Center site for your release as a reference. It's very helpful
if you're not a RPG or CL programmer.

***********************************************************
#/home/oneworld/ppat.sh
echo "DELETE FROM $1.F00165 WHERE GDOBNM = 'GT01131'" >
/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR
echo "AND GDDQE < "1$(expr $(date +%Y%j) - 14 | cut -c 3-7)";" >>
/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR

echo "DELETE FROM $1.F01131M WHERE ZMDTI < " 1$(expr $(date +%Y%j) - 14 | cut
-c 3-7)";" >> \
/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR
echo "DELETE FROM $1.F01131T WHERE ZCUPMJ < " 1$(expr $(date +%Y%j) - 14 | cut
-c 3-7)";" >> \
/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR
echo "DELETE FROM $1.F01133 WHERE ZTSERK IN " >>
/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR

echo "(SELECT ZZSERK FROM $1.F01131 WHERE ZZDTI < " 1$(expr $(date +%Y%j) - 14
| cut -c 3-7)");" >> \

/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR
echo "DELETE FROM $1.F01131 WHERE ZZDTI <" 1$(expr $(date +%Y%j) - 14 | cut -c
3-7)";" >> \

/qsys.lib/jdetemp.lib/SQL.FILE/PPATPURGE.MBR

system "RUNSQLSTM SRCFILE(JDETEMP/SQL) SRCMBR(PPATPURGE) COMMIT(*NONE)
NAMING(*SQL)" &
wait

for i in F00165 F01131M F01131T F01133 F01131
do system "RGZPFM FILE($1/$i)" &
done
wait
************************************************************************

These echo statements populate a SRC physical file in my JDETEMP library and
then I call the RUNSQLSTM command via the QSHELL "system" command which
executes the sql statement.

To get fancy I call ppat.sh from another script ppatrun.sh which contains
this
****************************************************
#/home/oneworld/ppatrun.sh
for j in $*; do ./ppat.sh $j; done
***************************************************

From the QSH command line I pass the library names I want to purge as
parameters to this script. So every weekend I run this command:

./ppatrun.sh TESTDTA CRPDTA PRODDTA

which loops thru each library and executes the ppat.sh script and the sql
statement to purge all messages and reorg the files based up on today's date
minus 14 days.

P.S. it's best to run this right after an IPL so the RGZPFM commands won't
fail due to file locks from QZDASOINIT jobs.

It's not exactly pretty but it works.




AllisonD <[email protected]> wrote:

from F00165. We have 1,220,300 GT01131 records which equates to 3 1/2 years
worth. Gotta find a way to purge this back and get back that space. Thanks
in advance...Darrell AllisonSystems ProgrammerAS/400 V4R5 9406-730 8-Way /
OneWorld B7332 SP11.3
 
Back
Top