Connection pooling issues with unixODBC, IBM DB2, and pyodbc

2
votes

tldr; Have a working setup of Python->DB2, using unixODBC, but connection pooling is not working.

I've tried using the driver directly:

conn = pyodbc.connect('DRIVER=/opt/ibm/db2/lib/libdb2.so;Hostname=mydb2.domain.com;PORT=1234;DATABASE=database1;UID=user1;PWD=password1')

By driver name:

conn = pyodbc.connect('DRIVER=DB2;Hostname=mydb2.domain.com;PORT=1234;DATABASE=database1;UID=user1;PWD=password1')

DSN:

conn = pyodbc.connect('DSN=server1UID=user1;PWD=password1')

I've been using this Python script to test:

import sys
import timeit

#import pypyodbc as pyodbc
import pyodbc


#conn = pypyodbc.connect('DSN=server1;UID=user1;PWD=password1')
def query():
    try:
        conn = pyodbc.connect('DSN=server1;UID=user1;PWD=password1')
        cur = conn.cursor()
        cur.execute("select current timestamp from sysibm.sysdummy1")
        row = cur.fetchone()
        print('.', end='', flush=True)
        conn.close()
    except Exception as ex:
        print(ex, end='', flush=True)

t = timeit.Timer(query)
count = 50
duration = t.timeit(count)
print("\n{count} @ {duration}/sec".format(count=count, duration=count/duration))

(When logging is enabled, in odbcinst.ini, it shows a connection open/close for every query)

In my dev environment the above gets around ~10 query/sec

If the connection is created outside of the loop, and cached, then ~120 query/sec

If I install the ibm_db_dbi and use connect the results are ~10 query/sec, and using pconnect ~60 query/sec.

I've also tried iODBC, but I can't get it to connect at all, and on Windows, using ODBC, connection pooling appears to be working as expected.

Why unixODBC and not ibm_db_dbi?

  • connect doesn't pool
  • pconnect fails catastrophically, and never recovers, after network interruptions. It's also half the speed of a cached connection.
  • pyodbc seems better maintained than the IBM driver.

Setup:

  • Debian Jessie
  • Docker 1.7 container
  • IBM DB2 ODBC v10.5 library installed
  • unixODBC 2.3.4
  • Python 3.5
  • pyodbc 3.0.10

Configuration:

odbcinst.ini

[ODBC]
Pooling         = Yes
Trace           = No
TraceFile       = /var/log/odbc.log

[DB2]
Description     = IBM DB2 Adapter
Driver          = /opt/ibm/db2/lib/libdb2.so
Driver64        = /opt/ibm/db2/lib/libdb2o.so
FileUsage       = 1
DontDLClose     = 1
CPTimeout       = 1000
CPTimeToLive    = 100
Threading       = 2
Pooling         = Yes

odbc.ini

[server1]
Description    = Server 1
Driver         = DB2

db2cli.ini

[server1]
Database      = database1
Protocol      = TCPIP
Hostname      = mydb2.domain.com
ServiceName   = 1234

References:

1
db2odbcpyodbcunixodbcdb2-400
According to this you may need to set CPTimeout to a non-zero value in the datasource section of odbcinst.ini. Alternatively, you could use the DB2 driver connection pooling instead. – mustaccio
@mustaccio - I edited the question to add both CPTimeout and CPTimeToLive; I missed them when copying my snippet =S – Justin

1 Answers

0
votes

It looks like you have Pooling = Yes in the wrong place, it needs to be under the [ODBC] heading, not the driver heading

http://www.unixodbc.org/doc/conn_pool.html