 |
Forums |
Admin Discussion Forums: help Start New Thread
By: Praveen Devarao
 RE: ibm_db long time to query response [ reply ]
2009-01-15 04:49
|
Hi Thaza,
Nice to hear, you have a work around.
If it is the authentication issue that is causing a longer time for response, then you can follow the same steps as before instead of the workaround by just specifying another parameter in the yaml file. The parameter is authentication. Now after this your yaml file will have contents something as below.
db2_connection_ws:
adapter: ibm_db
database: UAT1
username: user1
password: pass1
schema: schema1
host: host1
port: 50000
authentication: server #This is the extra parameter
Let me know how it goes.
Thanks
Praveen
|
By: thaza th
RE: ibm_db long time to query response [ reply ]
2009-01-14 17:06
|
Hi Praveen,
Thank you for the quick response.
I was able resolve the issue by implementing in a different way. Instead of establish_connection and running a query on the model, I have followed different process as below. This has significantly improved performance.
require 'rubygems'
require 'mswin32/ibm_db'
conn = IBM_DB.connect("DRIVER={IBM DB2 ODBC DRIVER};DATABASE=DB1;\
HOSTNAME=ihost2;PORT=50000;PROTOCOL=TCPIP;\
UID=nusere;PWD=pass1;schema=sch12;authentication=server", "", "")
Inside a method in the controller I used the above code. From one of the searches I found that the query can take longer time If there is authentication issue. My guess is "authentication=server" made the difference.
|
By: Praveen Devarao
RE: ibm_db long time to query response [ reply ]
2009-01-14 15:54
|
Hi Thaza,
Thanks for the info.
In this case, establishing the connection is taking long time and not the execution of the SQL. Could you check how long is it taking to establish the connection in the other ways (from NetBeans) that you have tried.
What happens is, Activerecord tries to establish a connection only when it encounters the first sql. Hence in your case, I suspect, the connection establishment is taking a longer time and once the connection is established the execution of sqls is taking place faster (as expected). You can test this by taking a CLI trace (it will give you the details of elapsed time for each call). Check for the time taken for the execution of call SQLDriverConnect()[CLI call to establish connection] and SQLExecDirect() [executes the SQL statement].
Procedure to obtain a CLI trace: -
* Make an environment variable named DB2CLIINIPATH and set it to some directory where your local db2cli.ini will reside.
eg. in bash, issue
export DB2CLIINIPATH=<dir name where the traces will be generated>
On Windows, issue
set DB2CLIINIPATH=<dir name where the traces will be generated>
* issue the following commands ( in windows run these commands in the DB2 command prompt, issue db2cmd for the same)
db2 update cli cfg for section COMMON using trace 1
db2 update cli cfg for section COMMON using tracepathname <dir name where the traces will be generated>
* Local db2cli.ini file should be generated in mentioned directory.
* Run the application. Traces will be generated inside the specified directory.
* To disable the CLI trace issue the following command
db2 update cli cfg for section COMMON using trace 0
Note: - Remember to turn off the trace once done.
Let me know if more info is required.
P.S:- Please paste the CLI trace that you get.
Thanks
Praveen
|
By: thaza th
RE: ibm_db long time to query response [ reply ]
2009-01-13 19:10
|
Thanks for the quick response.
here are the details :
Client used : IBM DB2 client and also running queries by connecting from NetBeans (both run pretty fast)
class Customer < ActiveRecord::Base
set_table_name "table1"
set_primary_key "key1"
end
require 'mswin32/ibm_db'
Customer.establish_connection(:db2_connection_ws)
yaml file:
db2_connection_ws:
adapter: ibm_db
database: UAT1
username: user1
password: pass1
schema: schema1
host: host1
port: 50000
controller :
Query to extract information
v = Customer.find(:first, :conditions => ["CUST_CD = '#{cc}'" ])
This execution of query takes about 15secs for the first time. but If subsequent queries are sent immediately, they are processed promptly.
|
By: Praveen Devarao
RE: ibm_db long time to query response [ reply ]
2009-01-13 16:37
|
Hi Thaza,
Could you provide the following details.
>> If I execute a query from the DB2 client the response is immediate <<
Which client are you referring to, whether the CLI or the Java (jcc) client.
Also could you provide some details about your DB2 environment like the db2level, the client server configuration etc.
Thanks
Praveen
|
By: thaza th
ibm_db long time to query response [ reply ]
2009-01-13 15:56
|
Hi,
I am trying to connect to a DB2 database over TCP/IP connection. If I execute a query from the DB2 client the response is immediate. But If I run the query through Rails, the response time to run the "SELECT" on a table for one record takes close to 15 secs.
Envt :
1. ibm_db 1.0.1 (mswin32)
2. Rails
3. Select Statement for one record on a remote machine
4. Query time >15 secs
Any help regarding this would be much appreciated.
Quetions :
1. What would I be doing wrong that causes the delay ?
2. I have tried both find_by_sql and find(:first... but same results. Any other option of running the query ?
3. Since no id column and legacy db ( set_table_name and set_primary_key ) implemented. Anything else to be done ?
|
|
|
