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

Posted on 2008-11-04
Last Modified: 2013-11-29
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.
Question by:QlikJim
    LVL 5

    Accepted Solution

    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

    LVL 5

    Expert Comment


     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  

    Featured Post

    Free book by J.Peter Bruzzese, Microsoft MVP

    Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s free!

    Join & Write a Comment

    Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
    If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
    The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now