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
SCD TYPE2 Implementation
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
I am not familier with this stage......so can anyone explain,what is meaning of change mode and how the data is captured.???
-
- Premium Member
- Posts: 132
- Joined: Tue Sep 04, 2007 11:38 am
- Location: NOIDA
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
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..??
.
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..??
.
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore