Adding timeout to ibm_db.connect()

[Ron Oommen Thomas]

Hi,

I'm connecting to a remote DB2 database and when the server is down, the control never comes out of 

ibm_db.connect(<remote db connection>)

I checked for a way to add timeout to this connect method and strangely I couldn't find anything, except another thread within this group
https://groups.google.com/forum/#!topic/ibm_db/x2IO45NN1Js

But I can't edit the ini files as I'm connecting to a remote server. Also, I can't execute DB queries on those servers, so the above solution doesn't work for me.

All I want is to add a timeout like we do for socket connections

socket.settimeout(10)

Thanks,

Ron 

[Saba Kauser]

Hello Ron,

Can you try to use  ConnectTimeout odbc and cli keyword in your connection string and see if you get error back?
You could set it in db2cli.ini file or db2dsdriver.cfg file or in connection string as explained in the another thread you mentioned.

You should be able to use a db2dsdriver.cfg or db2cli.ini or the connection string irrespective of local or remote server.
More information on the keyword can be found here:
https://www.ibm.com/support/knowledgecenter/SSSNY3_10.1.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0021533.html

-Saba.

[Ron Oommen Thomas]

Hi Saba,

This is how my connection string looks like :

def connect_to_database():
    dsn_query = 'DATABASE=%s;' 'HOSTNAME=%s;' 'PORT=%d;' 'PROTOCOL=%s;' 'UID=%s;' 'PWD=%s;'%(database, host, port, protocol, user, password)
    conn_str="dsn='%s';ConnectTimeout=1"%(dsn_query)
    db_connection = connect(conn_str, '', '',)
    return db_connection

but it returns this error :

[IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "'DATABASE=<databasename>".

SQLCODE=-1531

[Ron Oommen Thomas]

Hi Saba,

Thank you for replying back.

I'm not using a DB2 client in my machine. I just use the Python script to connect to remote DB2 servers, so I have no way to see db2cli.ini file or db2dsdriver.cfg file.

I tried using the connection string and I got this error 

[IBM][CLI Driver] SQL1531N  The connection failed because the name specified with the DSN connection string keyword could not be found in either the db2dsdriver.cfg configuration file or the db2cli.ini configuration file.  Data source name specified in the connection string: "DATABASE=<databasename>".
 SQLCODE=-1531

So, is there any other way than editing those files ?

Thanks,

Ron

[Saba Kauser]

Hi Ron,

Can you try like this :
import ibm_db
conn_str='database=image;querytimeout=1;connecttimeout=10;hostname=hostname;port=portno;protocol=tcpip;uid=username;pwd=passwd'
conn = ibm_db.connect(conn_str,'','')

You should be able to see below in cli trace files:
SQLDriverConnectW( )
    <--- SQL_SUCCESS   Time elapsed - +2.348000E-003 seconds
( UID="username" )
( PWD="" )
( DATABASE="image" )
( QUERYTIMEOUT="1" )
( CONNECTTIMEOUT="10" )
( HOSTNAME="hostname" )
( PORT="portno" )
( PROTOCOL="TCPIP" )

to collect above trace file:
db2trc on -cli -f del.dmp
<run your repro>
db2trc off
db2trc fmt -cli del.dmp del.cli

and you can see above entry in del.cli.

You should be able to locate clidriver under site-packages of your python installation.
e.g:
/home/sakauser/python2710/lib/python2.7/site-packages/clidriver

and you can have your own db2cli.ini or db2dsdriver.cfg under cfg folder
 db2trc utility can be found under /home/sakauser/python2710/lib/python2.7/site-packages/clidriver/adm

HTH.
-Saba.

[Ron Oommen Thomas]

Hi Saba,

Thank you for the reply.

Currently the servers are all up, so I can't recreate that exact scenario now but some of the DB instances are down. So, when I try to connect to those DB instance with the code snippet you provided, it waits for that much time period and exits with this error message

Exception: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the error was detected: "<ip-address>".  Communication function detecting the error: "selectForConnectTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001
 SQLCODE=-30081

I think this solution is working as I can see "selectForConnectTimeout" in the error logs. :)

I have one doubt in the code snippet, as to the difference between 

"querytimeout=1" and "connecttimeout=10"

Does connection timeout denotes the time the ibm_db module waits to establish a connection with the server ? If so, what will querytimeout accomplish ?

Thanks and Regards,

Ron

[Saba Kauser]

That's great to hear Ron!

You can disregard Querytimeout setting as it is actually meant for statement execution cancellation.  It does not have anything to do with the connection timeout.
You can get more information on Querytimeout here : https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.swg.im.dbclient.config.doc/doc/r0057060.html

Have a good day!
-Saba

[Ron Oommen Thomas]

Yes, thanks a lot Saba  for the help :)

Have a good day  

Ron