Delect members by age using CL
I download a bank file once a day. I want to keep the last two weeks of files stored in members of file ARCHIVE. Typical member names might be 20041109, etc. How can I purge the members that are more than 15 days old using CL? Is there a way to convert a *CHAR variable into a *DEC variable? Is there something in the output of DSPFD that can help? (NOte I can use any naming convention I want, but I may only use CL.)
TIA
Software/Hardware used:
Software/Hardware used:
ASKED:
November 9, 2004 11:19 AM
UPDATED:
October 19, 2009 8:46 AM
Answer Wiki:
Calculate your system date - 15 days, DSPFD *MBRLIST *OUTFILE on the archive file, compare member create date[MLCDAT] in outfile to your calculated date. Just a simple outfile read through a RCVF command and if it passes for over 15 days old, use a RMVM command with appropriate values from the outfile.
Julian date is easiest to use and keep in mind leap years. Here is some rough code to start you on the path, FIFTEEN is the calculated date:
PGM
DCL VAR(&FIFTEEN) TYPE(*CHAR) LEN(6)
DCL VAR(&CYR) TYPE(*CHAR) LEN(3)
DCL VAR(&JUL) TYPE(*CHAR) LEN(5)
DCL VAR(&YR) TYPE(*CHAR) LEN(2)
DCL VAR(&D) TYPE(*DEC) LEN(5 0) VALUE(0)
DCL VAR(&Y) TYPE(*DEC) LEN(2 0) VALUE(0)
DCL VAR(&C) TYPE(*DEC) LEN(3 0) VALUE(0)
RTVSYSVAL QDATE &FIFTEEN
CVTDAT DATE(&FIFTEEN) TOVAR(&JUL) FROMFMT(*MDY) +
TOFMT(*JUL) TOSEP(*NONE)
CHGVAR VAR(&D) VALUE(&JUL)
CHGVAR VAR(&CYR) VALUE(%SST(&JUL 3 3))
IF COND(&CYR <= '015') THEN(DO)
CHGVAR VAR(&C) VALUE(&CYR)
CHGVAR VAR(&YR) VALUE(%SST(&JUL 1 2))
IF COND((&YR = '05') *OR (&YR = '09') *OR (&YR +
= '13') *OR (&YR = '17') *OR (&YR = '21') +
*OR (&YR = '25') *OR (&YR = '29') *OR +
(&YR = '33')) +
THEN(CHGVAR VAR(&D) VALUE(366))
ELSE CMD(CHGVAR VAR(&D) VALUE(365))
CHGVAR VAR(&Y) VALUE(&YR)
CHGVAR VAR(&Y) VALUE(&Y - 1)
CHGVAR VAR(&D) VALUE(((&Y * 1000) + &D) - (15 - &C))
ENDDO
ELSE CMD(CHGVAR VAR(&D) VALUE(&D - 15))
CHGVAR &JUL &D
CVTDAT DATE(&JUL) TOVAR(&FIFTEEN) FROMFMT(*JUL) +
TOFMT(*YMD) TOSEP(*NONE)
I cannot guarantee if it will work, but it seems to work in debug. Thre outfile portion I leave to you. And if you are questioning the previous year calc, you must account for the beginning of January and the fact that Julian only goes upto 365/366 dependent upon year.
==================================================================
Date calcs aren't easy in CL. One example is already given above, so I'll give an alternative that uses SQL date arithmetic.
<pre>
pgm
dcl &Func *char 1 value( 'P' )
dcl &Buff *char 256
dcl &BuffLen *int value( 8 )
dcl &Flag *char 2 value( X'0000' )
dcl &RtnCde *char 2 value( X'0000' )
dcl &x00 *char 2 value( X'0000' )
dcl &SysExt *char 22 value( ' ' )
dcl &PrvDate *char 8 value( ' ' )
chgvar %sst( &SysExt 21 2 ) &x00
/* Start the REXX procedure */
call QREXX ( +
'DATECALC ' +
'QREXSRC MYLIB ' +
x'00000000' +
'*EXECSQL ' +
&SysExt +
x'0000' +
)
/* +
strrexprc srcmbr( DATECALC ) +
srcfile( mylib/QREXSRC ) +
parm( *N ) +
cmdenv( *EXECSQL ) */
/* Pull the result from the REXX data queue */
call QREXQ ( +
&Func +
&Buff +
&BuffLen +
&Flag +
&RtnCde +
)
chgvar &PrvDate &Buff
dmpclpgm
return
endpgm
</pre>
Of course, CL doesn't exactly do SQL; so I put it into a REXX proc because REXX does do SQL
<pre>
/* REXX to subtract 15 days for CL... */
signal on error name command_error
signal on failure name command_error
signal on syntax name syntax_error
/* SQL runs here... */
PRVDATE = '0001-01-01'
sql_Stmt = ,
'values CHAR(DATE(CURRENT_DATE - 15 DAYS)) into ?'
'execsql PREPARE S1 FROM :sql_Stmt'
'execsql EXECUTE S1 USING :PRVDATE'
say RC '<'PRVDATE'>'
/* Since VALUES INTO isn't working, try the old way... */
'execsql ',
'SET OPTION DATFMT = *ISO '
sql_Stmt = ,
'select CHAR(DATE(CURRENT_DATE - 15 DAYS)) ',
' from sysibm/sysdummy1 '
'execsql ',
'PREPARE S2 FROM :sql_Stmt '
'execsql ',
'DECLARE C1 CURSOR FOR S2'
'execsql ',
'OPEN C1 '
'execsql ',
'FETCH C1 INTO :PRVDATE'
/* Check if it worked... */
say RC '<'PRVDATE'>'
/* Break out year month day... */
parse var PRVDATE year '-' month '-' day
say (year)(month)(day)
'execsql ',
'CLOSE C1'
/* Send out concatenated values to CL... */
queue (year)(month)(day)
exit
/* ---------------------------------------------------------------- */
command_error:
trace off
parse source system start srcmbr srcfile srclib
say 'Unexpected error at line 'sigl' of REXX program ',
srcmbr' in 'srclib'/'srcfile'. The exception ID is 'rc'.'
exit(right(rc,4))
/* ---------------------------------------------------------------- */
syntax_error:
trace off
parse source system start srcmbr srcfile srclib
say 'Syntax error at line 'sigl' of REXX program ',
srcmbr' in 'srclib'/'srcfile'. The error code is 'rc'.',
' The description is :'
say errortext(rc)
exit(rc)
</pre>
The REXX includes two different SQL ways to do it. The first uses VALUES INTO, which I never tried before. Unfortunately it's not working (yet). There are no errors; it just isn't returning the value. I couldn't find any PTFs, so I'll have to contact IBM. That's why I included the older way after that which is working.
The CL calls the REXX API to run the proc. You can replace all of that with a STRREXPRC command. After the proc returns, the value is pulled from the REXX external queue using the REXX queue API. The DMPCLPGM shows that the value is there.
The REXX procedure runs in the *EXECSQL command environment in order to make the code a little simpler. It's not running any CL commands, so the default *COMMAND environment can be overridden. It prepares and executes a simple cursor and fetches the result of the date calculation. The result is parsed into year, month and day portions -- that's just to get the date separator out. Those values are pushed as a group onto the REXX external queue to hand it over to CL. A couple spots display values to show what's working.
Strip out any bits that are not useful, and both become smaller. Although it's not pure CL, maybe it shows how CL and REXX can cooperate.
Technically, I suspect that your entire procedure would be better done in REXX than CL.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks, that has done the trick. I simplified things by ignoring leap years (all years are 365 days long) and increasing the number of old versions I keep on-line. I also use the routing to check for dupilcate member names which will protect me from downloading the file twice in a day.