RE: Help with SQL

All dynamic SQL statements (embedded dynamic SQL, STRSQL, ACS - Run SQL
Script, ...) are optimized per Default with *FIRSTIO
Static SQL satements (static embedded SQL) are optimized per Default with
*ALLIO

The optimization goal can be changed by adding OPTIMIZE FOR X ROWS to the
end of the SELECT Statement.
Replacing the x with a small integer no, means Optimization Goal *FIRSTIO
Replacing the x with a large integer no or with ALL means Optimization Goal
*ALLIO

The optimization goal will become important if the optimizer can only fine
sub-optimal indexes.
With *FIRSTIO it may decide for a sub-optimal index, with *ALLIO it may
prefer a table scan.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vernon
Hamberg
Sent: Samstag, 18. Januar 2020 04:04
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL

I think we're agreeing violently! Both Run SQL Scripts and STRSQL typically
involve a person entering statements and pressing Enter or Ctrl-R - that has
an "interactive" feel. Embedded SQL is can more often be running in batch,
esp. when processing sets of records, a very common scenario.

OK, back to sleep I go!
Vern

On 1/17/2020 12:45 PM, Charles Wilt wrote:

It's not really interactive vs batch...

It's OPTIMIZE = *FIRSTIO vs OPTIMIZE = *ALLIO

STRSQL and Run SQL Scripts default to *FIRSTIO, embedded SQL defaults
to *ALLIO

But either can be changed.

Charles

On Fri, Jan 17, 2020 at 11:42 AM Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx>
wrote:

This is also true - generally speaking interactive will try for
faster response, so it gets the first records. Batch tends to go for all.

The only situation that is truly interactive, IMO, is a display session.
Running statements in remote connections will tend toward the batch
approach.

Vern

On 1/17/2020 12:24 PM, Sam_L wrote:

Digging into my memory, seems to me that the optimizer may decide
differently on batch vs interactive execution. but it's been a while...

Sam

On 1/17/2020 11:49 AM, Vernon Hamberg wrote:

I agree with using Visual Explain - it would show any difference in
what the optimizer does.

Of course, when the environment changes, the optimizer might make
different choices.

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

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

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.

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