Archives of the TeradataForum

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

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

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>>