Re: Creating Logicals before or after populating a file ? -- MIDRANGE-L

Evan,

  If you choose option 2, what must happen is that as each record is
added to the file the system must maintain EACH of the 20 logical
files.  If you add the records in a poor order for a particular logical
(this is inevitable for at least several of them) the system must work
to prevent vining of the access path by keeping it balanced.  In
addition it is working with all 20 of them plus adding to the PF all at
once.

  If you choose option 1, you fill the PF with records first.  Then as
each of the LF's is built the system can extract all the keys from the
PF for that access path, sort them (sorting is nearly Always faster than
insert after insert), and build a nicely balanced access path.  It can
use all available memory for this task with little competition. If you
have DB/2 SMP installed on a multiple processor system it will even use
all processors to do it even faster.

  You can accomplish the same thing by building all the files but
removing the members from the logical files.  Then after populating the
PF, one by one add the members to the LFs.  The access paths will be
built at that time.

  Of course if all the LF's are MAINT(*DELAY) or MAINT(*REBUILD) then
it's all mute because the access paths aren't built until you try to use
them. (Which will leave a BIG dent in performance for a while!)

  Also as was mentioned in another response to your question, do be sure
to build the access paths in order of most detailed to least detailed so
as to maximize the opportunity for sharing of access paths.  Just like
Mom said, Sharing is good!

  Larry Bolhuis
  Arbor Solutions, Inc
  lbolhui@ibm.net

Evan Harris wrote:
> 
> Larry:
> 
> Ok then, I'm asking about why ! I realize that there is more control over
> the process by creating the logicals separately and that they can be
> created in tandem etc, it would certainly help if I am able to provide the
> other people involved in this exercise with some reasons.. plus I'm kind of
> interested
> 
> Thanks for your reply so far anyway !
> 
> At 21:54 14/10/98 -0400, you wrote:
> >  ABSOLUTELY and POSITIVELY Option 1 is better.
> >
> >  Been there, Done that, Ate the stale pizza.
> >
> >  If you would like a discussion of why this is true, just ask..
> >
> >  Larry Bolhuis
> >  Arbor Solutions, Inc
> >  lbolhui@ibm.net
> >
> >> We are about to embark on an exercise that will populate a *large*
> >> database file (40 million records plus). This file has about 20 logical
> >> files built over it.
> >>
> >> My question is, which is more efficient:
> >>
> >> 1. Populating the file and building the logicals ?
> >>
> >> 2. Building the logicals and populating the file ?
> >>
> >> Any thoughts would be greatly appreciated.
> >>
> 
> +---
> | 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
+---