Toad World® Forums

An attempt to allocate a handle failed because there are no more handles to allocate

Hi,

Somtimes I need to assign a few thousand ID’s ( for copying a chink of records from one environment to another). The way to do this is to incremet the sequence of DB2, assuring that no other client will insert into the ID range that I will get.

When I tried getting a NEXTVAL a few thousand times, I recieved an error.

Today this happened again when I ran a statement to update the sequence to the MAX ID that I created by copying records. According to our DB2 expert, the error was coused by the Toad driver.

Error:04/09/2017 08:27:10 0:00:00.008: Lookup Error - DB2 Database Error: ERROR [HY014] [IBM] CLI0129E An attempt to allocate a handle failed because there are no more handles to allocate. SQLSTATE=HY014
ForwardSequences.sql:1598: SELECT NEXT VALUE FOR SCHEMA_NAME.SEQUENCE_NAME FROM SYSIBM.SYSDUMMY1 WHERE (SELECT MAX(ID) FROM SCHEMA_NAME.TABLE_NAME ) >= (SELECT IFNULL(MAXASSIGNEDVAL,RESTARTWITH) FROM SYSIBM.SYSSEQUENCES WHERE(SCHEMA,NAME) = (‘SCHEMA_NAME’,‘SEQUENCE_NAME’) );

Hi,

Over the weekend I ran a realy long select script. I opened two instances of Toad ( 6.2 licensed version and 6.3 Beta). Both scripts had more that 5000 selects and both failed after 2088. Is there any workaround for this issue?

The select I ran goes through an XML DATA FIELD and uses XMLSerialize to find a few set of strings. If I don’t break up the select to run on a few hundred records I run into a DB2 limitation.

Sincerely,

Shimon

Did anybody ever come across this issue before?