Forums | Admin

Discussion Forums: open-discussion

Start New Thread Start New Thread

 

By: Lucas Maxwell
RE: When to free statements from execute [ reply ]  
2012-11-16 01:04
Yep that's what's happening, if execute is called directly on the connection we start to leak handles. We do this a bit to run arbitrary sql without talking to ActiveRecord explicitly about inserts/updates/etc.

I had a look through your link, thanks for that. Increasing the statement handle count will probably work, but it does seem like it's deferring the problem.

I might try just overriding execute at the adapter level with a shim and free the statement explicitly for our use cases.

By: Praveen Devarao
RE: When to free statements from execute [ reply ]  
2012-11-15 07:54
You can refer to this link for more details on stament handles, the number of them available by default, how to increase them etc

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/c0004933.html

By: Praveen Devarao
RE: When to free statements from execute [ reply ]  
2012-11-15 07:52
Hi Lucas,

I think, I unnderstood your concern. Let me know if I get it right.

Your referring to situations where the SQL issued is something like insert (and directly called via IBM_DBAdapter#execute), right? If that is the case then you will need to note one thing, In activerecord we donot call execute directly. It is invoked via the insert or update or select methods which take care of freeing the statement handles appropriately

Other thing that ibm_db driver provides is, it wraps the statement structure into ruby object giving it a free method (invoked during garbage collection), that frees the statement handle. But yeah, you will need to wait for the garbage collector to kick in and there should be no reference to the statament object.

Let me know if you I understood your problem rightly or need more clarification

Thanks

Praveen

By: Lucas Maxwell
RE: When to free statements from execute [ reply ]  
2012-11-15 04:57
The error returned is:

ActiveRecord::StatementInvalid: RuntimeError: Failed to execute statement due to: [IBM][CLI Driver] CLI0129E An attempt to allocate a handle failed because there are no more handles to allocate. SQLSTATE=HY014 SQLCODE=-99999

So the error actually related to the client, then is it a function of the system's max file handles?

I think I've done a bad job at explaining myself.

When calling ActiveRecord::ConnectionAdapters::IBM_DBAdapter#execute the statement is returned, and that must be freed explicitly. If I free it after execute is run then other methods that use execute such as fetch_data fail because the statement has been freed before the data was fetched.

In essence .execute isn't useable without explicitly calling IBM_DB.free_stmt as over time it will consume all the available handles. I'm trying to find a place that the statement can be released such that the methods that use execute don't have the statement closed before they can use it but execute doesn't pass an unfreed reference back to the user code.

ActiveRecord doesn't appear to have an explicit execute/free structure as the other drivers appear to return results that don't need explicit free'ing.

By: Praveen Devarao
RE: When to free statements from execute [ reply ]  
2012-11-15 04:27
Hi Lucas,

Check the fetch_data method of IBM_DB Adapter. You will need to free the statement after all the data is been retrieved.

I am not sure what you meant when you say..."but doing it at the end of those methods doesn't always guarantee release" The statement does get released when called at the end of these methods.

>> Is it possible to release the statement from the server and still retain the data retrieved in a different way? <<

Statement handle is created on the client and not on the server. Cursor is the counterpart that is created on the server [in cases where there is a resultset associated]. When you retrieve the last row from the result set the cursor is automatically closed on the server [unless you have a scrollable or updateable cursor]. But however if you are still amidst the resultset and dont want to retrieve further, then a explicit call to free_stmt will release it.

Thanks

Praveen

By: Lucas Maxwell
When to free statements from execute [ reply ]  
2012-11-15 03:07
When using connection.execute the returned value is an instance of IBM_DB::Statement. This object appears to consume a handle with the DB2 server until it is freed. During normal flow doing the following too often can cause the server to run out of handles.

ActiveRecord::Base.connection.execute("SELECT * FROM SYSIBM.SYSDUMMY1")

That is unless

IBM_DB.free_stmt(result_of_execute_call)

is explicitly called.

I've done some digging around and execute is used by a lot of the adapter that expects it to return a statement that it then frees explicitly itself so freeing the statement and the handle. There doesn't appear to be a clear place when to free the statement, doing at the end of execute won't work as then fetch_data or result will fail, but doing it at the end of those methods doesn't always guarantee release.

Is it possible to release the statement from the server and still retain the data retrieved in a different way?