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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Wayne Armstrong
> Sent: Wednesday, June 18, 2003 8:34 AM
> To: Carlos; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
>
>
> ** Reply to message from "Carlos Oliva" <carlos(at)pbsinet(dot)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(dot)l(dot)johnson(at)cox(dot)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) |
+-----------------------------------------------------------