ODBC S1001 / HY001 Memory Allocation Failure

Resolving The Problem

ODBC 2.0 applications using Client Access Express may experience a ODBC S1001 Memory allocation failure on a SQLAllocStmt call. ODBC 3.5 applications using Client Access Express V5R1 or later will see the error reported as HY001 Memory Allocation Failure on a SQLAllocHandle API. Quite often, the database server joblog contains the following related warning message: PWS0083 - Additional 16 megabytes of storage allocated. The possible causes for this message are: Out of Memory and Application Statement Handle Leak.

Out of Memory

The obvious explanation is that the PC is actually out of memory and Client Access was not able to allocate the memory needed for the statement handle. This could be because the workstation is not sized correctly for the application or it could be the result of an application programming bug leading to a memory leak. The database server job will typically not show a large number of open files nor a PWS0083 message.

Application Statement Handle Leak

The more common reason for the Memory Allocation Failure is an application error involving an ODBC statement handle leak. The application continues to allocate new statement handles but never releases the existing handles' resources. Note that only the SQL_DROP option of the SQLFreeStmt API actually frees all memory associated with the handle. SQL_CLOSE and SQL_UNBIND do not. Because each statement handle allocated by the application also results in memory allocated on the server, the database server program may contain one or more PWS0083 messages.

In theory, Client Access ODBC could continue to allocate new statement handles until a PC actually runs out of memory. However, PC (and operating system database server job) performance begins to degrade long before this point so Client Access V5R1 and later will log a warning message in the detail trace log when 5K handles are allocated. Older versions of Client Access chose to actually limit the number of open statement handles to approximately 1024.

This handle leak problem can easily be checked by reviewing the ODBC SQL log or spy. Search for each SQLAllocStmt API, and note the statement handle. Then search on the statement handle and verify that the application performs a SQLFreeStmt on the handle using the SQL_DROP option. Support line users can download the "handle check" tool from ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/. This tool will scan the SQL log and locate the handles that were not dropped.

This problem is often encountered by programmers using an object model built over ODBC. Some common applications include JDBC-ODBC bridge, Small Talk, and Visual Basic ADO. The programmer may forget to destroy the object when they are done with it or rely on the object being destroyed when it goes out of scope (such as with a local variable). In the case of Java and Small Talk, this relies on the run time garbage collection. Vendor implementations of garbage collection vary greatly and in some implementations the connection object holds a reference to the statement object. The out of memory error will typically appear as an intermittent S1001 error since the number of open statement handles varies depending on how busy the PC is. The programmer should explicitly destroy the object allocating the statement handle rather then relying on the run time environment to destroy it.

Reported number of Active Statements

ODBC drivers report the maximum number of statement handles the driver can support on the ODBC SQLGetInfo SQL_ACTIVE_STATEMENTS API. Starting with APAR SA91139 V4R5 and V4R4 of Client Access Access ODBC incorrectly reports that there is no limit. This change was put in to circumvent a bug in a common PC application where the application would create one connection per statement if a driver reported any value other then 0.

Total number of Statement Handles Allocated

Supported versions of Client Access have no limit to the total number of statement handle allocates that are issued.