No more handles to allocate error in Nodejs web application

[fabyan-ibm]

Our team manages an application that communicates with a Db2oc database. Sometimes, we receive the error "Error: [IBM][CLI Driver] CLI0129E An attempt to allocate a handle failed because there are no more handles to allocate. SQLSTATE=HY014" and the application becomes unresponsive for a number of hours afterwards.

We believe that the problem is coming from some logic in our application that iterates through hundreds of records, and makes multiple DB select calls for each record as part of a single transaction (ie: 1 transaction processes hundreds of records, each with multiple select calls). In our application, the way we handle transactions are:

• we open a connection using a connection pool
• we synchronously begin a transaction
• we make the SQL calls, WITHOUT closing the connection each time a call completes (as we're in a transaction)
• we commit the transaction, and then close the connection

Outside of our transactions, the rest of our database calls opens a connection from a connection pools, executes, and then closes the connection. We don't believe these are causing any problems.

We wanted to know the best way to approach this issue. Specifically, if there is a better design to handle these large transactions on the codebase level based on the details provided above. Otherwise, we wanted to know if increasing the number of statement handles on the database level (https://www.ibm.com/support/pages/how-increase-number-statment-handles-available-your-application) would be helpful. We aren't sure what the current value of our database is, because it's not listed in the Administration page. What would be the best practices with handling such transactions with many queries before closing connection? as we currently do the following when executing a transaction:

• begin transaction -> prepare statement(s) -> execute statement(s) -> fetch the results -> the previous 3 steps are repeated for every query called without closing any objects in a closure
• commit and close transaction (which presumably closes all created objects & handles)

would we need to instead free the objects after every new query?

We also want to find out if it's possible to free these statement handles after we encounter the "no more handles" error. As previously mentioned, after we hit the error, the application becomes unusable for a few hours, returning the same "no more handles" error for any other database queries or transactions. It would be great if we know exactly which type of objects to close within a transaction.

Thank you

[bimalkjha]

@fabyan-ibm We need to close the results and statement both after transaction is ended and we are done with a statement handle. Call result.closeSync() once done with result and call stmt.closeSync() once done with statement after end of the transaction. If connection is no more in use, return the connection to pool using conn.closeSync(). If after modifying the application, still see the error, we need to collect db2trace and clitrace to see what is going on with application. A small repro test would also help. Thanks.

[fabyan-ibm]

@bimalkjha we will try that, thank you.

[shawn-gang]

hi @bimalkjha, thanks for the feedback. Unfortunately we weren't able to find the method closeSync on the ODBCStatement object.

Property 'closeSync' does not exist on type 'ODBCStatement'.

We checked out the type definition as well to confirm that the method does not exist. The file I'm looking at is node_modules/@types/ibm_db/index.d.ts

[bimalkjha]

@shawn-gang Install latest version of ibm_db@2.8.2. stmt.closeSync() is there in latest release. Also, you can find the documentation about it here: https://github.com/ibmdb/node-ibm_db/blob/master/APIDocumentation.md#-15-executesyncbindingparameters Also, you can see the use of result.closeSync() and stmt.closeSync() in this test file: https://github.com/ibmdb/node-ibm_db/blob/master/test/test-sp-resultset-execute.js#L104 Thanks.

[bimalkjha]

@shawn-gang @fabyan-ibm Please let us know any update about this issue. Thanks.

[fabyan-ibm]

@bimalkjha hi, I think our issue is solved by closing statement handles, but updating the ibm_db library and its types did not update the definitions to include the close method. It exists on runtime but the definitions do not seem up to date