Job seems to be Hanging

dataservices job design
1

Hi,

 The DI job seems to be hanging and I have to kill it from admin console. The mapping contains 3 joiner transformations, a update logic and table comparison.

We are using DS XI3.1 (12.1.1.0).

Any suggestions will be helpful

Regards

ay1224 :us: (BOB member since 2007-07-30)

2

There could be multiple reasons like:

  1. Huge Resultant Set from your Join
  2. Are the tables coming from same Data Source? IF yes, check what part is pushed down to the Source?
  3. There could be some DB Issue as multiple unused sessions from previous executions still doing some operations on the tables you are working with.
  4. Indexing on the Target Table.
  5. Mode of Comparisio you are using in Table Comparision.
  6. Possible check the Join Order option also

SantoshNirmala :india: (BOB member since 2006-03-15)

3

Hi Santosh,

                All the sources are comming from the same

data source. can you please elobarate on your points. I could not get what you mean by pushing to source.

Regards

ay1224 :us: (BOB member since 2007-07-30)

4

It means that, on checking the Optimized SQL for the Dataflow has everything you are trying to do in the Query like group by, order by, any group functions like sum(), count() etc…

SantoshNirmala :india: (BOB member since 2006-03-15)

5

Hi Santosh,

             I am doing nothing all I have is outer joins in the joiner transforms and then a table comparison and key generation transforms. The job seems hanged after it executes 2 joiner transforms.

Regards

ay1224 :us: (BOB member since 2007-07-30)

6
  1. check with the DBA whether there is any activity is going on the DB by the user you are using.
  2. Possily get the Optimized SQL for the dataflow and check whether the tables are being indexed properly.
  3. What is the option you have selected in Table Comparision?

SantoshNirmala :india: (BOB member since 2006-03-15)

7

Hi Santosh,

                We are using Cashed Option. I have checked the tables for indexes and we do have them. It seems that the mapping is hanging at the joiner transform where it is joining two table having 800K of rows. Is there a setting at job server or at any place to overcome this problem.

Thanks,
Anjan Yenduri

ay1224 :us: (BOB member since 2007-07-30)