Archives of the TeradataForum
Message Posted: Wed, 11 Aug 2010 @ 18:02:51 GMT
| Subj: | | Re: Pushdown Optimization and DD deadlocks. |
| |
| From: | | Robert Meunier |
Anomy.Anom wrote:
| | Flat files are picked up from source to be loaded into the staging area of Teradata. | |
| | Informatica feature 'Pushdown Optimization' is used for the view creations dynamically as coded in the ETL's (thousands in number). | |
| | Since view creations are DDL statements and these are many in number, this creates deadlocks on the DD tables (metadata in Teradata) and
these deadlocks are often resulting in ETL job failures. Stringent SLA's cannot absorb neither the load failures nor the time taken in restarting
the jobs without data corruption. | |
| | Recommended caveats like limiting the number of jobs, retrying after deadlock detection and creation of temporary views clash with the
SLA's. | |
I'm a little behind on reading this forum, so I'm a little late in replying to this post.
I ran into this issue at a client and did two things to at least minimize the occurrences of the deadlocking.
1) Minimize the number of views being created/dropped. You can do this through limiting the number of SQL overrides in your code. Every SQL
override caused the creation of a view. If I did the work of the SQL override within the mapping I was able to eliminate many of the views.
2) Use multiple userid's to execute your sessions. The hash for TVM is the userid creating the object and the database the object is created
in. If you have many userid's creating the objects you'll minimize the number of deadlocks you'll encounter.
Robert
| 
