Needs to capture Inserts and Updates
Moderators: chulett, rschirm, roy
Needs to capture Inserts and Updates
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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