RE: SQL Optimizer and LF with *OMIT fields

I have never seen the SQL ignore select/omit criteria from a LF. 

Now if the select/omit criteria is not there and you use a logical file,
it simply ignores the logical and goes to the physical and does an
analysis. 

SQL should always be based on a physical file. The logicals are simply
ignored unless it is select/omit. The index of the logical means
absolutely nothing to it. It always does it own analysis to determine
what is best. There is a way to override that which I will explain
below.   

From what I have read in V5R3, it will go to the new SQE engine even

through a logical unless you have any(!!) logicals on the physical that
is using hardcoded select/omit criteria, that is to say you are not
using DYNSLT. In that case, it drops into the old SQL Engine.

That is why when working with SQL you want to have no logicals defined
with hardcoded select/omits. Unfortunately, unless you have new files,
this is almost impossible because people have defined all kind of
logicals with hardcoded select/omits.  

-------------------------
Overriding default joins.

You can override the behavior of the analysis by using an option in the
QAQQINI options file. 

The option is "FORCE_JOIN_ORDER". If this true, the SQL runtime will not
attempt to pick, what it thinks, is the most efficient way to run this
query. Instead it assumes you know what you are doing and processes in
the order specified in the joins. 

SQL Server has in its Stored Procedure language something called SET
FORCEPLAN { ON | OFF } that does the same thing and makes it easy.
Unfortunately IBM does not make it easy. 

In order to change the value, you make a CRTDUPOBJ (Important) copy of
the QAQQINI file to QTEMP, update the value of "FORCE_JOIN_ORDER" to
*YES and issue a CHGQRYA to point to temp copy and then run your SQL and
when you are done, set it back to QUSRSYS. In other words, a royal pain.


It is important to never update the copy in QSYS or QUSRSYS because that
means that everyone will get that option and that is not a good thing. 

I wrote a little service program called XVSETO (Set SQL Option) that can
be called from CL or RPG/ILE that does all this automatically if anyone
is interested I can send it or I can post to
www.think400.dk/downloads.htm. 

One hassle is you must do a CRTDUPOBJ to make a copy but the default
security is *USE so it bombs if you try to copy the file so you must
either loosen the security on the copy in QUSRSYS or put a local copy
with lower security in some other library or run the XVSETO program with
adopted authority. 

There are other options in V5R3. Here is the text from the QAQQINI file.


Specifies that the join of tables is to occur in the order specified in
the query. QQVAL: 

*DEFAULT--The default value is set to *NO. *NO--Allow the optimizer to
re-order join tables. 
*SQL--Only force the join order for those queries that use the SQL JOIN
syntax. 
*PRIMARY nnn--Only force the join position for the table listed by the
numeric value nnn (nnn is optional and will default to 1) into the
primary position (or dial) for the join. 
*YES--The join will occur in the order in which the tables were
specified in the query.

Hope this makes sense.