× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.