Slow Performance querying AS400 via iSeries Client Access / 64-bit ODBC

I'm creating an application in 64-bit Windows that extracts data from AS400 using IBM Client Access 64-bit ODBC.   Issue is when query is running on AS400, it is very slow compared to other executing queries, time waits and pulls records back to my server a few at a time.   Trouble must be with IBM Client Access 64-bit ODBC driver, which I am required to use to access AS400 directly, but need to improve performance significantly.

No help from IBM yet, and hoping to for suggestions on performance settings or alternative solutions.

Please let me know your thoughts.
QlikJimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JBart_17Commented:
If possible make logical files of the files you are accessing on the As400.
 
For example if you are doing a query on say the order header fire and the physical file is keyed by order#, but your selection criteria is by date range. Then you can speed up performance by creating a logical file of the order header file on the as400 keyed by order Date and using the new logical file in you query.  
 
Logical files are similar to Views in MS SQL  
 
 That should help a lot, if youre still having issues check out
 
http://www-03.ibm.com/systems/i/software/db2/sqlperffaq.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JBart_17Commented:

 if you are joining files the keys are important for performance, so logical files can also help here.
if you test your SQL query in iSeries navigator, you can click the VisualExplian  menu and it will explain what is going on when the query executes this data should help you  optimize your query  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.