Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 14 Jan 2016 @ 21:23:58 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   OGG Replicates to Teradata - Dead Lock Issue
 
From:   Bodla, Sravan

Dear Forum,

Need your inputs to handle this:

We have an OGG source system which sends data to stage tables continuously (24*7). Its mapped from Oracle table to Teradata table directly.

Insert into Teradata.STG_Table1 . ? Write Lock

We have scheduled an Informatica workflow (Set of macro's - 1 macro for 1 stage table - with a Delete statement on the stage table to delete all records older than 3 days).

Due to conflicts of locks, the jobs are running into DEAD LOCK scenario.

Delete from Teradata.Stg_view1 .. DELTA_TIMESTAMP - INTERVAL '3' day AS DATE . (View1 with locking row for access on Table 1) -> Write Lock

Parallel Inserts (24*7) and Deletes (weekly twice) on same table? It's a design issue. But, do we have a way to handle this?

Its not possible to stop OGG replicates from source system/Teradata.

If I create a partition on DATE column on stage table and if use that column to drop data (Delete statement), will that help?

I mean, as partitioning deletion is much faster. But, what are the locks applied on this table (delete based on partition) ? (Table level - write lock?)

Any inputs will be highly appreciated.


Thanks,

Sravan.

Sravan Kumar Bodla
TERADATA DBA
TERADATA 12 DBA Certified
ITIL V3 Foundation Certified



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023