Firstly, I would like to stress that I am new new newbie to informix
We are using SQL Server SSIS package to connect to IDS 10.0 to import some data. We use IBM Informix ODBC driver (3.5) to connect to the informix database. IDS is running on Windows 2003 R2 Enterprise Edition. The package is scheduled to run every few minutes to pull in new data. After certain number of cycle the connection attempt from the package starts failing with the following error:
System.Data.Odbc.OdbcException: ERROR HY000 InformixInformix ODBC DriverInformixRead error occurred during connection attempt.
ERROR HY000 InformixInformix ODBC DriverInformixRead error occurred during connection attempt.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
After a bit of investigation on the Informix side I brought it down to the shared memory allocation within the Informix database. I check the onconfig setting for the server and it has the following settings
SHMBASE 0xC000000L # Shared memory base address
SHMVIRTSIZE 51200 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
EXTSHMADD 8192 # Size of new extension shared memory segments (Kbytes)
SHMTOTAL 179200 # Total shared memory (Kbytes). 0=>unlimited
We start getting our first error when the total memory exceeds the value specified for SHMTOTAL. The Online.log shows the following message
10:09:43 Dynamically allocated new virtual shared memory segment (size 8192KB)
10:09:43 Memory sizes:resident:42368 KB, virtual:133120 KB, SHMTOTAL:179200 KB
10:10:55 Size of resident + virtual segments 42368KB + 141312KB > 179200KB
10:10:56 total allowed by configuration parameter SHMTOTAL
10:10:56 create_tcb: cannot allocate memory
from then on we start getting the following error
16:45:36 nsocpoll.c, Line 1216, Thread 7 errno 2, Not enough memory, error in MEM_ALLOC_GLOBAL.
16:45:36 Assert Warning: Unable to allocate a transaction for user id 7, session id 0
16:45:41 (1211) connection rejected - too many users, or invalid user name
16:45:41 listener-thread: err = -25571: oserr = 0: errstr = : Cannot create a user thread.
After this point all connections from everywhere to the informix server fail. Even dbaccess is not able to connect.
I have checked that the SSIS packages open and close the connections properly. I have even create a test command application that repeats the following regularly untill the connection breaks
Fire a select statement
Even if I restart client application (even reboot client machine) we are not able to connect.
onstat shows that the server is online.
I have used onmode -F to free unused memory. Even that did not help.
The only way is to restart the informix server.
It would of great help if someone can let me know what is the best way to get the connection working without killing the IDS. The IDS we use is third party system. so, oncofig changes may not be an option.
More than anything I would like to know why the connection wont work even if I have freed up memory. Is there something more to it?
Thanks in advance
This topic has been locked.
5 replies Latest Post - 2012-04-06T08:56:53Z by SystemAdmin
Pinned topic Informix ODBC Error: Read error occurred during connection attempt
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-04-06T08:56:53Z at 2012-04-06T08:56:53Z by SystemAdmin
BlinddogMS 110000BDJS10 PostsACCEPTED ANSWER
Re: Informix ODBC Error: Read error occurred during connection attempt2012-04-04T14:54:20Z in response to SystemAdminHello. The problem seems to be related to your Informix engine, really.
Ask for your 3rd party support.
Just a doubt: did this SSIS package ran fine, on your system? Or it´s a new one....
Another thing: check your CSDK complete version, and if it´s compatible to your Informix version. Eg: CSDK 3.50 is not compatible do Informix 11.70 version. It will only work fine on versions 11.50 (and previous).
Re: Informix ODBC Error: Read error occurred during connection attempt2012-04-04T16:03:29Z in response to BlinddogMSThank you for your response.
SSIS package runs fine during development importing data from the same source. Production it works and brings in data until the point where we encounter the error.
Source is IDS 10.0, so CSDK 3.5 is good.
Re: Informix ODBC Error: Read error occurred during connection attempt2012-04-04T15:09:43Z in response to SystemAdminHello,
apparently the problem is that your Informix instance needs to allocate more memory for (any) new connection, but as it is reaching the SHMTOTAL limit set in $ONCONFIG file, it can't do so. You need to get in touch with the IDS admin and ask him 1) to increase the SHMTOTAL limit and 2) to investigate the memory usage. The real memory requirements of the instance depends on many factors (number of concurrent sessions, amount of data being processed, the type of the workload etc.), so it might be that the limit of 179 MB is just not enough.
The fact the 'onmode -F' makes no change only means that the allocated memory is really needed and can't be freed.
Kind regards, T.
Re: Informix ODBC Error: Read error occurred during connection attempt2012-04-04T16:00:37Z in response to SystemAdminThanks you very much for your response.
I understand that changing the settings in $ONCONFIG file will help.
There is one thing I don't quite understand. I have closed my applications down since the error. There are no more connections to the IDS. Why is that IDS does not accept any new connections? It seemed like IDS was holding on to the memory even if there was nothing connected to the server.
BTW running onmode -F for the first time produced the following in the log file
11:14:31 Attempting to free unused operating system segments. This operation may take several minutes.
11:14:31 Freed 5 shared memory segment(s) (41943040 bytes)
Seemed like it did free up memory. But that still did not help with new connections to be made. Kept getting the same error.
Re: Informix ODBC Error: Read error occurred during connection attempt2012-04-06T08:56:53Z in response to SystemAdminWell, i'm afraid this can be explained only by further analysis of the memory in use. I'd suggest to log a PMR with IBM Informix Support (if you have a valid Passport Advantage contract for Informix, you can do it yourself by calling your local IBM support center (for contact numbers see http://www.ibm.com/planetwide/region.html); if not, you have to ask the 3rd party who delivered the Informix to you).