Have you had issues with ODBC selecting indexes?

This is a really weird ODBC behaviour that has been hitting my head for days: I
have a MSAccess program which use ODBC to read data from iSeries (v5r2) and
produce a report. In most of the computers it take only few seconds to produce
the report, but in a few of them it takes forever. When I monitor the process I
see the reason of this issue: the good ones use logical files, but the bad ones
read the physical files directly (25 million records!). <br />
Well, some additional details:<br />
<ul class="jive-dash">
<li>The problem showed up without apparently reason on computers using
iSeries Access V5R3 with ODBC V10.00. We have been using that client version for
a long while without issues.</li>
<li>No PTFs have been installed in more than a year in the
iSeries.</li>
<li>I took two computers for my tests. I downgraded iSeries Access in both
of them to V5R2 with ODBC V9.00. At the beginning none of them worked well until
I decided to delete the SQL Packages MSACCESVBA and QZDAPKG from the system and
try again. The issue was fixed in one of them, but not the other.</li>
<li>I have tried deleting the SQL Package and running the "bad"
computer first. It creates the package but still runs inefficiently.</li>
<li>Starting from scrath: same query, same parameters, same ODBC version,
both WinXP, both computers produce different instructions in the SLQ Package. I
noticed also the "STATEMENT NAME:  Q...." is different in both of
them. Who does define it? Is there any table in iSeries (may be a catalog) where
the instruction set is assigned? or may be there is something in the PC (a DLL
from the previous version) sending a wrong id?</li>
</ul>
<br />
Any idea is welcome.<br />
<br />

   IIRC the identical statement with different names from the same 
application is a side effect of different connection information.  Is it 
possible that one connection is setup to have a sort sequence that is 
representative of a language whereas the other is setup with the default 
to have a /hex/ sort sequence?  See the Language tab under ODBC Setup. 
Check there, on other tabs, for other DSN differences as well.

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

canazco@schukra.com wrote:
> This is a really weird ODBC behaviour that has been hitting my head
> for days: I have a MSAccess program which use ODBC to read data from
> iSeries (v5r2) and produce a report. In most of the computers it take
> only few seconds to produce the report, but in a few of them it takes
> forever. When I monitor the process I see the reason of this issue:
> the good ones use logical files, but the bad ones read the physical
> files directly (25 million records!).
> 
> Well, some additional details:
> 
> The problem showed up without apparently reason on computers using
> iSeries Access V5R3 with ODBC V10.00. We have been using that client
> version for a long while without issues.
> 
> No PTFs have been installed in more than a year in the iSeries.
> 
> I took two computers for my tests. I downgraded iSeries Access in
> both of them to V5R2 with ODBC V9.00. At the beginning none of them
> worked well until I decided to delete the SQL Packages MSACCESVBA and
> QZDAPKG from the system and try again. The issue was fixed in one of
> them, but not the other.
> 
> I have tried deleting the SQL Package and running the "bad"
computer
> first. It creates the package but still runs inefficiently.
> 
> Starting from scratch: same query, same parameters, same ODBC version,
> both WinXP, both computers produce different instructions in the SQL
> Package. I noticed also the "STATEMENT NAME:  Q...." is different
in
> both of them. Who does define it? Is there any table in iSeries (may
> be a catalog) where the instruction set is assigned? or may be there
> is something in the PC (a DLL from the previous version) sending a