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.
Who is Participating?
JBart_17Connect With a Mentor Commented:
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

 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  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.