Reusable Job Design

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Reusable Job Design

Post by satheesh_color »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

Post by satheesh_color »

Thanks Craig.


Regards,
S.R
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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).
Post Reply