Baan Level 2 Drivers with Informix 9.40

We are currently running IDS 9.40FC4W1 and Baan 5. We have recently switched from using baan level 1 drivers to level 2 drivers and are experiencing some performance problems, specifically with standard baan code which uses nested left joins. We are running update statistics low, medium, and high for all columns that head indexes. I was wondering if anyone has had experience with using baan level 2 drivers with informix 9.40 and if so, if you have run into similar problems with left joins. We are working with IBM support and have upgraded a test instance to 9.40FC5 and applied a patch to fix two bugs which were thought to be the cause of the problems. This fixed some, but not all of our issues. Thanks in advance for any suggestions. Pam Ekstrand Database Administrator OneNeck IT Services 480-315-3087 Privileged/Confidential Information may be contained in this message or = attachments hereto. Please advise immediately if you or your employer do = not consent to Internet email for messages of this kind. Opinions, = conclusions and other information in this message that do not relate to = the official business of this company shall be understood as neither = given nor endorsed by it.

Low medium and high on index leading columns is redundant. Medium and High produce the same stats except that HIGH produces exact counts while MEDIUM only takes a very small sample and extrapolates. If you are running either or both the MEDIUM and HIGH without the DISTRIBUTIONS ONLY clause then the updates to syscolumns records that LOW performs is being done also by the HIGH/MEDIUM so that may also be redundant. In addition, you are not doing enough. You absolutely must perform a LOW on the EXACT set of key columns for each and every index in a separate LOW command in order to calculate the stats columns stored in sysindexes/sysindices. Finally, it is always useful, especially in a system like BAAN which is likely to filter on unindexed columns, to UPDATE STATISTICS MEDIUM on all columns for which you did not do a HIGH. You can either do this in a single command listing all those columns in a table or you can just perform a MEDIUM at the table level BEFORE running the other HIGH and LOW commands. Please read the Performance Guide guidelines or better yet, get my dostats utility which implements this protocol automatically. Dostats is contained in the package utils2_ak which you can download from the IIUG Software Repository. See what happens if you run the full protocol of commands as recommended. Art S. Kagel ----- Original Message ----- From: Pam Ekstrand <Pam.Ekstrand@OneNeck.com> At: 7/20 20:50 We are currently running IDS 9.40FC4W1 and Baan 5. We have recently switched from using baan level 1 drivers to level 2 drivers and are experiencing some performance problems, specifically with standard baan code which uses nested left joins. We are running update statistics low, medium, and high for all columns that head indexes. I was wondering if anyone has had experience with using baan level 2 drivers with informix 9.40 and if so, if you have run into similar problems with left joins. We are working with IBM support and have upgraded a test instance to 9.40FC5 and applied a patch to fix two bugs which were thought to be the cause of the problems. This fixed some, but not all of our issues. Thanks in advance for any suggestions. Pam Ekstrand Database Administrator OneNeck IT Services 480-315-3087 Privileged/Confidential Information may be contained in this message or = attachments hereto. Please advise immediately if you or your employer do = not consent to Internet email for messages of this kind. Opinions, = conclusions and other information in this message that do not relate to = the official business of this company shall be understood as neither = given nor endorsed by it.