RE: New Product Announcement - Reorg without locking files

Regarding "In other words you haven't figured out how to do a reorg without
locking the file, just a way to trade in the time to other places." - 

No, we actually _have_ figured out how to reorg without locking the file.
You don't have to wait for anything to reorg the records with the
ReorgWizard.  The records are moved around without locking the file, and the
access path maintenance [for access paths defined as MAINT(*IMMED)] is done
_immediately_, for each record that is moved.  The immediate access paths
are not being 'rebuilt' while the ReorgWizard is running, they are
'maintained'.  This 'maintenance' is just like what happens when your
on-line program adds new records to the file.  Access paths are immediately
'maintained' while it's running.

In contrast, when you do a RGZPFM, OS/400 _removes_ the immediate access
paths and _rebuilds_ them from scratch when it's done reorganizing the
physical file.  Removing the access paths and rebuilding them may be more
'efficient' overall, but these rebuilds lock the physical file and all the
other logicals.  If you have a file that would take 30 hours to RGZPFM and
you don't have 30 hours, then RGZPFM is not an option.  With this thing, you
don't have to lock the file _at all_ to reorg the records.  While the access
path 'maintenance' is overall less efficient that an access path 'rebuild',
you don't so much care any more.

Let's assume a 20 hour RGZPFM for a file with 80 million active records and
20 million deleted records (100 million total).  Let's assume that the 20
hours consists of 10 hours of physical data movement (the reorg of the
actual physical file member) and 10 hours of access path rebuilds for the 30
logicals you have built over the file.  Using RGZPFM the file is absolutely
unavailable for 20 hours.  You're dead in the water.  Doing it with
ReorgWizard might result in 10 hours of data movement (the underlying
component of this movement of physical data doesn't really change) but now
_20_ hours of access path 'maintenance'.  You've gone from a 20 hour
_exclusive_ lock on the file where nobody can get anything done, to a 30
hour process to reorg the file _and maintain the access paths_ without
locking the file for a second.  It might take 30 hours total to run, but it
didn't lock the file _at all_, and nobody noticed that you were running it.
And, you can run 10 hours worth today and take a break, 10 hours worth
tomorrow and take a break, 10 over the weekend and finish it up, etc...  You
ended up in the same place.  It took longer to get there in elapsed time,
but your users weren't groaning 'are we there yet?? Are we there yet??'
every 10 minutes during the trip.

Up to this point we haven't locked the file _for a second_.  You now have
the performance benefits of reorganized data.

When you're done, your file has 80 million active records in the front, and
20 million deleted records in the back.  Those 20 million deleted records
can be 'chopped off' in a very short period of time, ranging from a few
seconds to a few minutes depending on how many millions of records you're
chopping off.  In any event, it's wwwaaaaayyyyy shorter than the 20 hour
benchmark you started with.  Unless you have 1000 active records and
100,000,000 deleted records.  In that situation, a regular RGZPFM would run
very quickly and you should have done it a long time ago...  But we're
mostly concerned about the files with 100 million active records and 30
million deleted records.  These are the killers.

Re the journaling - this was done to support commitment control.  Should
anything go awry during the reorg (or if someone should happen to use option
4 from WRKACTJOB on your reorg when they shouldn't have) the last few record
moves will be rolled back.  Nice and safe.  The journals obviously take up
space.  If you're not using journaling, it's turned on for you just for the
file being reorganized.  Journaling is ended when the reorg is finished.  If
space is a problem, you can set up the journal receivers to delete
themselves when they automatically change themselves.  This would minimize
the space impact.

I hope this makes more sense.  Please let me know if you need more info.

Thanks.
++++++++++++++++++++++++++++++++++++++++++++
Go... FASTER!  Without an upgrade!  With ARCTOOLS/400(tm)
http://www.arctools.com
info@arctools.com 
DCSoftware, Inc.
Ph: (508) 435-8243
Fax: (508) 435-4498
++++++++++++++++++++++++++++++++++++++++++++

 -----Original Message-----
From:   RKHBIT@aol.com [mailto:RKHBIT@aol.com] 
Sent:   Monday, February 10, 2003 3:10 PM
To:     bpcs-l@midrange.com
Subject:        Re: New Product Announcement - Reorg without locking files

So, instead of waiting for the system lock to extricate deleted records, we 
have to wait for a logical path to be rebuilt.  (Which happens during a
reorg 
anyway). Also we need additional space and resources available for the 
journaling aspect to be viable, and when exactly does the journaling stop?

In other words you haven't figured out how to do a reorg without locking the

file, just a way to trade in the time to other places.
_______________________________________________
This is the SSA's BPCS ERP System (BPCS-L) mailing list
To post a message email: BPCS-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/bpcs-l
or email: BPCS-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/bpcs-l.