RubyForge: Rails Adapter/Driver for IBM DataServers: open-discussion

Discussion Forums: open-discussion

RE: Ruby ibm_db Adapter and Excessive SQL cal [ reply ]
2009-04-07 13:50

Venkata,
I am getting the same types of excessive SQL activity with DB2 for the i series. You dote that you opened a problem wit software support. Cah you share the resolution.
Thanks.
George

By: George Keenan

RE: Ruby ibm_db Adapter and Excessive SQL cal [ reply ]
2009-04-06 22:42

I am getting the same type of statements, altho not as many, useing DB2 for the i series. What was the solution Venkata got when he submitted the problem to software support? Thanks.

By: Praveen Devarao

RE: Ruby ibm_db Adapter and Excessive SQL cal [ reply ]
2009-02-09 02:01

Hi Venkat,

Thanks for the update. Feel free to write to us on any suggestions/comments or any issues that you are facing.

Thanks

Praveen

By: venkata yerrapothu

RE: Ruby ibm_db Adapter and Excessive SQL cal [ reply ]
2009-02-07 21:22

Praveen,

We were able to identify the root cause of these calls showing up on the server side.

DB2 9.5 CE is using a package NULLID.SYSSTATS while retriving CLOB columns. This package internally generating these calls.

VALUES ( LENGTH ( :H00003 ) ) INTO :H00016 :H00017

VALUES ( SUBSTR ( :H00003 , :H00014 , :H00015 ) ) INTO :H00009 :H00017

Looks like this is not a problem related to Ruby Adapter.

We did open a PMR with IBM( PMR 00355,122,000 ) as well.

Please mark this issue as resolved.

Thanks
Venkat

By: Praveen Devarao

RE: Ruby ibm_db Adapter and Excessive SQL cal [ reply ]
2009-01-27 14:36

Hi Venkata,

I need few clarifications.

In your mail you say there are many queries while fetching metadata. The queries that you have pasted (In the second mail) don't seem to be querying for metadata.

Could you be clear on which query is exactly being hit a many number of times. This will help us figure out if it is a query generated by ibm_db adapter or due to your app logic.

Thanks

Praveen

By: Praveen Devarao

Ruby ibm_db Adapter and Excessive SQL calls [ reply ]
2009-01-27 14:33

Hi,

Below I am pasting the mail extract so that others going through the forum will get the context of the conversation

__________________________________________________
Our database team noticed that some of the SQL statements are getting executed repeatedly causing CPU spikes on DB server.

We did came to a conclusion (assumption) that IBM Ruby adapter is making those calls to retrieve the meta data information from the database.

If our assumption is correct , Is it possible for the IBM Ruby adapter to cache this information and reuse it instead of going against the database.

Any optimization in this regard will improve our database performance .

This is what our DBA team is reporting.

The following two queries are executing thousands of times. They are coming from the application ID so something is driving these statements. You will need to track them down and determine if any of this information being queried can be cached. Both queries are using AP907011.

VALUES ( LENGTH ( :H00003 ) ) This query executed 226304 times in a 15 minute time period.
INTO :H00016 :H00017

VALUES ( SUBSTR ( :H00003 , :H00014 , :H00015 ) ) This query executed 113151 times in the same 15 minute window.
INTO :H00009 :H00017
___________________________________________________