CODEWITHSUNDEEP
databasewindowsjdbcdb2odbc
Alexander Tobias Bockstaller
Alexander Tobias Bockstaller

Reputation: 4746

Connecting to DB2 through ODBC is extremely slow

I am experiencing extremely slow connections to a local DB2 (v9.7 Express-C) database through ODBC on a Windows 7 system (x64). I wrote several test programs, each connecting to a local database for 100 times in a row (without querying) and measuring the time. Results are:

Average time for connection to a local database using different methods C++ x86 DB2/ODBC: 32bit C++ application, connecting to a local DB2 database via CDatabase
C++ x86 DB2/ODBC: same as above, but x64 application
PHP DB2/ODBC: Connecting to a local DB2 database via odbc_connect.
PHP MySQL/ODBC: Connecting to a local MySQL database via odbc_connect.
JDBC DB2: Java application, connecting to a local DB2 database using the DB2 JDBC driver.
JDBC MySQL: Java application, connecting to a local MySQL database using the MySQL JDBC driver.

My first thought was that something's wrong with my ODBC configuration, but as you can see connecting to a MySQL database through ODBC works like charm. On the other hand, connecting to DB2 with the JDBC driver instead of ODBC works fine too (although it's still a lot slower than MySQL).

I found this related question, but it does not apply to me. Any hints on what might be causing this and how to resolve would be great!


Supplementary question: What are common connection times when connecting to a DB2 database through ODBC? Is it normal for the DB2 ODBC driver to be so slow?!

Upvotes: 1

Views: 3074

Answers (1)

mustaccio
mustaccio

Reputation: 18945

One thing that you should look at is the database activation. DB2 activates the database upon the first connection and deactivates it when the last connection is closed. During activation DB2 allocates transaction log files, memory pools, and performs a number of other tasks that take time.

This behaviour does not depend on the type of connection (ODBC or JDBC); the difference you observe might be explained by an extra connection that keeps the database active during your JDBC tests.

Try issuing ACTIVATE DB <YOURDB> before your tests, which will keep the database active even in the absence of connections. There is no way to activate databases by default, apart from issuing this command upon the system startup, e.g. from a batch file.

Upvotes: 3

Related Questions