RE: sqlrpgle select statements

Can anyone tell me why it is when I use Visual Explain & create the indexes
it suggests, that it still shows I need to create the same thing the next
time I run the same SQL statement?

Debbie


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Matt.Haas@xxxxxxxxxxx
Sent: Thursday, March 02, 2006 8:14 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: sqlrpgle select statements

If you have select/omit criteria in the logical file, the query
optimizer will not consider it when determining the best access path.
When you include select/omit criteria in DDS based logical files, they
are both an index and a view at the same time which isn't normal in the
SQL world. The SQL book that explains the query optimizer is a very good
read if you are getting into SQL programming. Most things make sense
once you understand how it works but yes, there are times when you will
need to tweak the query a little bit (like adding flda=flda in the where
clause to get it to use the index with flda that it otherwise would
skip) to get it to use a better index but those types of tweaks are not
unique to the built in database.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, March 02, 2006 9:04 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: sqlrpgle select statements


One reason that I would recommend in using a(n) LF in a  SELECT/FROM, is
the SELECT/OMIT's within the DDS of the LF.  This might be the reason
"why
in the world _some_ SELECTs will run in a fraction of the time when an
LF
is named instead of the PF"


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


 

             qsrvbas@netscape.

             net

             Sent by:
To 
             rpg400-l-bounces@         rpg400-l@xxxxxxxxxxxx

             midrange.com
cc 
 

 
Subject 
             03/01/2006 08:26          RE: sqlrpgle select statements

             PM

 

 

             Please respond to

              RPG programming

              on the AS400 /

                  iSeries

             <rpg400-l@midrang

                  e.com>

 

 





rpg400-l-request@xxxxxxxxxxxx wrote:

>   3. AW: sqlrpgle select statements (HauserSSS)
>
>You should NEVER use a logical file in an SELECT-Statement!
>The query dispatcher that decices which query engine will be used, will
>reroute all SQL-Statements that use a logical file to the Classic Query
>Engine (CQE). This rerouting will cost up to 10% of performance.


Although I won't recommend specifying the LF name in a SELECT/FROM, this
does bring up the question of why in the world _some_ SELECTs will run
in a
fraction of the time when an LF is named instead of the PF.

I would expect that the answer would be found within DB2 group PTFs, but
I
don't really know.

Tom Liotta

--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertech.com