Re: ITHL02 sort

["Genyphyr Novak" <novakg@xxxxxxxx>]

Hello,

This is a common misconception, and is especially bad if you think this way
while you are coding embedded SQL in programs! Using the name of a logical
file in an SQL statement does absolutely nothing in the way of
sort/selection, except that it guarantees the optimizer will CONSIDER using
the access path you have named. It won't use the path unless the path fits
the statement you have listed AND is the fastest way to get the query
completed based on what the OS/400 optimizer thinks. It will use the
physical file or even a different logical, to return the records you are
requesting in your query, unless you specifiy selection/ordering criteria
matching that logical file. If you specifiy this sort of thing, then SQL
optimizer will find the best access path to get the data returned to either
the program or screen the fastest. So, naming a logical file in a SELECT *
FROM ITHL02 will only result in selecting everything from the ITH file,
likely using a table scan to return the records.

Use a native file utility in order to view native file types on the AS/400
if you want to check integrity or whatever else you were trying to check
about that logical. SQL is a cross platform utility, and as such does not
care about or use logicals in the same way as native tools would.

Thanks

Genyphyr Novak
SSA

-----Original Message-----
From: Paul Holstein <holstein13@excite.com>
To: BPCS-L@midrange.com <BPCS-L@midrange.com>
Date: Thursday, August 03, 2000 2:28 PM
Subject: Re: ITHL02 sort


>I am running an SQL statement on the logical:
>
>Select * from ITHL02
>
>I will try DFU next week.  However, I could have sworn that other SQL
>statements on other logical files did respect the sort without specifying
it
>in the query.
>
>Thanks for the posts.
>
>Paul Holstein
>iWork Software, L.L.C.
>
>
>On Wed, 2 Aug 2000 10:45:22 -0600, BPCS-L@midrange.com wrote:
>
>>
>>  How are you looking at the data? Query does not sort the data only the
>>  selection criteria is honored.
>>  EZVIEW & DFU honor both the selection as well as the sort criteria.
>>
>>  Chris Ertz
>>  Software Development Manager
>>  American Metal Products
>>
>>
>>
>>
>>
>>  Paul Holstein <holstein13@excite.com> on 08/01/2000 02:02:31 PM
>>
>>  Please respond to BPCS-L@midrange.com
>>
>>  To:   BPCS-L@midrange.com
>>  cc:
>>  Subject:  ITHL02 sort
>>
>>  ITHL02 is supposed to be sorted by TPROD, and TSEQ.  However, whenever I
>>  look at the file, I notice it is not sorted at all.
>>  I did a DSPFD on ITHL02 and it says it is sorted ascending by the two
>>  fields.  Does anyone else have this problem?  How do I (or should I) fix
>>  this?  Seams like this could make a big difference in performance.
>>  --Paul Holstein
>>  iWork Software, L.L.C.
>>
>>
>>
>>
>>  _______________________________________________________
>>  Say Bye to Slow Internet!
>>  http://www.home.com/xinbox/signup.html
>>  +---
>>  | This is the BPCS Users Mailing List!
>>  | To submit a new message, send your mail to BPCS-L@midrange.com.
>>  | To subscribe to this list send email to BPCS-L-SUB@midrange.com.
>>  | To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
>>  | Questions should be directed to the list owner: dasmussen@aol.com
>>  +---
>>
>>
>>
>>
>>
>>
>>  +---
>>  | This is the BPCS Users Mailing List!
>>  | To submit a new message, send your mail to BPCS-L@midrange.com.
>>  | To subscribe to this list send email to BPCS-L-SUB@midrange.com.
>>  | To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
>>  | Questions should be directed to the list owner: dasmussen@aol.com
>>  +---
>
>
>
>
>
>_______________________________________________________
>Say Bye to Slow Internet!
>http://www.home.com/xinbox/signup.html
>
>+---
>| This is the BPCS Users Mailing List!
>| To submit a new message, send your mail to BPCS-L@midrange.com.
>| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
>| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
>| Questions should be directed to the list owner: dasmussen@aol.com
>+---
>

+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---