Blog do projektu Open Source JavaHotel

piątek, 28 września 2012

db2odbc_fdw, db2 and postgresql

Introduction
Postgresql FDW and ODBC allows to get access to all databases having ODBC interface. But I'm not happy with this odbc_fdw implementation and decided to create my own.
Implementation
The source code is available here. Just download, read README file and make install. At the beginning I planned to create DB2 specific fdw using CLI, (Call Level Interface) but because DB2 CLI is almost identical with ODBC I decided to implement purely ODBC interface.
Comparison with odbc_fdw
  • Much smaller (600 lines of source code against 1200)
  • Simpler : just define DSN name, ODBC credentials mapping and query to run
  • Full signalling of connection and query errors.
  • Proper handling of NULL value
Problems
  • Wrapper reconnects with ODBC database every time foreign table is scanned. The performance could suffer that way. Maybe worth considering is connecting only for the first time and next time table is scanned just reuse connection opened before. But it requires passing state between wrapper invocation and could be the source of several problem.
  • Tuple is created by PostgreSQL API method BuildTupleFromCStrings. So ODBC firstly decodes all columns to string and later PostgreSQL engine encodes these string to appropriate format. Maybe performance would improve if direct data type were used without string intermediary format. 
  • For some reason DB2 decimal are converted to string format using coma (,) as decimal character. But posgresql expected dot (.) as decimal character so rather awkward solution (for number data types replace all , with .) is used. Some more elegant solution is needed.
  • PlanForeignScan is implemented in a simple way - just put some constant values. odbc_fdw solution run (select * from .) query to retrieve number of rows in the query. But using this solution means performance degradation - instead of one query two queries are executed. So in search of more advanced solution without performance degradation.
  • What about 'big' columns like: LOB, BLOB etc.
Future
The solution was tested on Fedora 17 (64 bit) against DB2 10.0 database. Next step is to test db2odbc_fdw wrapper against other databases and also test it on Windows platform. But to accomplish it I'm planning to prepare some regression test and run this test again every database. The main purpose is to check if all standard data types are converted correctly.


Brak komentarzy:

Prześlij komentarz