delta processing

[kashif007]

I NEED TO KNOW HOW DO I DO THIS

NEED TO DEVELOP A DELTA PROCESS THAT WILL DETERMINE THE DIFFRENCE BY COMPARING THE DATA FROM THE FILE RECIEVED FROM ONE TABLE WHAT IS CURRENTLY STORED IN ANOTHER TABLE. ALL THE ATTRIBUTES RECIEVED SHOULD BE COMPARED.

THE UNMATCHED RECORD SHOULD BE UPDATED OR UPSERTED INTO THE TABLE AND THE DATE ATRIBUTE IS SET TO SYSTEM DATE.

PLEASE I AM A IN A HURRY.
CAN ANYONE TELL ME HOW DO I DO IT.

[kris007]

First things First.

1. Post in the right forum. There is no way we would be able to tell which job you are trying to develop..server or parallel and what your target databases are etc etc. and the answer depends totally on it.

Give a nice detailed description of your Job and your source and target. I don't understand when you say data coming from a file received from one table. Is your source a table or a file?

[DSguru2B]

A couple of etiquettes that need to be kept in mind.
First of all you have posted in the wrong forum. We have absolutely no idea what job type it is, what version of datastage you are using and what is your OS type. Answer to your query will vary depending upon the above.
Secondly, please do not use all caps as it is a sign of shouting, as far as i remember.
Thirdly, please do not indicate, in your post, in any way, that your query is urgent, or you are in a hurry. No one does urgent here. This is a voluntary site and posters answer at their convenience.
I hope i did not sound rude, and conveyed my point gracefully.

Now as far as your query is concerned, you can used a hashed file lookup by declaring all columns as keys and detect the change, if your job is server type. If PX, then the CDC stage would be helpful. You can also load your data into a temp table and run sql to detect change.
Regards,

[kashif007]

sorry about that i told i was in a hurry

its a comparision between two tables. i am using a px environment and the source and target database is teradata.

[kris007]

Do you have a date column in your source data which explains about when that particular row was changed or created?
Is this date column being processed into your target database?
Or is the system date is the only date column available?

[DSguru2B]

Look into the CDC stage.

[kashif007]

i have two date four date column, it holds the business information. i have to modify two of them, one setting it to system date and one to null value. i can even use lookup also i guess. but i dont know whether it will work properly. the change capture does not read the data from the teradata EE.

[Daddy Doma]

the change capture does not read the data from the teradata EE.

What do you mean? Does it raise an error, do the rows stop, or are no changes identified?

[kumar_s]

There are ways to compare the current and previous data. ChanegCapture stage, or Join-Transformer Combination will help you.
But what is the requirement after getting the detail about, update and new.
Is it you want to update the existing record with the new records or you need to insert with an indicator saying it has been update. Basically what type of SCD you what to implement?

[Daddy Doma]

I'm more concerned about why the CDC cannot read the Teradata source. At it's basic level, shouldn't the job design be:

Code: Select all

               Teradata
               Existing
               Records
                  |
                  V
Teradata       Change        Teradata
New       -->   Data    -->   Insert/
Records        Capture        Update

(Of course, once the CDC is complete and records are tagged as Insert or Update they can be sent to load ready datasets, etc. And, the CDC stage can be replaced by a Join/Transformer combination as kumar_s has noted.)

[kashif007]

u r absolutely right doma thats what i need to do, although i tried to do so with cdc but i guess the metadata is not matching.

so what i plan to do is get each teradata into a lookup and then i can compare in a transformer and do inserts and update. i still didnt do it. but thats what i planned.

I'm more concerned about why the CDC cannot read the Teradata source. At it's basic level, shouldn't the job design be:

Code: Select all

               Teradata
               Existing
               Records
                  |
                  V
Teradata       Change        Teradata
New       -->   Data    -->   Insert/
Records        Capture        Update

(Of course, once the CDC is complete and records are tagged as Insert or Update they can be sent to load ready datasets, etc. And, the CDC stage can be replaced by a Join/Transformer combination as kumar_s has noted.)

[kumar_s]

Make sure your Data fits into memory. Else you may agian stik to Join.