DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 160

Points: 1912

Post Posted: Sun May 13, 2018 11:46 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42671
Location: Denver, CO
Points: 219762

Post Posted: Mon May 14, 2018 7:04 am Reply with quote    Back to top    

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. Wink

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.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 160

Points: 1912

Post Posted: Tue May 15, 2018 12:56 am Reply with quote    Back to top    

Thanks Craig.


Regards,
S.R
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54287
Location: Sydney, Australia
Points: 294450

Post Posted: Tue May 15, 2018 3:28 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42671
Location: Denver, CO
Points: 219762

Post Posted: Tue May 15, 2018 6:58 am Reply with quote    Back to top    

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. Wink

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 335

Points: 3391

Post Posted: Tue May 15, 2018 9:53 am Reply with quote    Back to top    

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).
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours