RE: Strange SQL behavior with logicals

Ben,

I'm looking around for links that might help, but for a first look, you may
want to review the QAQQINI configuration file.  Here's a link to V5R3
information center that explains the QAQQINI options...

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq
/qryopt.htm

Notice that the entire chapter in that book is about query performance and
optimization...

Here's another great link to the IBM Teraplex resource site...  I had to go
and find this one all over again. (Thanks IBM...)
http://www-03.ibm.com/servers/enable/site/bi/teraplex/lessons.html

happy hunting,

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
Ben_Pforsich@xxxxxxxxxxxx
Sent: Wednesday, December 14, 2005 10:26 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Strange SQL behavior with logicals



This is strange.  I've got two issues with the SQL query optimizer on how
it chooses access paths for a particular query.  First, I'm getting
different results depending on which path it chose.  Second, it also
appears to choose a different access path depending on the ORDER in which
they were built.  Has anyone else experienced this or can explain why this
is?

I have an interactive SQLRPGLE program that declares and uses the following
SQL cursor over a workfile JVP110WK:
   select J1LOAD, case when min(J1ORDN) <> '0000000' and
                            max(J1ORDN) <> '0000000'
                       then count(distinct J1ORDN)
                       when min(J1ORDN) = '0000000' or
                            max(J1ORDN) = '0000000'
                       then count(distinct J1ORDN) - 1
                       else 0 end,
          int(sum(J1CUBE) / :R1CUBE + 0.9),
          count(distinct ORDN10)
     from JVL110WK2
left join JVP110 on CONO10 = J1CONO and STRM10 = J1STKR and
                    ORDN10 = J1ORDN
    where J1STKR = :S1STRM and
          ((J1TP# = :S1TRAN and :S1TRAN <> :Unassigned) or
           (J1TP# = ' ' and :S1TRAN = :Unassigned))
   group by J1LOAD
   order by J1LOAD

(basically I'm counting distinct order numbers by their assigned load)

Prior to this query running, I create JVP110WK in QTEMP and dynamically
build 3 logicals over it.  Then I populate the workfile.  Here is DDS for
these logicals and in the order that they are built in QTEMP:
JVL110WK1:
A          R JVR110WK                  PFILE(JVP110WK)
A          K J1TP#
A          K J1STKR

JVL110WK2:
A          R JVR110WK                  PFILE(JVP110WK)
A          K J1TP#
A          K J1LOAD

JVL110WK3:
A          R JVR110WK                  PFILE(JVP110WK)
A          K J1TP#
A          K J1ORDN

(I realize that none of these logicals are optimized for this query, but
even after I created another one keyed only by J1LOAD, it still didn't want
to use it)

When I run the program with a specific set of input parms, this query is
returning 0 records when I expect 1 record.  However when I run the same
query in STRSQL with the same input parms,  it returns the 1 record.  While
in debug, I can see that the query is selecting a different access path
depending on whether the query is run from inside the program or from
within STRSQL.  The logicals over this workfile have no select/omit
criteria.  Why would I get different results depending on the access path
it uses?!?!

With my second issue, I have discovered that in order to fix this in the
program, I needed to build the second logical JVL110WK2 AFTER I build the
third logical JVL110WK3 in QTEMP.  Can logicals over the same physical
sometimes be built upon each other?  Or is the query optimizer actually
looking at the last update date/time when determining the access path to
use???

Here are the debug messages from inside the program before I changed the
build order:

**** Starting optimizer debug message for query .
All access paths were considered for file JVL110WK2.
Access path built from keyed file JVL110WK3.
File JVL110WK2 processed in join position 1.
File JVP110 processed in join position 2.
Unable to retrieve query options file.
**** Ending debug message for query .

Details from "all access paths were considered":
QTEMP/JVL110WK3  0, QTEMP/JVL110WK2  5, QTEMP/JVL110WK1  5.

And after I changed the build order:

**** Starting optimizer debug message for query .
All access paths were considered for file JVL110WK2.
Access path built from keyed file JVL110WK2.
File JVL110WK2 processed in join position 1.
File JVP110 processed in join position 2.
Unable to retrieve query options file.
**** Ending debug message for query .

Details from "all access paths were considered":
QTEMP/JVL110WK2  0, QTEMP/JVL110WK3  5, QTEMP/JVL110WK1  5.

Sorry for the long e-mail, but I thought this was especially strange and
needed all the background info to explain.

Thanks,

Ben Pforsich
Programmer Analyst
Bob Evans Farms, Inc.
I/S Department
Columbus, Ohio
Ben_Pforsich@xxxxxxxxxxxx