timeout in Informix client (Widows)
We've got an issue when trying to upgrade our Informix ODBC from CSDK 2.70 to a newer one. When query (or stored procedure) doesn't return any row within 30 seconds then we are receiving error: (-11094) *Timeout* expired and (-213) Statement interrupted by user. The only solution is to modify source code. In our case it is not possible. We are getting this error in 2 applications: 1. VB accounting application that makes end of day processing by running configured SPLs. We don't have source code for it and we suspect that modifying it would be costly because application is quite complex. 2. Our ETL is Ab Initio. I don't think Ab Initio will provide us with specially tailored version of Cooperating Systems just because we have issue upgrading Informix driver. Also I haven't found any standard settings in AbInitio to set default timeout for ODBC command. On the other hand we need newer drivers for our .NET application which will be on the same workstation as our accounting application. We've already check that when we install both CSDK 2.70 and 3.00 then timeout occurs also for ODBC connections which were earlier on 2.70. I've googled for "ODBC timeout" not related to Informix. I've found that in all cases a solution (I think that it is quite obvious) is to set ODBC parameter in ODBC manager. It appears that other databases do not support timeout feature or support it together with possibility to set default value for it (MS SQL). It seems that Informix consciously introduced this timeout feature (In release notes to *ODBC* 3.81 you can see: "Support SQL QUERY TIME OUT") but forgot that it breaks compatibility with older applications. Does anybody found a workaround for this 30s ODBC command timeout? The last thing is: is it a bug or feature request? I've found that this issue is quite old one (2002) so probably Informix support rejected it to treat it as a bug. Robert Sosnowski Links to similar, earlier cases: http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/b4423 e2fe206ef62/0f5e2d453c17124d?hl=pl&lnk=st&q=odbc+timeout+informix+group%3Acomp.d atabases.informix#0f5e2d453c17124d http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/907b7 308811b1c46/ad9240c88feb47be?hl=pl&lnk=st&q=timeout++30+group%3Acomp.databases.i nformix#ad9240c88feb47be http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/21963 187f55bbd3f/753228ee02f0523d?hl=pl&lnk=st&q=timeout++30+group%3Acomp.databases.i nformix#753228ee02f0523d http://www.webservertalk.com/archive221-2004-9-402489.html
Yes, IBM Informix ODBC Driver implements Query Time Out functionality( from CSDK 2.80/ODBC 3.81 onwards) but I didn't find default value being 30 secs anywhere (not sure if I missed it). In my opinion there is no default value (that means infinite) and unless this is explicitly set by an application the behavior of an application should be same as the ODBC driver without this functinality/feature. Having said that, this must be explicitly set by your application (may be in VB Bridge piece) to 30 secs. Could you take the ODBC trace and check(for SQL_ATTR_QUERY_TIMEOUT) the same to ensure its indeed being called by your application and there is no default of 30 secs in IBM Informix ODBC Driver? Thanks -Shesh "ROBERT SOSNOWSKI" <robsosno@gmail.com> Sent by: ids-bounces@iiug.org 06/01/2008 16:14 Please respond to ids@iiug.org To ids@iiug.org cc Subject timeout in Informix client (Widows) [10859] We've got an issue when trying to upgrade our Informix ODBC from CSDK 2.70 to a newer one. When query (or stored procedure) doesn't return any row within 30 seconds then we are receiving error: (-11094) *Timeout* expired and (-213) Statement interrupted by user. The only solution is to modify source code. In our case it is not possible. We are getting this error in 2 applications: 1. VB accounting application that makes end of day processing by running configured SPLs. We don't have source code for it and we suspect that modifying it would be costly because application is quite complex. 2. Our ETL is Ab Initio. I don't think Ab Initio will provide us with specially tailored version of Cooperating Systems just because we have issue upgrading Informix driver. Also I haven't found any standard settings in AbInitio to set default timeout for ODBC command. On the other hand we need newer drivers for our .NET application which will be on the same workstation as our accounting application. We've already check that when we install both CSDK 2.70 and 3.00 then timeout occurs also for ODBC connections which were earlier on 2.70. I've googled for "ODBC timeout" not related to Informix. I've found that in all cases a solution (I think that it is quite obvious) is to set ODBC parameter in ODBC manager. It appears that other databases do not support timeout feature or support it together with possibility to set default value for it (MS SQL). It seems that Informix consciously introduced this timeout feature (In release notes to *ODBC* 3.81 you can see: "Support SQL QUERY TIME OUT") but forgot that it breaks compatibility with older applications. Does anybody found a workaround for this 30s ODBC command timeout? The last thing is: is it a bug or feature request? I've found that this issue is quite old one (2002) so probably Informix support rejected it to treat it as a bug. Robert Sosnowski Links to similar, earlier cases: http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/b4423 e2fe206ef62/0f5e2d453c17124d?hl=pl&lnk=st&q=odbc+timeout+informix+group%3Acomp.d atabases.informix#0f5e2d453c17124d http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/907b7 308811b1c46/ad9240c88feb47be?hl=pl&lnk=st&q=timeout++30+group%3Acomp.databases.i nformix#ad9240c88feb47be http://groups.google.pl/group/comp.databases.informix/browse_thread/thread/21963 187f55bbd3f/753228ee02f0523d?hl=pl&lnk=st&q=timeout++30+group%3Acomp.databases.i nformix#753228ee02f0523d http://www.webservertalk.com/archive221-2004-9-402489.html ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!
Hi Shesh, Your suggestion to check ODBC trace was very good. It appeared that 30s timeout is set by VB application. Thank you very, very much. I've come to the (wrong) conclusion that issue is in Informix driver because: - my C# programs have 30s timeout by default when working with Informix database (ODBC); - application made by our software provider (VB 6.0 with ODBC) looks like having 30s command timeout; - there are older similar cases from other people. These 3 different things have in common 30s ODBC timeout so it was natural to think that there is one reason for all this (actually there are different reasons). I haven't considered possibility that VB application have a bug, especially that setting timeout to 30s in such a kind of application (long running reports) is just stupid. To be 100% sure I've written small C program to execute long running SPL via ODBC, CSDK version 3.00. There was no timeout. Once again thank you for your help, Robert Sosnowski