Thread: How to convert Oracle database to Postgresql.
Dear all, Is there any script/tool I can use to convert Oracle database to Postgresql? Thanks Ursula
Ursula Lee wrote: > Dear all, > > Is there any script/tool I can use to convert Oracle database to > Postgresql? The /contrib directory of the distribution contains an ora2pg script used to generate a conversion. I've never used it though. Mike Mascari mascarm@mascari.com
Hi Ursula, That script only works for Oracle 8.x though, and needs modification for Oracle 9i. :-) Regards and best wishes, Justin Clift Mike Mascari wrote: > Ursula Lee wrote: > > >>Dear all, >> >>Is there any script/tool I can use to convert Oracle database to >>Postgresql? > > > The /contrib directory of the distribution contains an ora2pg script > used to generate a conversion. I've never used it though. > > Mike Mascari > mascarm@mascari.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Is there a script or method to convert a DB2 database also?
Thanks
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift
Sent: Wednesday, June 18, 2003 7:02 AM
To: Mike Mascari
Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
Hi Ursula,
That script only works for Oracle 8.x though, and needs modification for
Oracle 9i.
:-)
Regards and best wishes,
Justin Clift
Mike Mascari wrote:
> Ursula Lee wrote:
>
>
>>Dear all,
>>
>>Is there any script/tool I can use to convert Oracle database to
>>Postgresql?
>
>
> The /contrib directory of the distribution contains an ora2pg script
> used to generate a conversion. I've never used it though.
>
> Mike Mascari
> mascarm@mascari.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
your
> message can get through to the mailing list cleanly
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
** 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. Regards, Wayne Armstrong, Bacchus Management Systems. > Is there a script or method to convert a DB2 database also? > Thanks > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift > Sent: Wednesday, June 18, 2003 7:02 AM > To: Mike Mascari > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql. > > > Hi Ursula, > > That script only works for Oracle 8.x though, and needs modification for > Oracle 9i. > > :-) > > Regards and best wishes, > > Justin Clift > > > Mike Mascari wrote: > > Ursula Lee wrote: > > > > > >>Dear all, > >> > >>Is there any script/tool I can use to convert Oracle database to > >>Postgresql? > > > > > > The /contrib directory of the distribution contains an ora2pg script > > used to generate a conversion. I've never used it though. > > > > Mike Mascari > > mascarm@mascari.com > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that > your > > message can get through to the mailing list cleanly > > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
On 18 Jun 2003 at 22:33, Wayne Armstrong wrote: > ** 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. I would say this is worth a HOWTO. Would you do that? Bye Shridhar -- Decision maker, n.: The person in your office who was unable to form a task force before the music stopped.
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. Regards, Wayne Armstrong, Bacchus Management Systems. > Is there a script or method to convert a DB2 database also? Thanks > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift > Sent: Wednesday, June 18, 2003 7:02 AM > To: Mike Mascari > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql. > > > Hi Ursula, > > That script only works for Oracle 8.x though, and needs modification > for Oracle 9i. > > :-) > > Regards and best wishes, > > Justin Clift > > > Mike Mascari wrote: > > Ursula Lee wrote: > > > > > >>Dear all, > >> > >>Is there any script/tool I can use to convert Oracle database to > >>Postgresql? > > > > > > The /contrib directory of the distribution contains an ora2pg script > > used to generate a conversion. I've never used it though. > > > > Mike Mascari > > mascarm@mascari.com > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that > your > > message can get through to the mailing list cleanly > > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
It would be great if besides SmallTalk, the scripts could be written in
plain Linux scripts or Python
Regards
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Shridhar
Daithankar
Sent: Wednesday, June 18, 2003 8:42 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to convert Oracle database to Postgresql.
On 18 Jun 2003 at 22:33, Wayne Armstrong wrote:
> ** 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.
I would say this is worth a HOWTO. Would you do that?
Bye
Shridhar
--
Decision maker, n.: The person in your office who was unable to form
a task
force before the music stopped.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
** Reply to message from "Carlos Oliva" <carlos@pbsinet.com> on Wed, 18 Jun 2003 08:49:52 -0400 Hi Carlos, Is email ok for delivery (probably about 10 meg all up)? Regards, Wayne > 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. > > > Regards, > Wayne Armstrong, > Bacchus Management Systems. > > > Is there a script or method to convert a DB2 database also? Thanks > > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin Clift > > Sent: Wednesday, June 18, 2003 7:02 AM > > To: Mike Mascari > > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold > > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql. > > > > > > Hi Ursula, > > > > That script only works for Oracle 8.x though, and needs modification > > for Oracle 9i. > > > > :-) > > > > Regards and best wishes, > > > > Justin Clift > > > > > > Mike Mascari wrote: > > > Ursula Lee wrote: > > > > > > > > >>Dear all, > > >> > > >>Is there any script/tool I can use to convert Oracle database to > > >>Postgresql? > > > > > > > > > The /contrib directory of the distribution contains an ora2pg script > > > used to generate a conversion. I've never used it though. > > > > > > Mike Mascari > > > mascarm@mascari.com > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that > > your > > > message can get through to the mailing list cleanly > > > > > > -- > > "My grandfather once told me that there are two kinds of people: those > > who work and those who take the credit. He told me to try to be in the > > first group; there was less competition there." > > - Indira Gandhi > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
I think so. You may want to consider zipping the file also. -----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:59 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:49:52 -0400 Hi Carlos, Is email ok for delivery (probably about 10 meg all up)? Regards, Wayne > 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. > > > Regards, > Wayne Armstrong, > Bacchus Management Systems. > > > Is there a script or method to convert a DB2 database also? Thanks > > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Justin > > Clift > > Sent: Wednesday, June 18, 2003 7:02 AM > > To: Mike Mascari > > Cc: Ursula Lee; pgsql-general@postgresql.org; gilles Darold > > Subject: Re: [GENERAL] How to convert Oracle database to Postgresql. > > > > > > Hi Ursula, > > > > That script only works for Oracle 8.x though, and needs modification > > for Oracle 9i. > > > > :-) > > > > Regards and best wishes, > > > > Justin Clift > > > > > > Mike Mascari wrote: > > > Ursula Lee wrote: > > > > > > > > >>Dear all, > > >> > > >>Is there any script/tool I can use to convert Oracle database to > > >>Postgresql? > > > > > > > > > The /contrib directory of the distribution contains an ora2pg > > > script used to generate a conversion. I've never used it though. > > > > > > Mike Mascari > > > mascarm@mascari.com > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that > > your > > > message can get through to the mailing list cleanly > > > > > > -- > > "My grandfather once told me that there are two kinds of people: > > those who work and those who take the credit. He told me to try to > > be in the first group; there was less competition there." > > - Indira Gandhi > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
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) | +-----------------------------------------------------------
Hi Ron, Probably .... The java program I wrote works by copying the tables via JDBC so it does not produce any text files and relies on the capabilies of JDBC to treat SQL types without having to specify them. It is similar to methods published to copy databases in the server. In fact it copied a BLOB from DB2 into a bytea of PostgreSQL without my having to deal with SQL types (I tried a program that copy tables via ODBC but it failed everytime that a BLOB had to be copied). I imagine that one could output data from tables into text files by outputing the data as objects. In this way one may not have to deal with the different SQL types. I am not sure about other aspects of the database such as triggers -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson Sent: Wednesday, June 18, 2003 10:12 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] 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) | +----------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
** Reply to message from Ron Johnson <ron.l.johnson@cox.net> on 18 Jun 2003 09:11:34 -0500 Yup, But then you need to reliably convert from quote, separated to tab delimited, look after different representation of time and timestamp and null values (and some other formatting issues which I can't remember. If youv'e got clobs/blobs to transfer (or carriage returns in varchar fields (fine in db2))also, there is just no good way to do it. So slower, but lots less labor intensive is ok for me. Regards, Wayne > 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) | > +----------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html