DSXchange

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

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.

Incremented by 1 with the recent version?
Is there any limit for the maximum number of versions (or unlimited)?

There is no limit for the version Number. For new record it has version number=1.

You should look for the way to implement TYPE 2 SCD.