Teradata input stage locked by output stage

JNG · Post by **JNG** » Wed Oct 20, 2004 8:54 pm

I'm working with Teradata plug-in stages, but I think the question applies to all databases.

I have a job executing an SQL that joins table A & B, do necesssary transform, and produce an output to insert back into table B through a database load. My job was hung, and database trace shows the load step was blocked and eventually timed out. It was released if I terminate my SELECT session.

Code: Select all


        Teradata API stage  -->  TRANSFORM  --->  Teradata multiload stage
        (SELECT FROM A                                        (INSERT)
         WHERE NOT IN B)

Does Datastage starts the multiload stage before the SELECT stage complete? Is there any way to control the job so the load will not be initiated until the full SELECT is finished? Would changing to use Teradata fastexport stage to perform the SELECT help in this situation?

THanks.
Jennifer

chulett · Post by **chulett** » Wed Oct 20, 2004 9:16 pm

Assuming a job structure as you've noted, regardless of the Source database and target, the target process starts before the sourcing stage completes. A row from the source is pulled from the result set and processed through to the target before the next row is pulled from the result set and processed. Lather, rinse, repeat until complete.

If you want A to finish before B starts, you should break this up into two discrete steps. For example, landing the data to a flat file would allow the 'select' to finish before you would then pick up the flat file and load it. There may be other options specific to Teradata stages, but since I'm not familiar with it...

Hope that helps,

JNG · Post by **JNG** » Thu Oct 21, 2004 7:14 am

chulett wrote: If you want A to finish before B starts, you should break this up into two discrete steps. For example, landing the data to a flat file would allow the 'select' to finish before you would then pick up the flat file and load it. There may be other options specific to Teradata stages, but since I'm not familiar with it...

Thanks Craig. By 2 discrete steps, do you mean 2 jobs? or would adding a file stage within the same job (see below) do the work?

source stage --> transform --> seq file stage ---> multiload stage

Thanks.
Jn

KeithM · Post by **KeithM** » Thu Oct 21, 2004 7:33 am

You can accomplish this in one job exactly like you show in your example. Datastage will process from the source to the flat file and once that finishes it will process from the flat file to the target. You should get the results you're looking for and avoid the locking problem that you were having.

chulett · Post by **chulett** » Thu Oct 21, 2004 7:38 am

I specifically said 'steps' so that it would apply to either of the scenarios you mentioned - two jobs or turning your job into two 'sections'.

As Keith mentioned, draining the table to a flat file first (even in the same job) would avoid your locking problem on the target.

JNG · Post by **JNG** » Thu Oct 21, 2004 7:58 am

Thanks, Keith & Craig, for clarifying.

Jen