SQE and V7R1 mystery

[Gerald Kern <jp2558@xxxxxxxxx>]

Tuesday night we had an SQLRPGLE program in the production environment bomb
due to some perplexing circumstances. (This job is a batch job and runs off
the job scheduler.)

Last Sunday we upgraded our production partition to V7R1 and TR4, making
all three of our partitions identical in terms of OS version, LPP's, group
ptf's and cumes.

The following is a quote I found on the net, referring to something I was
aware of before the upgrade:

"Traditional Applications meet SQE"

"Many more applications will be able to leverage the significant
performance boosts from use of the SQL Query Engine with its new support
for select/omit logical files! Because of the heavy use of select/omit
logical files in many IBM i applications, this is great news, as those
applications
can now benefit from the huge performance gains that SQE can bring
without having
to alter the applications!"

Something to do with the above, we think is responsible for the program
problem.

Since 2003 the program has used an LF with select/omit criteria. Running in
debug mode on our test partition showed the correct logical file, and query
engine choosing to use another logical for the job, (which had the same
select/omit criteria), and it produced the desired results.

We repeated the same job on production in debug and found that the query
engine showed the correct logical file in use but chose to use a different
logical - one that had no select/omit criteria, which provided a result set
with duplicate records!

We checked the QAQQINI (sp?) file and it's identical (everything using the
defaults) and we compared the file descriptions of the physical and logical
file on both systems and they were identical as well (ie db maintenance =
*immed, shared access path = *no, file level id etc).

So as a test we changed the SQLRPGLE to not use the LF but instead to use
the PF and added a where clause to replicate the select criteria in the LF
DDS, and an order by to replicate it's key sequence.
We ran that version of the program on both production and test and got
identical (and correct) results! In debug the job log showed the PF and the
query optimizer choosing on both systems to use the correct logical file
(the one that was selected on our test partition before we changed the
program).

So we concluded that all things were equal but that something triggered the
optimizer to behave differently and we haven't a clue what it is.

I spoke to my business partner and they weren't aware of anyone reporting
this behavior so I thought I share this here hoping someone might have had
a similar experience or has any comments to offer.