Problem:

While developing the SCD2 data flow using the Teradata tables a lock happened between the updates and the reads of the Teradata target table and the job was hanging for long time.

SCD2

The goal of a slow changing dimension of type two is to keep the old versions of records and justinsert the new ones.

Solution:

If we follow the normal SCD2 conventional method explained above using Teradata tables the job will hang with dead lock. Because the TC is pointing to the same Teradata target, the incoming records are trying to compare with the Teradata target table and trying to insert/update in the same target table. Due to this comparison and the manipulation in same table, the Teradata issues a dead lock and the job hangs for long time.

Normal Flow:

Source Table—> Query –> TC–> HP –> KG –> Target Tab

Solution:

create a view over the target table using the locking row for access method as mentioned below.

CREATE VIEW TABLE_VW 

AS LOCKING ROW FOR ACCESS

SELECT * FROM TABLE  WHERE EFF_STAT = ‘A’;

Then use the view in  TC for comparing the target records. Now we have same records for comparing and manipulation but in different objects as a view in TC and as a table in target.

LOCKING ROW FOR ACCESS used in the view allows the dirty reads from the table and allows INSERT/UPDATE/DELETE operations on the table. Thus we can read the records from the same table as a view and INSERT/UPDATE/DELETE in the same table as table itself.

New Flow:

Source Table—> Query –> TC (View)–> HP –> KG –> Target Tab