We are extrating source from legacy system and loading into DB2target database.
Ex: one of the table structure of POSTAL ADDRESS TABLE in Db2 like following:
ID,ADDRESS_CD,ADDRESS1,ADDRESS2,CITY NAME,STATE,EFF_START_DT,EFF_END _DATE,VER_NUM
1,Mailing,xxx,xxx,TROY, MI,1/11/2012,2039-12-31,1
I am using CDC stage to caputure New recors(1) and Changed records(3).
Whenever the changed record comes as follows with City change or any change as follows
1,Mailing,xxx,xxx,AuburHILLS, MI,01/17/2012,2039-12-31
The output in the table should be like this
ID,ADDRESS_CD,ADDRESS1,ADDRESS2,CITY NAME,STATE,EFF_START_DT,EFF_END _DATE,VER_NUM
1,Mailing,xxx,xxx,TROY, MI,01/11/2012,01/17/2012,1
1,Mailing,xxx,xxx,AuburHILLS, MI,01/17/2012,2039-12-31,2
In the above example when a changed record with the change of city "AUburHills" comes and insert into the table.
The previos Record EFF_END_DATE need to be changed to current date(i.e 01/17/2012) and Version number should be increment by 1 of the record.
Please can anybody suggest it
Help needed for the below Logic
Moderators: chulett, rschirm, roy
Re: Help needed for the below Logic
Incremented by 1 with the recent version?e1994264 wrote:The previos Record EFF_END_DATE need to be changed to current date(i.e 01/17/2012) and Version number should be increment by 1 of the record.
Is there any limit for the maximum number of versions (or unlimited)?
pandeeswaran
Re: Help needed for the below Logic
There is no limit for the version Number. For new record it has version number=1.
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
Re: Help needed for the below Logic
You should look for the way to implement TYPE 2 SCD.