Delect members by age using CL

0 pts.
Tags:
i5
iSeries
OS/400
Programming Languages
tips and tricks
Tools
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
ASKED: November 9, 2004  11:19 AM
UPDATED: October 19, 2009  8:46 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DavidG1963
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following