RE: Most Efficient Way of Purging File

Not to mention journaling. You're quite right. There are more
considerations.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, December 08, 2008 1:27 PM
To: Midrange Systems Technical Discussion
Subject: RE: Most Efficient Way of Purging File

Add these steps...
Fix the owner and other related authority on the file.
Replace any triggers, constraints, and other file attributes.
Resolve any check pending constraints caused by deleting records
constrained to another file.
Ensure that the new ROWID set up on the file won't cause duplicates or
other issues.

Man, some of these suggestions are assuming that you are still running
an AS/400 and not an i.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"Albert York" <albertyork@xxxxxxxxx>
To:
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date:
12/08/2008 01:21 PM
Subject:
RE: Most Efficient Way of Purging File
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Remove the logicals
Copy the records you want to keep to a file in another library
Delete or move the original file
Move the new file in place
Rebuild the logicals

Albert

----- Original Message -----
From: "Dan Kimmel" <dkimmel@xxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Subject: RE: Most Efficient Way of Purging File
Date: Mon, 8 Dec 2008 11:00:47 -0600



DLTPFM on all logicals.
Save a copy of the file. Do not save access path.
CLRPFM
Restore the copy to another name.
Copy the records you want to keep back to the original.
ADDPFM on all logicals.

A couple tricks in this: Dropping the logical members (DO NOT delete

the

logical files, just the members) removes the system's need to maintain
the logicals on every change to the physical. You won't need the

access

paths on the save/restore, saves some time and space. CLRPFM is very
fast (almost instantaneous no matter how many records) and removes all
deleted records eliminating the need for RGZPFM. The restore will go
fast as it is just the data. Use CPYF or SQL to copy the records you
want back into the original. Each ADDPFM will rebuild the access path

in

the logical over all the records in the physical, seems to be much
faster than maintaining the logical access path as each record is

added

on large files.

We do this regularly on files of 10 million records or more and get it
done in well under an hour on really slow machines. I should think

even

your 2 billion record file would work in less than 10 hours provided

you

have a fairly quick machine. (And you must have a fairly quick machine
to get 2 billion records in the table in the first place.)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: Monday, December 08, 2008 4:19 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Most Efficient Way of Purging File

We have a file that has an enormous number of records in (just over

1.9

billion) and want to clear it down a bit to reclaim some much needed
DASD. The trouble is that because of the file size our standard purge
routine takes longer than the available 17 hour window that we have

for

running housekeeping tasks.

At the moment we think we have two choices available to us:

1. Use SQL DELETE to remove all the records we want to purge and then
run a RGZPFM over the file

2. Restore a copy of the file from tape to a second server, and use

CPYF

to create a new version of the physical with only those records we

want

to retain and then restore that over the original. We think this will
automatically perform the RGZPFM as part of the restore/copy.

Does anybody have any experience or advice on performing this sort of
"purge", ideas on how to improve performance, etc, etc.

We're running V5R4 and there are eight logicals over the file.

Thanks

Jonathan








_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________




--
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.