AW: embedded sql session error in subfile

Hi,

SQL works differently than RPG native I/O does.
If specifying a logical file (joined or not), the query optimizer analyzes
the logical file.
It takes the field selection, row selection (select/omit) and join
information.
Then it REWRITES the SELECT-statement by joining these physical files
together and by adding where clauses.
After the query optimizer searches for the optimal access path for each of
these physical files.

If a logical file is specified in an SQL statement, the query dispatcher
will reroute the execution to the old CQE (Classical Query Engine). This
rerouting can cost between 10-15 % of performance.

If you do not like to join the files in your program, create a SQL view
instead.
Views are handled slightly different by the query optimizer than logical
files,
besides the query optimizer searches access path for each of the joined
physical files.
We have made the experience, defining a view (instead of joining directely)
will help the query optimizer to find the optimal access path.

Birgitta


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Joel Cochran
Gesendet: Dienstag, 26. Juli 2005 18:55
An: RPG programming on the AS400 / iSeries
Betreff: RE: embedded sql session error in subfile


On Tue, 2005-07-26 at 21:07, Douglas W. Palme wrote:
> I have a logical file which is built across four physical files.....they
are
> joined files....the sql statement works in the sql interactive mode, I do
> not see how the query optimizer is going to figure out from four separate
> physical files which logical file I am using, not to mention that the
reason
> I used a LF was to exclude certain records at the file level, instead of
> having the query system do it.

But that's what the Query Engine does...

You should perform your JOINs as part of your SQL, I'm not sure how the
Engine would handle such a logical file.  If it is something you are
going to use a lot, create a view out of the appropriate JOINs and use
that for your SQL.

Joel Cochran
http://www.rpgnext.com

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.