RE: Re-Organize File (originally on RPG400-L)

(I have taken the liberty of replying to this original RPG400-L post on
MIDRANGE-L.  The original poster asked about the time required to reorg a
huge file.  Responders suggested looking into reorg-in-place commercial
products.  The thread history in this context follows my response.)

In particular, I am writing about the technique that this "product" uses
(and I presume that other "products" i.e., Turnover, use this same technique
as well).  Generally speaking, it seems that the general technique is as
follows:

1) Create empty duplicates of the physical and its logicals in a new
temporary library.
2) Start journaling the file.  (before only?  before & after?)
3) Submit a job to CPYF the production physical to the temporary physical
4) After step 3 finishes, start applying journal changes to the temporary
physical (don't know if this is a home-grown app or if command will let you
do this to a different file from the journaled file).
5) Once step 4 is pretty much "caught up", find a spot of time to grab
exclusive use of the production file, apply the last of the journal changes,
rename the original production physical and its logicals, and move the files
from the temporary library into production.

Is this pretty much it?  This really shouldn't be beyond the capabilities of
most experienced AS/400 pros, unless there's one or more small tricks or
gotchas that need to be considered.  Plus there's the fact that the typical
candidate file is usually a critical file required for the continued
operation of a company; mess it up in any small way, and the bankruptcy
lawyers sign your next check.  I'm thinking there may be other
considerations that are linked to the original file object that does not
relink to the new file object, i.e., triggers?  Others?

Of course, if the commercial apps to do this type of reorg is reasonably
priced, then it may be more penny-wise and safer to use that type of
solution.

- Dan Bale
(I am *NOT* "Dale"
http://archive.midrange.com/midrange-l/200105/msg00281.html )
SAMSA, Inc.
989-790-0507
DBale@SAMSA.com <mailto:DBale@SAMSA.com>
  Quiquid latine dictum sit altum viditur.
  (Whatever is said in Latin seems profound.)

-----Original Message-----
From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
Behalf Of Nicolay, Paul
Sent: Thursday, April 18, 2002 9:07 AM
To: 'rpg400-l@midrange.com'
Subject: RE: Re-Organize File


HI,

http://www.iterainc.com/products2/reorg_while_active.doc is one of them...
don't have experiences with it however.  I think I saw different ones in the
Midrange newsletter but can't find it immediatly (maybe it was even the
same).

Kind regards,
Paul

-----Original Message-----
From: JJW [mailto:rpgilejjw@charter.net]
Sent: 18 April, 2002 14:45
To: rpg400-l@midrange.com
Subject: Re: Re-Organize File


Not what I wanted to hear, but what I had expected to
hear.  Thanks for the quick responses.

Anybody know of any products that can re-org files faster
than RGZPFM or CPYF?

TIA!!!

On Thu, 18 Apr 2002 08:24:30 -0400
  "Fisher, Don" <Dfisher@roomstoreeast.com> wrote:
>We had a file similar to that on our model 720 with two
>processors and it
>took DAYS to execute an RGZPFM command.
>
>On the other hand, copying the file, as Paul suggested,
>only took about
>eight hours.  Granted, the file only had about four
>access paths to rebuild.
>
>Hope that helps.
>
>Donald R. Fisher, III
>Project Manager
>The Roomstore Furniture Company
>(804) 784-7600 extension 2124
>DFisher@roomstoreeast.com
>
><clip>
>Given the following: I have a file that has Total records
>= 75 million and Total deleted records = 50 million and
>there are 25 logicals attached and total record length is
>442.
>
>How long would RGZPFM take to run over this file?
><clip>