DSXchange

Hi,
I have scenario in which i need to develop a SCD type2.Here i have table in which colums are as follows:-
SGTKE(PK),PARTY_ID,SEG_ID,R_ID,MANAGER_NAME,START_DATE,END_DATE
1,1,111,2,A,2009-07-10,NULL
2,6,123,B,2009-07-10,NULL
3,5,078,C,009-07-10,NULL

Now i need to capture the new records basced on PARTY_ID,i.e For a given PARTY_ID(lets say 111),if any other columns changes like
SEG_ID=2
R_ID=4
MANAGER_NAME=E

then i want to capture this new record and my table will be like this:-

SGTKE(PK),PARTY_ID,SEG_ID,R_ID,MANAGER_NAME,START_DATE,END_DATE
1,1,111,2,A,2009-07-10,2009-07-16
2,6,123,B,2009-07-10,NULL
3,5,078,C,009-07-10,NULL
4,1,111,2,4,E,2009-07-16,NULL

for this i am first i am using Change Capture staeg .Here i m considering PARTY_ID as KEY column and change_mode=Explicit keys,all values assuming my values will be
SEG_ID,R_ID,MANAGER_NAME

IS my approch correct..? If i am wrong at any place pelase correct me.

thanks

Depends on the version I think.

If you use 7x, yeah, Change Capture should be the correct one as it allows you to compare new data with the old one.

Otherwise, if you're using the 8x version, you might think of using the SCD stage but only if you use surrogate keys.

algfr wrote:Depends on the version I think.

If you use 7x, yeah, Change Capture should be the correct one as it allows you to compare new data with the old one.

Otherwise, if you're using the 8x version, you might think of using the SCD stage but only if you use surrogate keys.

Yes my datastage version is 7x.In change capture i made copy and insert as TRUE,but my job is aborted

I am not familier with this stage......so can anyone explain,what is meaning of change mode and how the data is captured.???

Please go through the Parallel Job's Developer Guide

In other words what i want to do is:-

1. i want to perform a join on natural key column(PARTY_ID) and saperate the new records from old records.
2.assign the key for new records
3.update the old record with END_DATE of new records START_DATE.

How can this be captured using Change capture stage..??

.

Using Change capture you can achieve splitting your data into inserts, updates(edit), deletes and copy.

Once you have your different sets of data you can process them accordingly.

ddevdutt wrote:Using Change capture you can achieve splitting your data into inserts, updates(edit), deletes and copy.

Once you have your different sets of data you can process them accordingly.

what will be change mode in my case..???

I think you are talking about change code.
The default change code values for:
Inserts=1
Update(Edit)=3