Re: Converting large amount of data

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Brace yourself for just a bit more TMI - not sure you are suggesting 
this, but we are told not to use any LF in the SELECT statement. If we 
do, the optimizer goes out to find the PF, then does what it would have 
done to find the best plan.

Basically, the optimizer won't use an LF just because we put it in the 
SELECT table list.

I believe DYNSLT is more relevant for non-SQL IO. Maybe we are talking 
at cross-purposes, however.

Cheers
Vern

On 3/10/2021 11:36 AM, Gad Miron wrote:
> Vern
>
>   TMI indeed
>
> I figure that a large DYNSLT LF with a large number of records that do not
> meet the Select/Omit criteria will cause this phenomenon. .
>
> HAND (Have A Nice Day)
> Gad
>
>
> >
> date: Wed, 10 Mar 2021 06:50:04 -0600
> > from: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
> > subject: Re: Converting large amount of data
> >
> > Gad, I don't think "always" is the operative word. It depends - a
> > select/omit LF is already an index and will often be used if the WHERE
> > clause matches the S/O specifications.
> >
> > The SQL optimizer will work through existing indexes, including
> > DDS-based LFs, to find one that is suitable for selectivity and joining
> > and grouping, as well as column specification, etc.
> >
> > BTW, there used to be an option in the QAQQINI, IGNORE_DERIVED_INDEX,
> > that defaulted to *YES - S/O LFs are derived-key indexes. That setting
> > is no longer in the QAQQINI, probably especially so because we can now
> > create similar indexes with SQL CREATE INDEX itself.
> >
> > TMI, right?? :)
> >
> > Cheers
> > Vern
> >
> > On 3/9/2021 11:58 PM, Gad Miron wrote:
> > > Just a thought...
> > >
> > > If the LF has a Select/Omit part then an index will always be built when
> > > opening the LF
> > > Won't it?
> > >
> > > Gad
> > >
> > > >    > On Tuesday, March 9, 2021, 3:10:18 PM EST,
> > smith5646midrange@xxxxxxxxx
> > > > <smith5646midrange@xxxxxxxxx> wrote:
> > > >
> > > > I?m resurrecting this old post because I have a problem.
> > > >
> > > > We have rewritten our conversion to use CHGPF where we can.? Today
> > someone
> > > > tried to DBU a logical file built over a huge physical (182M records)
> > and
> > > > it took 15 minutes (this is a severely restricted machine) and it said
> > > > something about rebuilding the index.?
> > > >
> > > > I thought CHGPF updated the indexes while it was running (excluding the
> > > > mismatched ones that we discussed earlier).? We can?t use the CHGPF if
> > that
> > > > is going to delay the normal processing the first time that it tries to
> > use
> > > > the index.
> > > >
> > > > Is there something I missed in all of this?