DSXchange

Hi All,


We have a situation in which we need to load data from sql server to Oracle staging area. It is a one to one data load(Metadata will differs for each table).But, i have so many tables(15) needs to be migrated using datastage. It is an initial load. Once that has been done, we will need to design the same kind of job for incremental load(Truncate staging tables) every day.


Note: We can use RCP, but my worry would be if there were any data type conversion happens from sql server to Oracle.


Kindly let me know your thoughts on the same.


Thanks,
S.R

I personally would not consider 15 to be any kind of "many". In the time you spend pondering how to do them using RCP and schema files, you could have developed and tested all 15.

And I long ago gave up on designing different jobs for an initial load versus a daily incremental load... unless that very first load is many orders of magnitude larger in volume than what you will load daily, something that would greatly benefit from say, a bulk load. Last few projects however, we just used the incremental job design for all loads, initial or otherwise, without issue. For the timestamp that controls your incremental starting point, just set it to something appropriately early... we used "01/01/1980" as our "beginning of time" date for my current project.

Hope some of that helps.

Thanks Craig.


Regards,
S.R

That said, it is possible. I once created three generic jobs to move 823 tables from Oracle to MySQL. The only reason for requiring more than one was the need to manage some RAW fields. The jobs read table metadata from the Oracle catalog, and constructed appropriate CREATE TABLE and INSERT statements using that information.

That said, did something similar back in the day... one job that created the ~600 Server jobs we needed in dsx format. Fed it the table names then imported the result. Good times.

you can make the first part unique and the back end of the job reusable to protect your metadata. The front piece would do any manual conversions and corrections. Or you could make the RCP version, validate your tables, and if any are incorrect write unique jobs for only those tables?

you can also make the RCP type job work via using a schema file for each table (instead of RCP, schema file, that is but same idea).