Page 1 of 1
Checking for updates in a database then migrating data
Posted: Tue Jun 07, 2005 6:25 pm
by kiran_418
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
Posted: Tue Jun 07, 2005 7:38 pm
by baabi_26
If a row gets added in your DB2 every min, should you update your oracle table immediately? Or Can you accumulate rows, say, for 1 day and start your update process and update all the rows changed in that day?
Posted: Tue Jun 07, 2005 8:03 pm
by vmcburney
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.
Posted: Wed Jun 08, 2005 3:14 am
by ray.wurlod
Log scrapers are to be resisted because of the danger that database vendors will change the format of transaction logs in future releases.
Posted: Wed Jun 08, 2005 3:17 am
by Sainath.Srinivasan
You need a real-time mechanism. Check the real-time options and let us know if you need any assistance.
Posted: Wed Jun 08, 2005 5:33 am
by elavenil
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
Posted: Wed Jun 08, 2005 10:42 am
by kiran_418
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
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.
Thanks
Bye
Kiran