I've noticed inconsistencies in type 1 updates when both type 1 and type 2 changes occure in the same incoming row. When an incoming row only contains type 1 changes ALL versions of a logical key are updated. However, if the source has both type 1 and type 2 changes in the same incoming record, a new type 2 record is created, however the type 1 changes only occure in the newly created row.

You can reproduce this issue using the dmsurscd fex


A. Add the following filters on select columns icon to issolate on incoming row

SELECT DISTINCT
T1.EMPID , --Employee ID
T1.PLANT , --Store Name
T1.SALESREP --Plant Location
FROM
dmsale T1
WHERE
T1.EMPID = 'YY040' AND
T1.PLANT = 'ORL'

B. In the target hard code values for fname = 'Deb' ; lname = 'Smith'; plant = ORL

C. Run the initial load: Result below

1 YY040 Deb ORL FL 1

D. Test incoming row only has type 1 changes by changing fname = ted and rerun: Results below

1 YY040 Ted ORL FL 1

E. Test incoming row only has type 2 changes by changing plant=RSE and rerun: Results below

1 YY040 Ted ORL FL 0
2 YY040 Ted RSE FL 1

F. Test incoming row has both type 1 and type 2 changes by changing fname = Sue and plant = ZZZ: Results below

1 YY040 Ted ORL FL 0
2 YY040 Ted RSE FL 0
3 YY040 Sue ZZZ FL 1

G. Retest incoming row has only type 1 changes by changing fname=Lisa and rerun: Results below

1 YY040 Lisa ORL FL 0
2 YY040 Lisa RSE FL 0
3 YY040 Lisa ZZZ FL 1

Is this a bug. If not could someone explain why we would want/expect this difference in type 1 updates. I would expect type 1 changes to ALWAYS update all rows that match the incoming logical_key.

This message has been edited. Last edited by: <Kathryn Henning>,

It's arguable whether Type I changes should apply all rows with a matching natural key or just the current one. But I agree that presence of type Type II changes in the row shouldn't affect that one way of the other.

We've reproduced your scenario here and we are working to address it.

In fact, it would be great to be able to have an option to specify how you wanted your type one changes to behave. Update the active record only or 'punch through' and update all rows with a matching natural key.

Thanks for the looking into this issue and thanks for the update.

The issue you originally supported has been addressed and will available in the Release 7.7.05M Gen 147 which is now being certified and will be available for download soon.

Giving you the option of all or last is a good idea; you could open a new hottrack case as a new feature request to record your interest.