Blog do projektu Open Source JavaHotel

czwartek, 3 stycznia 2013

db2odbc_fdw, db2 and postgresql

Introduction
I created a new version of DB2/ODBC FDW extension. This version implements connection cache but previous functionality (without cache) is still maintained.
Source code and README is available here.
Problem
Without cache any reference to foreign table triggers a sequence: connect -> query execution -> disconnect. It is very convenient because you don't need to bother about connection to external dataserver but means significant performance degradation because establishing connection to a database (particularly if foreign database is hosted by other server) is very costly. It does not matter if you have relatively small number of connections and costly sql statement to execute but could matter otherwise.
So the new version keeps connection in a local cache and reuse it later. But it is related  to another problem. What to do if in the meantime connection to a foreign database is broken or foreign database is stopped or restarted ? ODBC does not have any method for probing if ODBC connection (SQLHDBC handler) is alive.
Solution
To define a cached connection an additional parameter is required. Example

CREATE SERVER db2odbc_servercached 
        FOREIGN DATA WRAPPER db2odbc_fdw 
        OPTIONS (dsn 'TSAMPLE' , cached '-30081');

Parameter 'cached' has double meaning:

  1. Defines 'cached' connection.
  2. Define the native exception code which triggers connection retry after query execution failure (-30081 in case of DB2). It allows to distinguish between sql failure caused by the broken connection and failure caused by another reason (for instance invalid query syntax). Value -1 means "retry always". Retry is done only once and the second failure fails the whole statement.
Test
I also created a set of regressive tests (in terms of BoaTester) just covering the basic functionality. The tests are available here.  The test scenarios are something like:
  1. Connect to DB2, create a table and insert several rows.
  2. Create a corresponding foreign table in Postgresql.
  3. Run a statement and verify that foreign table in Postgres has the same content as original.
  4. Run a sequence (100) of statement against foreign table to be sure that connection reuse is working.
Additional problem
The connection cache is implemented as a simple list and the beginning of the list is kept as a static variable in the module. It is not thread safe and further investigation is necessary.

Additional remarks, future
The solution has been tested against DB2 (version 10.1) but should work with any other database server. Additional tests will be performed in the future.
In case of DB2 also test against SP (stored procedure) returning result set will be performed.

Brak komentarzy:

Publikowanie komentarza