Re: How to convert Oracle database to Postgresql.

On Wed, 2003-06-18 at 07:49, Carlos Oliva wrote:
> Hi Wayne,
> Yes.  I would not mind getting a copy of the code.  I wrote a java
> program to copy the data via JDBC and it works but it is rather slow.  I
> am not using triggers either but I a use lots of blobs (I converted them
> into bytea also)
> Regards
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wayne Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18
> Jun 2003 08:12:00 -0400 Hi Carlos,  We've just been through this
> recently.  We used db2look to get the ddl for the database, ran some
> editing scripts over it (just ex) and fed that into postgres.  To load
> data from the db2 database, I wrote a smalltalk program that for each
> table, read from the db2 dtabase and inserted into the postgres
> database. The smalltalk needs no prior knowledge of the table layouts to
> do that- queries syscat.tables in the db2 database to get a list of
> tables etc. You are welcome to the smalltalk executable (and source if
> you want it (it is not really production code though :) )
>
> We used bytea in place of blob.
> Triggers we did manually (we are not big users of triggers).
>
> All in all it was pretty easy. Places we came unstuck were on views
> using the "with temp" syntax, and some recursive sql which we rewrote
> using pgplsql and temp tables. Also Db2 will hapilly store 24:00:00 as
> well as 00:00:00 for midnight. It's worth updateing any occurennces of
> 24:00:00 to 00:00:00 before you convert.

Wouldn't it be faster to do host-based conversions to tab-delimited
files that can be loaded into postgres via COPY?

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------