Re: SQL select via logicals

[Vern Hamberg <hambergv@xxxxxxxxxxxxxx>]

To all

At 03:41 PM 12/6/97 -0500, you wrote:
>
>
>RE:    Re: SQL select via logicals
>
><SNIP>
>No, your records will not necessarily be returned based upon the S/O
criteria.
>SQL evaluates the n (used to be five, may now be ten or more) oldest logical
>files by creation date based upon the ORDER BY and WHERE clauses to decide
>whether or not it needs to build an access path.  If you have an ORDER BY
>statement that doesn't match the WHERE (select/omit) and key of those older
>logicals, you may not get the logical specified in your SELECT statement.
><SNIP>
>Dean Asmussen
>
>Hey Dean
>
>I thought it looked at the oldest first too.  But Kent Milligan of Rochester
>DB2/400 said the following;
>
><Kent Milligan said;>
>"A couple of ways to influence the optimizer is by specifying the logical 
>file directly on the SQL statement so that the optimizer does look at that
>access path first.  The other trick is recreating the index - due to the 
>way index information is organized in the underlying database file object
>the optimizer is typically given indexes in a most recently created order
>(eg, first index examined, tends to be the index most recently created). 
>These methods provide ways of influencing the optimizer, but aren't 
>guaranteed to work in all cases."
>
>So I guess it's the Newest to Oldest.

Maybe we should end this discussion with a suggestion: Don't use S/O
logicals at all in SELECT statements. Rather, use the physical (or some
preferred logical) and specify the same criteria in the WHERE clause that
are specified in the S/O logical. Then let the system take care of itself.

Why this suggestion? Standard SQL knows diddly about AS/400 idiosyncracies.
It may be preferable, in a multi-platform world, to avoid mixing idioms.

Just a thought—

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---