Re: How to migrate data from Oracle to Informix

Hi, I just went through this exercise -- and the Informix side is a breeze -- it's Oracle that is a problem (although these hints you may already know). We went from Oracle 7.3 -- and as far as I could determine there is no Oracle equivalent of the 'unload' statement. After really ugly use of 'spool filename' from SQL*PLUS -- a consultant that was working on another project (and once worked for Oracle) gave the hint that instead of using: Select columna, columnb, columnc from atable; (which inserts lots andlots of spaces) use: Select columna||'|'||columnb||'|'||columnc||'|' from atable; (Informix's default delimiter is the pipe). It sounds like you already have this side of the picture solved -- but this was a great help to me (an Informix -- not an Oracle type of guy) You also need to fiddle with all the set commands in the sql script if you do it this way (as you probably know). You'll probably need to use dbload with the option set to commit every so often if you are loading to a logged Informix database and the files are of decent size. Otherwise the load command from DBACCESS or ISQL works fine. If the tables are huge, onload works very well -- but it will only load to an unlogged database. I'm not sure about the high performance loader -- it doesn't work with the 7.22uc2 release we are using, and when I tried to update to 7.23uc4 so I could try it, it crashed our system. I hear really good things about it if you need to get large loads done quickly. Hope this is of some help. As I said, I just completed this conversion, so if I can be of any more help let me know. Tom Wouters wrote: > > I want to get the data I have in an Oracle 7.3 database into an Informix > database. > How do I do that ? > > Is there a tool that migrates the data between the two systems ? > Maybe there is a tool to load data stored in flat files into Informix. I > can get the data from Oracle to a flat file in almost any format. > > Any suggestions ? > > Tom -- Tom Grenier ########################################################### # Home: Office: # # BizTravel.com # # The Internet company for frequent # # business travelers(sm.) # # Phone: (800) 497-1969 (617) 621-3919 x115 # # e-mail: tom@sqlman.com tomg@biztravel.com # # web page: http://www.sqlman.com http://www.biztravel.com # ########################################################################