I need to transfer some Ingres 6.4/06 databases from an old HP9000 box to a new IBM pSeries system running Oracle 9i. Is there any utilities within Ingres that would export databases to a specific filetype? I could then use Oracle SQL*Loader to import the data.
Do you mean export as comma (or other delimiter) delimited file?
Take a look at the copydb command. I cannot remember exact syntax. I'm pretty sure you can modify the sql scripts created if you need to set a specific delimiter. Check also the issue with exported data files > 2GB.
Yes, I need to export them as comma delimited ascii files.
I have another concern. It seems that the command will only take the actual USER's tables, not ALL tables. It would be crazy to login again with every other username to get all tables! Is there any way to get over this?
... unloaddb and copydb seems to only create the DDL, it won't create ascii files with every record in it. That's what I need. Also, the DDL files syntax won't be recognized by Oracle and it would be too long to change the file manually (the script is very big). Am I right to say that the only clean way would be to use a third-party utility? If so, which one would do the job?
You have to run the scripts created to get the data to unload. It's a 2-stage process. The utils create the SQL to export, you then run the SQL created to extract the data. You then need a util to create the tables in Oracle and load the data from the flat-files you just created.
Maybe there is a 3rd party util (or maybe one from Oracle) to make the loading/conversion easier.
You need to run unloaddb as the database owner (usually ingres). This will create an unload.ing which will export the data for when you run it. It will export all tables for all users if you run as datbase owner.
There will be a copy.out and copy.in created - if you wish to change the format of the output (ie commas or tab seperated etc..) then edit the copy.out using sed or awk (the file is normally quite big!) to the format of your choice.