Hi
My requirement is
there are two databases. One is DB2 and other oracle.
When ever there is a new record getting into Db2 database I want my datastage to select those new records and load into oracle database into a specified location. Records keep adding may be in 1 min or 1 hour or 1 day. Thats not known but if a new record is added it should be updated in oracle.
Can anyone suggest me how to design a datastage job
If my requirement sounds vague pls reply me
thanks
Bye
Checking for updates in a database then migrating data
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
DataStage is not a replicating tool, if you want real time replication between DB2 and Oracle you need to search the market for a better solution or have a look at DB2 replication tools. The best you could do through DataStage is get a DB2 log scraper, there are a few on the market and I think Ascential/IBM sells one, and deliver changes to DataStage jobs.
Not sure exactly how the interaction between the log scraping and DataStage would work.
If you don't have a log scraper then your solution become ugly, you have to run SQL selects against every table on your DB2 database based on date fields to find new or changed records. You need to somehow trap and collect deletes, perhaps by putting triggers on every table on your database and writing the primary keys of deleted rows to a set of delete tables.
It is highly likely that any solution you try to build in DataStage without a log scraper or replication tool will cost you far more money in time and resources then buying or implementing a proper tool to do the job.
Not sure exactly how the interaction between the log scraping and DataStage would work.
If you don't have a log scraper then your solution become ugly, you have to run SQL selects against every table on your DB2 database based on date fields to find new or changed records. You need to somehow trap and collect deletes, perhaps by putting triggers on every table on your database and writing the primary keys of deleted rows to a set of delete tables.
It is highly likely that any solution you try to build in DataStage without a log scraper or replication tool will cost you far more money in time and resources then buying or implementing a proper tool to do the job.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
By designing a DS job to load data into Oracle and this job can be called at some specific event so that the input data can be loaded into Target DB. But you need to find a way to extract the changes from the source DB and loads the changed data into a file and this file would be the input.
Pooling technique to be used to extract data whenever there is a change in the source table.
Pls let us know if you really need some assistance in designing and developing this code for you.
Regards
Saravanan
Pooling technique to be used to extract data whenever there is a change in the source table.
Pls let us know if you really need some assistance in designing and developing this code for you.
Regards
Saravanan
Hi you got me. I was just thinking about pooling technique. Whats this pooling technique. Can you please tell me how to design my job using this pooling technique.elavenil wrote:By designing a DS job to load data into Oracle and this job can be called at some specific event so that the input data can be loaded into Target DB. But you need to find a way to extract the changes from the source DB and loads the changed data into a file and this file would be the input.
Pooling technique to be used to extract data whenever there is a change in the source table.
Pls let us know if you really need some assistance in designing and developing this code for you.
Regards
Saravanan
Thanks
Bye
Kiran