Problem with Informix linked tables on MS Access
Posted in 2012
Hi. We are migrating our data warehouse from Informx 10.0 on Windows Server 2003 (32bit) to Informix 11.7 running on RedHat Linux 5.5 64bit. In testing the new server I have encounted a problem with a user who connects to the dbserver using MS Access linked tables (via Informix ODBC 3.70 TC1). When the user opens a linked table ( > 1 million rows) in Access, CPU utilisation climbs on the server to 100% and the dbserver becomes unresponsive until either the session is killed or data is returned. The user session consumes significant more memory compared to a similar SQL querys. The problem only happens with MS Access linked tables. I think this is due to the way Access passes many small queries to the dbserver to build it's dynaset. This problem didn't occur with linked tables on our old Informix 10.0 server. On the new server I have modified a number of onconfig parameters to enable PDQ Queries and increased shared memory values, so possible I've got something wrong. Other applications that use ODBC connections don't suffer the same issue and neither does MS Access "pass-through" SQL querys. Any insight on how to troubleshoot this issue would be appreciated?