Re: SQL select via logicals

[Vernon Hamberg <hambergv@xxxxxxxxxxxxxx>]

David

At 09:54 AM 12/2/1997 -0700, you wrote:
>>>> Vernon Hamberg <hambergv@goldengate.net> 12/01 9:00 PM >>>
>>David
>
>At 02:26 PM 12/1/1997 -0700, you wrote:
>>>>> John Carr <74711.77@compuserve.com> 11/30 10:18 PM >>>
>>
>>>>RE: Re: SQL select via logicals
>>
>>>>I also think it would be good to have a mechanism to force the inspection
>>>>of all indexes before deciding to Sort, or Copy the data or Build an Index
>>>>on the fly. 
>
>>>>But I can't imagine how they would implement it.  I can see a new
parameter
>>>>on OPNQRYF but can't see how they would implement it in SQL. Unless it was
>>>>a system wide System Value.
>
>>>>John Carr
>
>>>A system value and option on CHGQRYA would be better than nothing.
>
>>>We have had some problems with the optimizer.  In a few cases it used 
>>>a logical  for months and then, for no obvious reason, it times out and 
>>>consistently builds an access path.  When you don't know what the access 
>>>plan was it is difficult to determine why it is no longer being used.
In these 
>>>cases forcing the optimizer to look at possibilities would help.
>
>>The changes you describe may have to do with changes in the file size.
>>That's one of the factors used by the optimizer, which estimates the cost
>>of performing the given query. Now, if you're talking embedded, the access
>>plan is stored in the *PGM and would not change without some severe change.
>>If you're talking OPNQRYF or interactive SQL or QMQRY or dynamic embedded
>>SQL or SQL CLI, then the access plan is redetermined every it's run.
>
>Dynamic SQL.


OK, so the access plan may be recalculated each time, no matter what. But
does anyone know about replacement variables (:customer, e.g.) in a
prepared dynamic SELECT? Are these saved in an SQL package, thus preserving
the access plan? This is, in fact, the case with extended ODBC, e.g.


>>Check out the Database Programming manual, the Data Management Guide, and
>>the SQL Programming and Reference manuals. There are appendices that deal
>>with query performance, as well as using STRDBG to see what the optimizer
>>did. Changing the order of the files, whether in OPNQRYF or in an SQL
>>statement, can have profound affects on the time it takes to execute a
query.
>
>Spent many hours doing that.


Yeah, been there, done that!


>>Anyway, in the kindest possible way, RTFM. There's a lot of really
>>excellent material that will help you in setting up these things.
>
>>BTW, one thing you should almost always do (some OVRDBF issues aside, I
>>think) is set the ALWCPYDTA(*OPTIMIZE) parameter of OPNQRYF.
>
>ALWCPYDTA and OPTIMIZE have no effect when using For n Rows.  We use 
>For n Rows based on file information because we cannot recompile each time 
>a report is run.


Oops, sorry—my remarks here were addressed only to the use of OPNQRYF. But
isn't htere a SET OPTIONS clause in embedded SQL? Does that have what you
need?


>Thanks for the suggestions. I have had to spend quite a bit of time trying
to 
>determine what slight change in the data caused the optimizer to determine 
>that building an access path was best.  Rather than spending hours to save 
>a few cycles I would rather be able to tell the optimizer I want you to
use an 
>existing access path because it worked well before and a I know it is better 
>than building a new access path.
>
>We do a lot of very dynamic reporting using SQL and it is not possible to
predict 
>how a file might be queried by a user this week.  We keep information
about our files 
>and use this when building select statements to apply the best access
method (join
>order, n rows, etc.).  We generate our SQL statements from files that tell
us how our 
>files are related etc.  We save the statements and have seen the exact
same statement 
>go from 1 minute to 2 hours.  If experience from the prior 300 runs tells
us that with the 
>selected files it has proven better to use an existing access path, we
would like to at 
>least be able to tell the optimizer not to give up easily.


I hear you.


>One thing that used to be (still?) very annoying was when an file was open
for update 
>the associated access path was not considered.  Not mentioned in the
manual and not 
>considered a bug.  It took a lot of time to find this and got us a bill
for several thousand 
>dollars from IBM.  We switched to updating through the unkeyed physical to
avoid this.

Anyone know if this is related to SEQONLY(*YES) that is sometimes forced on
files?

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN  55401-2499
(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
+---