× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Double clicking on the message in Visual Explain Optimizer messages will
display the second-level text on mine.

-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxx]
Sent: Monday, July 20, 2009 4:31 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Table Scan Concern.

In Visual Explain you can see the messages from the optimizer -
unfortunately, not the second-level text, I think. One of those messages

will tell you at least one reason why an index was not used. You could
find the MSGID, I think, and display that on the iSeries, in order to
see the reasons that correspond to the codes.

HTH
Vern

Michael_Schutte@xxxxxxxxxxxx wrote:
It's possible. On V5R4. I understand the SQE to CQE handling. I'm
just
confused as to why SQE not using the logical.


--

Michael Schutte
Admin Professional



Try Bob Evans GRILLING SAUSAGE! This summer's hottest destination is
your
own backyard with Bob Evans Brats and Italian Sausage! For tasty
recipes
using Bob Evans grilling sausage, visit
http://www.BobEvans.com/Recipes






"DeLong, Eric"

<EDeLong@Sallybea

uty.com>
To
Sent by: "Midrange Systems Technical

midrange-l-bounce Discussion"

s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>


cc


07/20/2009 04:34
Subject
PM RE: SQL Table Scan Concern.





Please respond to

Midrange Systems

Technical

Discussion

<midrange-l@midra

nge.com>









Neill,

The CQE/SQE optimizer was phased in over several releases, so earlier
releases may have had additional constraints. He doesn't mention what
release he's using.

Regarding the use of logical file name in a query, I believe that CQE
was
able to take advantage of explicit access path references, but SQE
prefers
to parse the physical file object to locate associated access paths.
To
me, it sounds like his reference to the LF object is forcing CQE to
handle
the query.

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Neill Harper
Sent: Monday, July 20, 2009 3:15 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Table Scan Concern.

Hi Michael

From my understanding you hit the CQE if any of the following are in
your
statement:

INSERT WITH VALUES statement or the target of an INSERT with subselect
statement
Logical files
Tables with Read Triggers
Read-only queries with more than 1000 dataspaces or updateable queries
with
more than 256 dataspaces.
DB2(r) Multisystem tables
non-SQL queries, for example the QQQQry API, Query/400, or OPNQRYF

Otherwise you use the SQE.

It is generally accepted that the SQE is better/ faster than CQE,
however
perhaps in this instance you have found a query that the CQE is better
at
than the SQE.

If you want to know more I'd suggest some googling on the QE and CQE.

Neill

May be you have found a particular scenario where going through the
CQE is
faster than the SQE

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Michael_Schutte@xxxxxxxxxxxx
Sent: 20 July 2009 20:45
To: Midrange Systems Technical Discussion
Subject: Re: SQL Table Scan Concern.

I think that you might have it backwards.

When using the logical it works as expected, quickly. But using the
Physical, it does a table scan.

Good piece of information... the physical is a non-keyed physical.

Anyway have fun on vaca.


--

Michael Schutte
Admin Professional



Try Bob Evans GRILLING SAUSAGE! This summer's hottest destination is
your
own backyard with Bob Evans Brats and Italian Sausage! For tasty
recipes
using Bob Evans grilling sausage, visit
http://www.BobEvans.com/Recipes



midrange-l-bounces@xxxxxxxxxxxx wrote on 07/20/2009 03:31:25 PM:


Several issues. If you have select/omit logicals on the table it
could
be dropping into the old query engine. You need to specify in the
options

file

ignore the logicals with select/omit.

Going though the logical will do nothing. It will just using the
logical

to

get the physical file and then go looking for the indexes it thinks
is
correct.

As far as not using the copy in QTEMP did you create the table using
SQL using the generate temp file option? (DECLARE GLOBAL TEMPORARY
TABLE) If not, I don't think SQL would even know the table exists.

I am leaving on vacation so I can't research this further but others
can help you with more details.

On Mon, Jul 20, 2009 at 12:59 PM, DeLong, Eric

<EDeLong@xxxxxxxxxxxxxxx>wrote:

Are there a lot of logicals over that file? While I haven't seen
this myself for a long time, there was a time when the query
optimizer would timeout before it go to search a particular access
path, so the

optimizer

would punt (force table scan). I know I saw this prior to SQE...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of

Michael_Schutte@xxxxxxxxxxxx

Sent: Monday, July 20, 2009 1:28 PM
To: Midrange Systems Technical Discussion
Subject: SQL Table Scan Concern.


In several web programs, I thought I was being smart by creating
tables

in

QTEMP that would contain selection criteria to query against large
data files because we don't have logicals that met my selection

criteria.
I

have one that is acting kinda strange. It's not picking a locigal
by company and date over the large data file.

The QTEMP Table only has company and date in the file.

i.e.

001 20090601
001 20090602
001 20090604
001 20090605

Notice that one date is missing. The user wants to be able to select
certain days of the week. ie Weekdays only for an entire month.

Anyway,

I'm trying join this file to a large data file that does have an
index

by

company and date.

But VisualExplain is telling me that it's doing a table scan over
RTA60 because the cost is better. Which I know that isn't true. If
I

replace

RTA60 with the locigal name, it runs as I expect. I hate to use the

logical

in the query because I also have to give the user the ability to run

for

select restaurants (so there's another table in qtemp that has the

user's

selection of restaurants). But nevermind that for now. Just
company

and

date I get horrible response time when listing the physical in
the query. Anybody know why?

select a.*
from sar_dates b
join rta60 a on
a.tcono = b.company and a.tdate = b.dateiso

Does a table scan


select a.*
from sar_dates b
join rta60L9 a on
a.tcono = b.company and a.tdate = b.dateiso

Makes use of the RTA60L9 logical.

Thanks in advance for the help.




--

Michael Schutte
Admin Professional



Try Bob Evans GRILLING SAUSAGE! This summer's hottest destination is

your

own backyard with Bob Evans Brats and Italian Sausage! For tasty

recipes

using Bob Evans grilling sausage, visit
http://www.BobEvans.com/Recipes
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.



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

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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.