Design issue in datastage

[thanush9sep]

I am facing design issue in datastage

I have one source table ADDRESS and 3 target tables CUSTOMER, CONTACT and AUDIT tables.
customerID is the key column in source and target tables.

For each customerID in ADDRESS table, I have to update one record in CUSTOMER and to delete one record in CONTACT and insert one record in AUDIT table.
If any error occurs in delete or insert operation, all three operations should be rollbacked (UPDATE/DELETE/INSERT) for that record.
If all three operations completed successfully I have to continue with next customerID for ADDRESS table.
Meanwhile I have to update status column of ADDRESS table for each record('OK' if all 3 operations completed successfully or 'NOT OK' if any error).

I couldnt find straight forward design to take one record from source and track this record for 3 SQL operations on target tables at the same time rollback should be done in case of error.
could you please help me on this issue.
Thanks for your reply

[ray.wurlod]

The Distributed Transaction stage is probably what you're looking for. Or, if you're using a server job, the ODBC stage allows for a "grouped transaction".