We experienced a failure in our loads last night. We have a TPT that has a DELETE step and an INSERT step. The time between the DELETE steps firstResponse and the INSERT steps firstStep is 6.3 seconds. In that time a procedure ran that selected data from this empty table.
The INSERT consumes data from an OPERATOR that contains a SELECT from a stage table. I assume the 6.3 seconds was the time it took to read that stage table but I'm not 100% sure on that.
Is there anyway in a multistep TPT to lock a table that is being loaded via TPT until the TPT is complete?
Solved! Go to Solution.
Dieter, that is what I was hoping was possible. All of our TPT scripts are copy pasta so I wasn't sure if Multi-Statement would be supported. I will test this approach and implement to avoid the dreaded 6.3 second window from destroying other loads.
Now that I look at it, it's a DDL operator for the DELETE, but the INSERT is on an UPDATE operator selecting from an EXPORT operator. That seems a little silly. Surely that is more overhead then just an `INSERT... SELECT...` in a DDL operator, right?
There's no reason to use UPDATE, as the target table is empty a LOAD would be more efficient.
But when both source and target table are on the same Teradata box most efficient is a simple Insert/Select, of course.