Capturing delta

[phc9741]

Hello, could anyone provide me with recommendations with the following problem.

I have two tables, parent and child, child table as a reference, writing into a third target table. One of the business logic I need to incorporate is to capture a new record from the child table and insert this record into the target table. However, the only way to determine a new child record is by the record's create timestamp.

Question 1. Has anyone done anything similar? If so, should I code this in the transform using SQL or write a routine?

Question 2. How did you design this routine?


Thank you in advance.

[kcbland]

If you have the means to detect deltas in the source system via an last update timestamp then by all means use it. It allows you to extract only the row necessary for processing, thus reducing network and pipeline traffic.

Your issue is if you need to pull the parent again. Since your delta detection is off the child, you either:

1. Join the parent and child table and pull a denormalized set based on the children; or
2. Pull the delta children as one dataset, and pull the parents using a where clause on the order of

Code: Select all

WHERE PARENT_ID IN (SELECT PARENT_ID FROM CHILDTABLE WHERE CHILDTABLE.LAST_UPDATE >= #YOURDATEVALUE#)

Both methods are valid. I don't recommend pulling over both tables to DataStage just to then eliminate rows.

Now the last trick is the date value to feed the query. You're going to need to track the last successful date used, so that you can pickup deltas from there. There's many ways to do that, from audit tables, process logs, whatever.