Re: Remove Old members from a physical file

On 07-Oct-2010 05:36, Raja Nama wrote:

I have a multi member physical file. Need to remove
the members from the physical file which are
more than 90 days old. What is the best way to
perform this task?

The following is _an_ approach to perform that task, where CMDEXEC is an SQL function which processes a CL command string.

<code>

with
gen_fdmbr (since, i) as
( select
right(replace(char(current_date-90 days),'-',''), 6)
, cmdexec('dspfd crp1/qsqlsrc *mbr output(*outfile)
outfile(qtemp/fdmbr)')
from qsys2/qsqptabl)
select 'rmvm ' concat rtrim(mblib) concat '/' concat
rtrim(mbfile) concat ' ' concat mbname
, since , MBFCCN, MBFCDT
, MBCHGC, MBCHGD, MBUPDC, MBUPDD
, MBSAVC, MBSAVD, MBRSTC, MBRSTD
, MBUCEN, MBUDAT, MBUCNT
, MBTCEN, MBTDAT, MBSTFR
from qtemp/fdmbr
cross join gen_fdmbr
where MBFCDT<since

</code>

- On supported releases, replace the CTE SELECT with VALUES. Or possibly use a UDTF to better encapsulate the effect of DSPFD which could be achieved instead using the list member API QUSLMBR; the expression for "SINCE" can be moved elsewhere.

- Consider utilizing some [probably most] of the other column names in the final SELECT column-list to enable more appropriate decision-making about which members are truly eligible; i.e. using just the creation date may easily allow an undesirable result. For Understand where blanks may exist and why, in including a member in the criteria.

- Use a function to combine the century and character YYMMDD values from the file; to use in comparison with the expression that generates values for column SINCE. Note the WHERE clause in the example code does *not* function properly as written unless all rows represent the century count of one; i.e. the alluded SQL function combining the century and *YMD value would correct that.

- The string expression in the final SELECT can be input to the CMDEXEC function to perform the CL request, so running just the one statement both generates the member list and then removes the selected members.

Note: there is a catalog table or view named SYSPSTAT or SYSPARTITIONSTAT which may have the creation date. IIRC a VIEW which uses a UDTF [User Defined Table Function] which provides the equivalent of the CTE in the given example.

Regards, Chuck