Table locks when loading through Teradata Enterprise stage

nishadkapadia · Post by **nishadkapadia** » Mon Jan 15, 2007 5:46 am

Adding a bit to the detailed ans by pratap to the Enterprise stage.The idea of aborting it from Datastage, however does not stop the Insert..Select statement from the ORCH<cookie_id> to the populated/empty table.

Only DBA can kill this particular session , however it is beset with performance issues on Teradata in terms of rollback/transient journals( incase of populated tables) and at times cannot kill the process depending on the mode of execution.

Sakthi_Sst · Post by **Sakthi_Sst** » Fri Jan 19, 2007 2:40 am

Hi Nishad,

Iam tryig to load with Teadata Multiload which is faster for my job structure compared to the other loading stages in Teradata.
Even to kill the session i need to know which sessions are running for my particular Multiload table.
I have a utility called PMON in Teradata through which i can see all the sessions running with the user id i have used to load the table, it also incluudes the other sessions used to load other tables other than my Multi load.

Regards,
Sakthi

ray.wurlod · Post by **ray.wurlod** » Fri Jan 19, 2007 5:29 am

Design it well; you should never need to kill anything in a well-designed DataStage job.

Sakthi_Sst · Post by **Sakthi_Sst** » Fri Jan 19, 2007 6:24 am

Ray ,

As of now i dont have a solution other than killing the teradata sessions to release the table immediately provided i could find the table which i am doing a Multiload.

If u have a beter solution plz update.

Regards,
Sakthi

ray.wurlod · Post by **ray.wurlod** » Fri Jan 19, 2007 3:57 pm

MultiLoad locks the table. This is documented. Cautious approach would involve executing a BTEQ script to ensure that the table is not already locked. And you can not select or read from the same table while MultiLoad is running. Your job design must handle all possible abnormalities in data without aborting, so that the job always finishes and the table lock can be released.