Thread: Migration from db2 to postgres'

August 19th 2003
4:36p


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


Have a grate day

-----Warm RegardsShÿam PeriII Floor, Punja Building,M.G.Road,Ballalbagh,Mangalore-575003 Ph : 91-824-2451001/5Fax :
91-824-2451050 


DISCLAIMER: This message contains privileged and confidential information and 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.

> 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