RE: Remove Old members from a physical file

SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, SYSTEM_TABLE_MEMBER,
LAST_CHANGE_TIMESTAMP FROM syspartitionstat WHERE
SYSTEM_TABLE_SCHEMA='ROB' AND SYSTEM_TABLE_NAME='QPGMSRC'
and date(last_change_timestamp)>current date - 300 days


LIBRARY FILE SYSTEM_TABLE_MEMBER LAST_CHANGE_TIMESTAM
NAME NAME P
ROB QPGMSRC SKELETONC 2009-12-22-09.05.17.000000
ROB QPGMSRC A01 2009-12-22-08.31.12.000000
ROB QPGMSRC A 2009-12-14-16.17.57.000000
ROB QPGMSRC SQLSAMPLE 2009-12-17-11.06.24.000000
ROB QPGMSRC AA2 2010-01-12-14.39.23.000000
ROB QPGMSRC ML1 2010-01-18-14.28.02.000000
ROB QPGMSRC ML2 2010-01-18-14.24.17.000000
ROB QPGMSRC ML3 2010-01-18-14.28.44.000000
ROB QPGMSRC S813C5V7 2010-01-22-09.56.57.000000
ROB QPGMSRC A1SQL 2010-02-10-13.22.15.000000
ROB QPGMSRC A1SQL2 2010-02-10-11.16.17.000000
ROB QPGMSRC JARED 2010-02-24-16.42.05.000000
ROB QPGMSRC TROY 2010-06-30-14.48.23.000000


Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 10/07/2010 01:50 PM
Subject: Re: Remove Old members from a physical file
Sent by: midrange-l-bounces@xxxxxxxxxxxx



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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.