RE: [EXTERNAL] Re: Converting large amount of data

Hi Vern
I was also told that same information - many moons ago
However, I have since found that using the logical is the correct way to go - especially if the logical has selects and/or omits
In this instance - the number of records processed in the logical is way smaller - and can process the data MUCH quicker
Now that I have said that, someone is going to say - NEVER use logicals, with a valid reason why

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D715BB.002FCFC0]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

'If you're going through hell, keep going.'
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Wednesday, March 10, 2021 2:08 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: Converting large amount of data

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<mailto: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<mailto:smith5646midrange@xxxxxxxxx>

<smith5646midrange@xxxxxxxxx<mailto: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?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>



The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.