Job Gets into a HANG State

[bapajju]

Hi All,
We are developing a Job that will take care of SCD Type-2. First let me explain how we are implementing the same.

1.Data Extracted from Source using Teradata Access Plug-In
2.We then look up the Target table and basing upon the constraint and look up we Update or Insert the Target table in two separate links through Teradata Access Plug-In.

..................|Look Up of Target Table|
.........................|
|Source|------>|Tran|---------->|Update Target|
..............................| ---------->|Insert Target|

When we have records for both Update and Insert the job gets into a Hanging State. Initially we tried the Look Up through Teradata Access Stage, but We tried putting the Look Up in HASH also and we get the same problem.

Please suggest.

Thanks in Advance

[rasi]

Instead of doing the update and Insert in the same job. Try outputting the target to a sequential file and then do the update following by insert job. This should rectify your problem.



Thanks
Rasi

[bapajju]

Thanks for the instruction, Rasi. But Can't we achieve this in a Single Job only.When we also do it in Two Jobs we get the perfect,but in a Single job it hangs. Could you please let us know why this happens and can't we achieve this in a single job only.

[rasi]

This happens basically becos of the locks in database. The best practise is to split the two stream (insert/update) separate and then do the inserts or updates. If you want this to happen in the same job then send the target to text file and then use the same text file and do your inserts and updates. This will work in the same job.

Cheers
Rasi

[bapajju]

Thanks Rasi.

I tried it again with Update and Insert in the single stage with two links and in the Update Link I have put transaction Size as 1. It now works fine.

Thanks for your suggestion