apply table locks in teradata through DataStage

meet_deb85 · Post by **meet_deb85** » Mon Sep 26, 2011 11:32 pm

We are loading multiple Teradata tables through a single job.
While loading if the job fails then all the records which were inserted in the other tables in the same run should be rolled back. .i.e either all the records should be inserted in all the tables or no records should be inserted in any of the table at all.
Please suggest which stages to be used in DataStage Server job to achieve the same

ray.wurlod · Post by **ray.wurlod** » Tue Sep 27, 2011 1:17 am

Distributed Transaction Stage, once they enable it for use with Teradata.

Until then it has to be "roll your own" back!

meet_deb85 · Post by **meet_deb85** » Tue Sep 27, 2011 1:38 am

Hi Ray,

Can't we use Teradata Multiload Stage and using 1 stage for 1 table within my job. I mean to say if there are 3 tables to be populated using the same job, I will use 3 stages within the job.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 27, 2011 4:46 am

That's fine, but it does not give you any rollback capabilities on the set of tables. You have to design another job to "undo" committed transactions on the other tables.