Postgres Support ?

greenplum · Post by **greenplum** » Fri Jul 14, 2006 6:41 pm

Is anyone working on a Postgres Stage? The ODBC connection is a bit slow for large loads. It also appears that the Linux Postgres ODBC driver is not supported by IBM. Seems like the best thing to do would be to create a stage plugin (not sure I'm using the correct terminology) for Postgres. Anyone out there already working on such a project?

kumar_s · Post by **kumar_s** » Fri Jul 14, 2006 8:02 pm

May I know what is Postgres?

greenplum · Post by **greenplum** » Fri Jul 14, 2006 8:07 pm

kumar_s wrote:May I know what is Postgres?

Postgres is an open source database.

http://www.postgresql.org/

kumar_s · Post by **kumar_s** » Fri Jul 14, 2006 9:01 pm

Humm... Thanks for that.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 15, 2006 4:35 am

That a driver is not supported by IBM doesn't mean you can't use it. That's the entire point of OPEN DataBase Connectivity. All you need to do is to have its library findable (LD_LIBRARY_PATH environment variable, set in the dsenv script) and edit the .odbc.ini file to refer to it. Use any of the other entries therein as a prototype. You will also need to emplace an entry in the uvodbc.config file in your project to indicate that your data source name (DSN) is of type ODBC rather than of type UNIVERSE, so that the driver will be loaded.

greenplum · Post by **greenplum** » Sat Jul 15, 2006 4:28 pm

ray.wurlod wrote:That a driver is not supported by IBM doesn't mean you can't use it. That's the entire point of OPEN DataBase Connectivity. All you need to do is to have its library findable (LD_LIBRARY_PATH environment variable, set in the dsenv script) and edit the .odbc.ini file to refer to it. Use any of the other entries therein as a prototype. You will also need to emplace an entry in the uvodbc.config file in your project to indicate that your data source name (DSN) is of type ODBC rather than of type UNIVERSE, so that the driver will be loaded.

Yes, we already are using the existing Postgres ODBC driver--unfortuately we have run into a few issues (problems we don't see with other ODBC based products using the same driver). As I mentioned before, when you contact IBM support, they won't help because the driver is not on their supported list.

In addition to the bugs, loading large datasets through ODBC is not the quickest of alternatives. IBM has other RDBMS "plug-ins" that take advantage of the database products high speed loader among other DB specific capablities. I'm curious if any one is working on or has seen such a plug-in for postgres--I can't find anything via the Postgres community.

Having that said, I would be very interested to hear any ideas you might have on getting IBM to support the existing Postgres ODBC driver.

kcbland · Post by **kcbland** » Sat Jul 15, 2006 7:43 pm

I did a project two years ago with MySQL, it was the same situation. What we ultimately did for the customer was use the 3rd party ODBC driver simply for metadata gathering, but did all DB processing thru scripts. We found that the command line was suitable for the types of processing you do in ETL. Most queries are SELECT * from table WHERE blah blah blah, and most loads were bulk loading inserts and then updates where bulk loads into work tables and then a merging query like a correlated update.