From shyamperi@davlin.co.in Tue Aug  19 11:07:50 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 75549D1B524
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Aug 2003 08:07:50 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 26159-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Aug 2003 08:07:42 -0300 (ADT)
Received: from davlin.co.in (ns.vasnet.net.in [202.4.160.11])
	by svr1.postgresql.org (Postfix) with ESMTP id 501A0D1B56B
	for <pgsql-sql@postgresql.org>; Tue, 19 Aug 2003 08:07:23 -0300 (ADT)
Received: from Application (unknown [202.4.160.30])
	by davlin.co.in (Postfix) with ESMTP id 6AEA214B78
	for <pgsql-sql@postgresql.org>; Tue, 19 Aug 2003 16:22:11 +0530 (IST)
Message-ID: <1263873.1061290766755.JavaMail.tomcat@mail.davlin.co.in>
From: shyamperi@davlin.co.in
To: pgsql-sql@postgresql.org
Subject: Migration from db2 to postgres'
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="----=_Part_14_4936811.1061290766461"
Date: Tue, 19 Aug 2003 16:22:11 +0530 (IST)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=BAYES_30,
	HTML_10_20, NO_REAL_NAME
X-Spam-Level: *
X-Archive-Number: 200308/215
X-Sequence-Number: 14831

------=_Part_14_4936811.1061290766461
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

August 19th 2003
4:36p


Dear All,
I wanted to know how can I migrate all my data including the schema and the=
ir definitions,from IBM's db2 to the postgres.


Have a grate day

-----Warm RegardsSh=FFam PeriII Floor, Punja Building,M.G.Road,Ballalbagh,M=
angalore-575003 Ph : 91-824-2451001/5Fax : 91-824-2451050=20


DISCLAIMER: This message contains privileged and confidential information a=
nd is
intended only for the individual named.If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver this message.Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system.
------=_Part_14_4936811.1061290766461
Content-type: text/html
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename=Untitled1

<head></head><body marginwidth=3D4 marginheight=3D4 topmargin=3D4 leftmargi=
n=3D4><P><FONT face=3Dverdana size=3D2>August 19th 2003<BR>4:36p</FONT></P>=
<BR>
<P><FONT face=3Dverdana size=3D2>Dear All,<BR>I wanted to know how can I mi=
grate all my data&nbsp;including the schema and their definitions,from IBM'=
s db2 to the postgres.</FONT></P><BR>
<P><FONT face=3Dverdana size=3D2>Have a grate day</FONT><BR></P><PRE>-----<=
BR><STRONG>Warm Regards<BR></STRONG><EM>Sh=FFam Peri<BR></EM><BR>II Floor, =
Punja Building,<BR>M.G.Road,<BR>Ballalbagh,<BR>Mangalore-575003 <BR>Ph : 91=
-824-2451001/5<BR>Fax : 91-824-2451050 <BR><BR><BR></PRE><BR></body><br><br=
><b>

DISCLAIMER: This message contains privileged and confidential information a=
nd is
intended only for the individual named.If you are not the intended
recipient you should not disseminate,distribute,store,print, copy or
deliver this message.Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system.</b>
------=_Part_14_4936811.1061290766461--




From timander37@yahoo.com Tue Aug  19 12:43:53 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 7A4E6D1B980
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Aug 2003 09:43:53 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 42451-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Aug 2003 09:43:46 -0300 (ADT)
Received: from web10005.mail.yahoo.com (web10005.mail.yahoo.com
	[216.136.130.41])
	by svr1.postgresql.org (Postfix) with SMTP id 2CACED1B982
	for <pgsql-sql@postgresql.org>; Tue, 19 Aug 2003 09:43:42 -0300 (ADT)
Message-ID: <20030819124345.62081.qmail@web10005.mail.yahoo.com>
Received: from [129.186.237.18] by web10005.mail.yahoo.com via HTTP;
	Tue, 19 Aug 2003 05:43:45 PDT
Date: Tue, 19 Aug 2003 05:43:45 -0700 (PDT)
From: Tim Andersen <timander37@yahoo.com>
Subject: Re: Migration from db2 to postgres'
To: shyamperi@davlin.co.in
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1263873.1061290766755.JavaMail.tomcat@mail.davlin.co.in>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/216
X-Sequence-Number: 14832

> I wanted to know how can I migrate all my data
> including the schema and their definitions,from
> IBM's db2 to the postgres.
> 

I have done this with other DBMSs before by writing a
program (using PowerBuilder).  The core concept is
this:

two ODBC datasources (source, target)

for (loop through source's schemas){
  for (loop through source's tables){
    for(loop through source's rows){
      "select [row] from [source table]"
      "insert into [target table]"
    }
  }
}

The key is this:  I have a view in both databases to
compare system catalog tables across DBMS platforms.
It is actually the view I was trying to create if you
look back at the SQL questions I asked to this mailing
list a few days ago. the subject was: "Re: [SQL] About
primary keys -- made some progress"

In the past, I ran into several problems with
different datatypes and have had to write workarounds
for those (mainly blobs and timestamps).

Concerning referential integrity:  Two options.  You
could wait to add all of your foreign keys until after
all of the data has transferred from the source to the
target.  The other option is to run the nested loops
several times (this "fills in the gaps" and allows
other tables to be filled each time you run it).  I
prefer the second.

I also have a feature to compare the rowcounts of the
source and target so I can be sure all of my data has
been transferred.

I have not yet implemented PostgreSQL into this
application, but I intend to do that over the next few
months.

Tim


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com