DSXchange

Hi All,

Assume the case
---------------

If a SQLServer Database is having 1000 tables and 100 stored procedures, how to transfer all the 1000 tables and 100 stored procedures to Oracle Database. Is there any option in DataStage which is similar to Data Transformation Service in SQLServer.

Thanks in advance,
T Sankar

Even though it is more powerful than a speeding locomotive, this isn't a job for DataStage... or any other ETL tool, I would imagine. This just seems like 'normal' DBA work to me.

Now, perhaps once the tables and procedures have been migrated, you could look at using DataStage to transfer the *data*, but you'd end up writing 1000 jobs to do it. Must be a better way.

-craig

Sakar,

craig is right. DataStage is a Tool to transfer Data on a permanent and frequent basis including a certain transformation. In doing this DataStage is a very good an flexible Tool. For to transfer Tables as they are and only once you should simply copy them. Your problem will be the Stored procedures and any kind of Triggers if you have them. It may be that you have to rewrite them all.

But you cannot blame DataStage for this. It is the same as to use a screwdrive for to dig the garden.

Wolfgang

AND (I am assuming from the requirement to move stored procedures) the original poster wants to move table definitions (metadata) as well.
To some extent this can be handled via MetaStage, however there is no guarantee that stored procedures from SQL Server will work in Oracle. In fact I would go so far as almost to guarantee that none will; they use an almost totally different syntax.

You need the Oracle Migration Workbench, designed to simplify the process of migrating 3rd party databases to Oracle. Have a look at this link:
http://otn.oracle.com/tech/migration/wo ... ntent.html

Note that it can migrate stored procedures and triggers. I've been on a couple projects where this would have saved a lot of time!

Microsoft has information on migration from Oracle to SQL Server with good descriptions of the differences between the two:
http://www.microsoft.com/sql/techinfo/d ... Oracle.asp

regards
Vincent

I too think that Oracle Migration Workbench is the best tool available for this specific purpose. Also this is available as free down load from Oracle.

This tool proved to be a boon when migrating an application and DB
from MS Access to Oracle. With very little effort we were able
to migrate the DB into Oracle and the Access Application was also
working with the new Oracle DB.

Amaruvi

I think you can also do this with MetaRecon. To the best of my knowledge I do remember that MetaRecon can analyse any DS connected throught ODBC. So in your case if you have MetaRecon, just take SQL server as your input and you can select Oracle as an target database. MetaRecon also created basic jobs for you to transform data from the source to target databases.

Just an idea ...