Topic
5 replies Latest Post - ‏2012-04-06T08:56:53Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic Informix ODBC Error: Read error occurred during connection attempt

‏2012-04-04T13:16:06Z |
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)
at System.Data.Odbc.OdbcConnection.Open()

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

Open connection
Fire a select statement
Close connection

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
Updated on 2012-04-06T08:56:53Z at 2012-04-06T08:56:53Z by SystemAdmin
  • BlinddogMS
    BlinddogMS
    10 Posts
    ACCEPTED ANSWER

    Re: Informix ODBC Error: Read error occurred during connection attempt

    ‏2012-04-04T14:54:20Z  in response to SystemAdmin
    Hello. 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).

    Regards.
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Informix ODBC Error: Read error occurred during connection attempt

      ‏2012-04-04T16:03:29Z  in response to BlinddogMS
      Thank 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.

      Regards
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Informix ODBC Error: Read error occurred during connection attempt

    ‏2012-04-04T15:09:43Z  in response to SystemAdmin
    Hello,
    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.
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Informix ODBC Error: Read error occurred during connection attempt

      ‏2012-04-04T16:00:37Z  in response to SystemAdmin
      Thanks 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.
      • SystemAdmin
        SystemAdmin
        1143 Posts
        ACCEPTED ANSWER

        Re: Informix ODBC Error: Read error occurred during connection attempt

        ‏2012-04-06T08:56:53Z  in response to SystemAdmin
        Well, 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).
        T.