Re: importing/translating from DBMS en masse to SQL

On May 9, 3:08 pm, mabbu..._at_yahoo.ca wrote:
> Hi, we're looking at a way to take all of our data in Oracle CODASYL
> DBMS (running on OpenVMS) and export it into a SQL Server db nightly
> (or almost nightly). Currently, we have the CONNX integration/ODBC
> connector tool running, from which we can run SQL queries against the
> DBMS databases, but searching for and collecting even moderate amounts
> of data takes a long time.
>
> We are, however, making nightly full backups of the databases on the
> DBMS side. So, I'm wondering if anyone's had success in doing this
> effeciently in some fashion, possibly using the backup files in some
> way...
>
> I haven't had a great deal of success with searching for this issue
> (other than consultants offering to convert the whole DB to Oracle
> RDB, which we don't want to do right now), so any suggestions
> appreciated.
>
> Thanks!

I am not familiar with the Oracle CODASYL database but you should be able to write programs that extract the data into character format then you should be able to transfer these files over to the target platform where you could use BCP or DTS to load the data into SQL Server.

We extract data from IMS this way and send it to Oracle via ftp where a waiting program grabs new files on arrival and inserts the data into Oracle. We run this both ways so it is possible to enter data into Oracle and see it in IMS about two seconds later. Our process requires a couple of table trigger, a couple of daemons, and use to the mainframe internal reader but you do not need anything this complicated.

Just extract, ftp, insert.

HTH -- Mark D Powell -- Received on Thu May 10 2007 - 19:50:56 CDT