Re: Estimating the Time required to Build Access Paths

["Eric DeLong"<eric.delong@xxxxxxxxxxxxxxxxx>]

I haven't found anything more specific than the following:

>From "DB2 for OS400 Database Programming": 
-------------------------------------- 3.4.3.2.1 Rebuilding Access Paths
Rebuilding a database access path may take as much as one minute for every 
10,000 records.

Note:  This estimate should be used until actual times for your 
system can be calculated.

The following factors affect this time estimate (listed in general order of 
significance):

   Storage pool size.  The size of the storage pool used to rebuild 
the access path is a very important factor.  You can improve the rebuild time by
running the job in a larger storage pool.

   The system model.  The speed of the processing unit is a key 
factor in the time needed to rebuild an access path.

   Key length.  A large key length will slow rebuilding the access path 
because more key information must be constructed and stored in the access path.

   Select/omit values.  Select/omit processing will slow the 
rebuilding of an access path because each record must be compared to see if it 
meets the select/omit values.

   Record length.  A large record length will slow the rebuilding of an 
access path because more data is looked at.

   Storage device containing the data.  The relative speed of the 
storage device containing the actual data and the device where the access path 
is stored has an effect on the time needed to rebuild an access path.

   The order of the records in the file.  The system tries to rebuild an 
access path so that it can find information quickly when using that access path.
 The order of the records in a file has a small affect on how fast the system 
can build the access path while trying to maintain an efficient access path.


All of the preceding factors must be considered when estimating the amount of 
time to rebuild an access path.


Eric DeLong


______________________________ Reply Separator _________________________________
Subject: Estimating the Time required to Build Access Paths 
Author:  <MIDRANGE-L@midrange.com > at INET_WACO
Date:    10/22/98 3:04 PM


Hi,
     
I am currently working on a system that is very over utilized from both a 
performance and disk space perspective.  We are currently purging old data 
(delete phase), and then will have to reorganize files. Of course the 
system has many to many LFs. 
     
I have a two hour window nightly from end of batch/backup to when the 
system has to be back for the users.
     
I am looking for any kind of algorithm or formula to help me assess how 
long the access path build portion of the re-org will take.
     
Any ideas?
     
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-9400
Fax:        914-251-9406
     
     
+---
| 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
+---