Type 2 question

[marc_brown98]

I have a server job that checks for type 2 scd, if any of the fields have changed via crc32(all fields concatenated) I either update(expire) an existing record and write a new version of the expired record or it is a new record and is an insert only. Problem that I have is that sometimes the update existing/expire process does not complete before the job tries to insert the new dimension record and the insert fails because of a unique constraint on the natural key and the expire date. Any suggestions, short of splitting the server into 2?

Thanks
Marc

[ogmios]

If you land your data to a file e.g. you can never have this.

Ogmios

[rasi]

Split your type 2 data into two streams one for existing update and another one for new inserts. Send this as Sequential File and in the next job use the file to update the existing records first and then use the separate job to insert new records you will never have this problem.

Thanks
Rasi