Hi,
Well... almost always it's because it's _not_ the "exact same
statement." Often there are host variables or other elements that
require changing between the two.
Even with the same host variables i.e. values static SQL in an embedded SQL
program and iteractive SQL may differ, because of the different
opitimization goal.
All dynamic SQL statements (such as embedded dynamic SQL, interactive SQL,
JDBC, ODBC ...) are optimized to return the first block of the result set as
fast as possible. (Optimization goal is *FIRSTIO) In this way an index may
be used even it is not considered as being optimal.
Static SQL statements (embedded static SQL) is optimized to return the
complete result set as fast as possible (Optimization goal is *ALLIO). In
this way a table scan may be prefered even if there is a sub-optimal index.
In this way either OPTIMIZE FOR x ROWS at the end of the SELECT-Statement
will effect the optimization goal.
If is a small integer, the optimization goal *FIRSTIO is used while if ALL
ROWS is specified the optimization goal is *ALLIO.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Tom Liotta
Gesendet: Wednesday, April 02, 2008 22:52
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Classic Traps -- I need your input!
Scott Klement wrote:
Folks, I'm working on an article that's tentatively titled "Classic
Traps and How to Avoid Them". The general idea is to discuss the classic
mistakes that programmers make while programming, and (where necessary)
explain how to avoid that.
One just came to mind that we see here regularly -- "The exact same
statement works in interactive SQL. Why doesn't it work in my program?"
Well... almost always it's because it's _not_ the "exact same
statement." Often there are host variables or other elements that
require changing between the two.
Use statements in the environments for which they are intended. And
if _anything_ changes, then start looking there for the resolution.
Tom Liotta
midrange.com
