Incremental Table Transfer....

devaraj4u · Post by **devaraj4u** » Thu Feb 20, 2003 2:24 pm

Hi All,

I designed Job using DataStage to do ETL from Relational Oracle schema to Star Schema.I finished creating jobs it is working fine.I want to do incremenal backup of the tables.I hope u can understand what it means.ie I want first time Insertion (Update Action of ODBC Stage) 2nd time onwards i want do (Insert New or Update existing rows OF odbc Stage).how can i do this.Can any one please help me.

Eg

Source Oracle Table (A) ---> ETL ---> Target Oracle Table (TA)

when I run the job first time it needs to insert rows. when i m suppose running the job for 2nd time or nth time it needs to do (Insert New or Update Existing Rows Update ACtion of ODBC Stage).

So Please give me solution for this problem.

Note:it is very urgent , SO please Please any one of you help me.

Thanks & Regards,
K.S.Rajan.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 20, 2003 4:10 pm

Why is everything you post urgent or very urgent, while nobody else's posts are? Usually on this forum you get one or two good responses within 24 hours (and that delay's only because most of the world does not live in your time zone). Please don't push so hard. If it's REALLY genuinely urgent, hire an expert consultant's time.

There are several strategies for incremental transfers, some of which depend on your being able to identify the changed rows.

Firstly, you can do what you propose (use "Insert New Rows or Update Existing Rows" in your target-feeding stage).
Another approach is to pre-load the primary keys of the target table into a hashed file, and Insert or Update on separate links into the target table.
If the incoming records are timestamped with the most recent change, you can SELECT these based on the change time being more recent than the previous run of your DataStage job.
There are other solutions, these should get you thinking.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

devaraj4u · Post by **devaraj4u** » Thu Feb 20, 2003 10:49 pm

Dear Ray,

I am really Sorry Ray.I admit my mistake.I know that in this forum people are giving solutions before 24 hrs for the posted questions.

Here after i don't do that way.

Thanks & Regards,
K.S.RAJAN.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 21, 2003 12:17 am

That's OK, but please understand that it had to be said. If people contributing to this Forum think they're being pushed, they may be less willing to contribute.

jinmina · Post by **jinmina** » Mon Feb 24, 2003 12:34 pm

If source db is Oracle 9iR2, you may subscribe CDC.
In addition, I'm sure other RDBMS vendors provide the similar
CDC functionality.
Why don't you explore CDC options?

vmcburney · Post by **vmcburney** » Mon Feb 24, 2003 10:38 pm

I'm not familiar with that acronym, although I'm pretty sure you're not talking about the Canada Dairy Corporation. Could you give a brief explanation of what CDC is.

Vincent McBurney
Data Integration Services
www.intramatix.com

ray.wurlod · Post by **ray.wurlod** » Mon Feb 24, 2003 10:47 pm

CDC refers in this case to "Change Data Capture" (poor English, should really be "Changed Data Capture"), in which the rows that have changed since a given point in time can be identified by the database server. Notice now recent the release of Oracle to which Mina refers. CDC has been around in a number of guises for some time, but at considerable expense (whether money, disk space or performance).