Checking for updates in a database then migrating data

kiran_418 · Post by **kiran_418** » Tue Jun 07, 2005 6:25 pm

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

baabi_26 · Post by **baabi_26** » Tue Jun 07, 2005 7:38 pm

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?

vmcburney · Post by **vmcburney** » Tue Jun 07, 2005 8:03 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 08, 2005 3:14 am

Log scrapers are to be resisted because of the danger that database vendors will change the format of transaction logs in future releases.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jun 08, 2005 3:17 am

You need a real-time mechanism. Check the real-time options and let us know if you need any assistance.

elavenil · Post by **elavenil** » Wed Jun 08, 2005 5:33 am

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

kiran_418 · Post by **kiran_418** » Wed Jun 08, 2005 10:42 am

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