Help needed for the below Logic

[e1994264]

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

[pandeesh]

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)?

[e1994264]

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

[arunpramanik]

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