6 Replies Latest reply: Jul 27, 2011 12:20 PM by Richard Cripps RSS

    Very Slow Extract Speed through AS400 ODBC

    Richard Cripps


      Am trying to extract from an AS400 system (Movex) using the IBM Iseries ODBC driver. The connection works but is very slow.

      Has any one had any performance issues using the Iseries ODBC driver?

      Does anyone have any suggestions of things to look into to try and resolve this?

      I thought at first it could have been a version issue as when I first tried to extract from the system was using an old version, however, I get the same issue on version 11 of the driver from iseries v5SR4.


        • Very Slow Extract Speed through AS400 ODBC

          How "slow" is slow to you?

          Which version of QV are you using?


          • Very Slow Extract Speed through AS400 ODBC

            I run my queries using odbc to iSeries v6R1 and have not had problems as long as the queries were not too complex. If I use multiple inner joins against millions of records I find the performance lacking. I try and design the reports using as simple of query as possible to improve the performance.


            How much memory do you have on your server? What version of qlikview are you running?

              • Very Slow Extract Speed through AS400 ODBC
                Rakesh Mehta

                I read from iSeries and it's actually pretty fast. Try to pull one plain table without any where conditions and joins to make sure whether problem is with ODBC or the selection in AS/400 due to index problems. Also check in the ODBC connection setup if you have a flag to set the connection to read-only. If you do, it may make the extract faster. I am using QV9 SR6 with 32 GB RAM on the server, but RAM will not be an issue with ODBC and selection. Pay attention to CPU usage as that could be a problem while extracts. I remember I had to play with package size or queue size or something to tweak it at one client. Let me know if above mentioned don't help you and I will try to recall or look at customer setup.


              • Very Slow Extract Speed through AS400 ODBC
                Richard Cripps

                Thanks for replies, definitely some things for me to try.

                One thing that occurred to me that I hadn't really thought of before is that network performance could be a contributing factor. I am not 100% on the customers network architecture, but the server that I was extracting data from isn't in the same location as the PC I was extracting to.

                The actual CPU usage was a bit strange though it isn't constant, the ODBC process flickers between 10% usage and nothing about every second.

                As the work was for a SIB the customer had not yet purchased Qlikview so was using personal edition on a laptop.


                • Very Slow Extract Speed through AS400 ODBC
                  Richard Cripps

                  This was indeed a network issue, when implemented the server and the system being extracted from were in the same location and we experienced none of the problems which I'd experienced previously.