DB2 API

srikanthd1978 · Post by **srikanthd1978** » Wed Nov 10, 2004 4:57 pm

guys,

has anyone come across the following error.. I am using a User defined query in my DB2 API to pull data and throw it to a Dataset..

Fatal: SQLAllocHandle: Failed to allocate environment handle 'ENV'.

all inputs are welcome..

thanks

ray.wurlod · Post by **ray.wurlod** » Wed Nov 10, 2004 8:16 pm

SQLAllocHandle is a call to a function in the ODBC API, one of SQLAllocEnv, SQLAllocConnect or SQLAllocStmt. The DB2 API largely follows the conventions and protocols of the ODBC API.

Without any other information it's really difficult to diagnose further. However, you may find the following information useful.

SQLAllocEnv simply grabs some memory in which to manage the connection environment(s), primarily error handling. It should only ever fail if the system can not deliver any virtual memory, which ought to be fairly rare.

SQLAllocConnect grabs some more memory, in which to manage an individual connection environment, including structures for interaction with the database and discovery of information about the driver and the database.

SQLAllocStmt requests memory in which the preparation and execution of SQL statements and retrieval of result sets is managed, including binding of variables to query parameters and result set columns.

If you have any DB2 trace tools, use them to try to figure out at which point the process is failing.

You might also like to contact your DB2 support provider; they may have some extra diagnostic tools available.

Eric · Post by **Eric** » Fri Nov 12, 2004 7:43 am

Are you trying to use the special environment variable $ENV ?

srikanthd1978 · Post by **srikanthd1978** » Tue Dec 21, 2004 3:23 pm

hi eric,

I am not trying to use any special env varaibles.. the API was previously working fine..but now it does not seem to work.. as ray had said do u think its a problem with the Virtual Memory allocation..

..all inputs are welcome..

thanks

mhester · Post by **mhester** » Tue Dec 21, 2004 4:01 pm

Within the context of ODBC connections the SQLAllocHandle is used to allocate handles for environments, connections, statements, and descriptors. Giving that this did work previously then the most likely culprit would be the inability to allocate a handle for a specific environment because some sort of limit has been reached.

In ODBC, no limit is defined on the number of environment, connection, statement, or descriptor handles that can be allocated at any one time. Drivers may impose a limit on the number of a certain type of handle that can be allocated at a time

It might be worth checking to see if you are running up against some sort of limit, otherwise I'm not sure why this would happen.

I found the following on a DB2 information tech site -

The limit for the number of handles that can be allocated for the type of handle indicated by the HandleType argument has been reached, or in some cases, insufficient system resources exist to properly initialize the new handle

This is likely what is happening.

manishk · Post by **manishk** » Fri Mar 31, 2006 6:28 pm

Hi

I am running the PX jobs . I am currently using DB2 API.

Here is the error which i am getting.

FIRST ERROR :" stgDB2ReadScotiaGlobe,0: Failure during execution of operator logic."

SUBSEQUENT ERROR : "stgDB2ReadScotiaGlobe,0: Fatal Error: Fatal: SQLAllocHandle: Failed to allocate environment handle 'ENV'. "

Please let me know what all parameters in the environment are required to over come this.

Please suggest in this regards.

Thanks
Manish

ray.wurlod · Post by **ray.wurlod** » Fri Mar 31, 2006 11:02 pm

Most likely more physical memory, or more virtual memory, or higher ulimit setting for the executing user.

bcarlson · Post by **bcarlson** » Wed Apr 05, 2006 4:46 pm

Is there a reason why you are not using the DB2 Enterprise stage for reading/writing data? It is more efficient and better supported than the API stage.

Brad.