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
As an Amazon Associate we earn from qualifying purchases.