Re: reorg HUGE file

[D.BALE@xxxxxxxxxxxxx]

Al,

  >If I did a RGZPFM, I'd remove the members from the file's logicals
  >before the reorg, and then add them back after.  I think I've heard
  >it would be more efficient to do so.
>If and only if some of the access paths build off each other.  Clearly
>this will have positive performance implications if you have any
>multi-record format logicals, or LF joins.  Particularly beneficial if
>you have multiple files to re-org.

>Internal sequence is:
> all access paths are removed  (includes keyed physical and LFs)
> file is copies to a "hidden space" omitting deleted records
> file is moved back, with out a change of create date, etc.
> all access paths are rebuild

The eight logicals:  There are no join logicals, no multi-format logicals,
just simple access path with no record selection (except for one which selects
over 90% of the records in the file).  What happens, when the system rebuilds
the indexes, when you have logicals such as:
AOSDTLL2   Key: DTLMEM DTLODT
AOSDTLL3   Key: DTLMEM DTLODT DTLCLC DTLRPS DTLRPR DTLSKU
                  DTLPRO DTLBCC DTLBMP DTLBCF DTLECC DTLEMP
                  DTLECF
AOSDTLL4   Key: DTLMEM DTLODT DTLPIN DTLORD(D)
AOSDTLL6   Key: DTLMEM DTLODT DTLCLC DTLRPS
AOSDTLL8   Key: DTLMEM DTLODT DTLCLC DTLRPS DTLRPR DTLSKU
                  DTLPIN DTLORD

Noticing that L6 could use the access path of L8, and L8 could use the access
path of L3, and L2 could use the access path of any of the other listed
logicals, does the system recognize this and build L3 before L8, L8 before L6,
and build L2 last?  (What's the term that describes a logical file using the
access path of another?)  Or do key duplicates follow FIFO by default?  How do
I tell?

Would it be a fair statement to say that the manual effort I described
(CRTDUPOBJ in a separate library and CPYF the data, delete the original files,
move the new ones back to the original library) would gain me nothing over
using RGZPFM?

Dan Bale
IT - AS/400
Handleman Company
248-362-4400  Ext. 4952

-------------------------- Original Message --------------------------
At 07:47 PM 2/26/01 -0500, you wrote:
 >I've got a file with 40 million records, 10 million of which are
deleted.  The
 >file is over 14 GB.  There are 8 access paths built on this file.  We would
 >like to attempt a reorg of this file, but are concerned about the downtime
 >we'll experience as this file is used throughout our operations.  Is
there any
 >reliable way to estimate the time required to reorg?


There is no way to estimate.  REUSEDLT (Re-use deleted records) is a good
solution if you have NO programs that read the file sequentially and count
on the fact that the records are in sequential order by date/time
generated.  This is difficult to determine unless it is all your own code.

Lakeview sells a product that does a re-org while in use.  It requires
journaling.


 >FWIW, this is a model 510 running V3R7 and it has 94 GB of DASD, of which
 76%
 >is currently being used, and 392 MB of memory.


Relatively tiny system now-a-days.


 >If I did a RGZPFM, I'd remove the members from the file's logicals before
 the
 >reorg, and then add them back after.  I think I've heard it would be more
 >efficient to do so.


If and only if some of the access paths build off each other.  Clearly this
will have positive performance implications if you have any multi-record
format logicals, or LF joins.  Particularly beneficial if you have multiple
files to re-org.


 >Should I also consider duplicating the physical file in a separate
library and
 >using CPYF to copy the 30 million records into it, then duplicate the
logicals
 >over, then delete the original library's files, then move the newly copied
 >data over?

This is essentially what happens under the covers.

Internal sequence is:

all access paths are removed  (includes keyed physical and LFs)
file is copies to a "hidden space" omitting deleted records
file is moved back, with out a change of create date, etc.
all access paths are rebuild

Al




+--------------------------------------------------+
| Please do not send private mail to this address. |
| Private mail should go to barsa@ibm.net.         |
+--------------------------------------------------+

Al Barsa, Jr. - Account for Midrange-L
Barsa Consulting, LLC.  
400 > 390

Phone:          914-251-1234
Fax:            914-251-9406
http://www.barsaconsulting.com
http://www.taatool.com

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---