Needs to capture Inserts and Updates

Souji · Post by **Souji** » Tue Aug 18, 2009 3:53 pm

Hi DataStage Guru,
This is my 1st post in this forum. I hope I will get answer from you guys.

I have a Requirement like this:-

SRC COL SOURCE COL DTYPE TGT COLNAME

NA NA CHANNEL_KEY
CHANNEL_ID INTEGER CHANNEL_ID
CHANNEL_DESC VARCHAR(20) CHANNEL_DESC
NA NA ROW_INSERT_TS
NA NA ROW_UPDATE_TS

TGT COL DATATYPE PK TRANSFORMATION RULE

INTEGER YES SK
VARCHAR (10) NO Trim Leading and Trailing space
VARCHAR(30) NO Trim Leading and Trailing space
TIMESTAMP NO SYSDATE when record inserted
TIMESTAMP NO SYSDATE when record updated

Those are the Source and Target column names

We are using DataStage 7.5 Server Edition

I need to capture If any inserts and updates to target table.

Can you guys give me better solution to solve this problem.

Please excuse if there are any grametical mistakes

Thanks
souji

ray.wurlod · Post by **ray.wurlod** » Tue Aug 18, 2009 4:59 pm

Preload a hashed file with at least keys from current records in target table, and use this to determine existence and, perhaps, change. In turn this will drive your insert/update decision. You can update the same hashed file from the same Transformer stage if your input stream is likely to contain duplicates.

Souji · Post by **Souji** » Tue Aug 18, 2009 5:06 pm

Ray,
Thanks for your reply. As I am not a premium member I am unable to see your post. I want to become a premium member probably this week or next for sure.

Ray, Can you please explain me in detail how to handle this situation to capture inserts and updates as above expained senorio.

More inputs are welcome

Once again I'm very thankful to Ray for your help

ray.wurlod · Post by **ray.wurlod** » Tue Aug 18, 2009 5:13 pm

I could, but that would also be a premium post. Until you get your premium membership why not Search DSXchange, where the technique has been discussed and fully described?

Souji · Post by **Souji** » Tue Aug 18, 2009 5:21 pm

Ray,
I did search on this topic but did not find exact solution.

The reason I'm asking to post step by step meaning I am planning to become a premium member this week.

I will check your post atleast after become premium member.

this is humble request and eagerly waiting for your reply.

I know you are the best in this forum.

Please excuse me if anybody hurt. Please take this post as a challenge and post yoru views here

chulett · Post by **chulett** » Tue Aug 18, 2009 5:33 pm

No need to challenge anyone, people will help when and if they can.

Souji · Post by **Souji** » Tue Aug 18, 2009 5:49 pm

Craig,
Please consider this post as request but not challenge. As you know I'm a new to this forum.

I know about you style of posting and never hurt anyone. Giving short and simple solutions to juniors.

Sorry if I did wrong in my post

chulett · Post by **chulett** » Tue Aug 18, 2009 9:52 pm

Once you get your membership, come back and check out a couple of other posts on the subject. The long and the short of it, Ken Bland style.

chulett · Post by **chulett** » Tue Aug 18, 2009 9:57 pm

Bottom line is you use the heart and soul of the Server product, a hashed file as a reference lookup. Build a list of your existing keys in a hashed file and then use it to decide insert (miss) versus update (hit). You can also refine update to mean 'only if changed' by including fields to compare (old v. new) in the hashed file.

laknar · Post by **laknar** » Thu Aug 20, 2009 2:46 am

first load the existing records into the hashed file.

next job.

refer the hashed file with source

have a variable like if Not(DSlink1.NOTFOUND) then 'Y' Else 'N' in the transformer stage.

have a constraint in transformer for 2 links For'Y'(UPDATE) and 'N' for(NEW)

design should look like below
hashedfile
|
|
source-----------trans-------UODATE
|
|
NEW