Job hangs intermediately

pandeesh · Post by **pandeesh** » Sun May 01, 2011 8:23 pm

Hi,

We have a parallel job which reads the data from oracle table and loads into a sequential file.

The job was running fine for the past few years.

Now there are around 4 million records in the table.

Usually the job finishes within 1.5 hrs.

But for the past few days, the job processes the 3 million records within 40 minutes. After that it hangs and doesn't process any records.

We tried to stop the job manually and reran.

After that also the same thing is happening.

How to avoid this and make the job to complete fine?

How to resolve this?>

Please help me with your valuable suggestions.

Thanks

XRAY · Post by **XRAY** » Sun May 01, 2011 9:46 pm

Is there anything in the director log of the parallel job ?

pandeesh · Post by **pandeesh** » Sun May 01, 2011 10:09 pm

There is no clue in the director log.

I hope analyzing the particular table in oracle will improve the performance..

I am trying that ..

chulett · Post by **chulett** » Sun May 01, 2011 10:38 pm

A stop or 'hang' usually means a blocking lock, have your DBA trace the sessions. Another test - run on a single node.

srinivas.g · Post by **srinivas.g** » Mon May 02, 2011 1:45 am

Change the target sequential file to dataset and try it.
Also share your design

pandeesh · Post by **pandeesh** » Mon May 02, 2011 8:28 am

Hi,

My exact job design is as given below:

1)Oracle source stage

2)Lookup stage which having 2 sequential files and 2 lookup filesets for reference

3)copy stage

4)insert or update in target oracle table

Past few days back, the job was running fine.

There are around 4 billiion records in the source..

There are no issues upto 3 billion records.

After that the source query becomes inactive

The job is running on five node configuration.

Recently i have analyzed the target oracle table.

But that also din't help.

GJ_Stage · Post by **GJ_Stage** » Mon May 02, 2011 10:15 am

Hi,

Check if any indexes need to add or rebuild index if exists.

pandeesh · Post by **pandeesh** » Mon May 02, 2011 10:19 am

indexes in source table??

But in the recent run, it fetched all the records fromm the source and the source query session becomes inactive in oracle. so thats not a problem,,

This time the insert/update query in the target table is very very slow.
Previouly with more data from the source, teh same job finished within 1 hr.
But the job is running more than 6 hrs.

What could be the problem and how to avoid this?

thanks

pandeesh · Post by **pandeesh** » Mon May 02, 2011 10:45 am

One more thing is, in my job design the link from lookup output to copy stage and from copy stage tgt oracle stge..in those stages the records are processed @ 129 rows/sec.

Thats why it takes long time.

How to increase that rows/sec so taht the job ll be finished earlier?

Thanks

greggknight · Post by **greggknight** » Mon May 02, 2011 5:05 pm

what I have had happen in the past when a job that is inserting or updating is just sitting there is that my transaction log is full and cannot grow. After doing a TLOG backup the datastage job continued to process.

We set our tlog backups to run quite frequent when we are processing the DW so that we don't have these issues.

Just something else to look at.

jwiles · Post by **jwiles** » Mon May 02, 2011 5:16 pm

Try writing to a dataset rather than directly to oracle to see if the throughput changes. Then take the dataset and write it to oracle to see if you have throughput issues.

Is your quantity in the millions or billions of rows? You have mentioned both in this thread.

Has the ratio of inserts to updates changed recently? You should choose the appropriate upsert order to match the data you're processing. Update then Insert, or Inset then Update. Alternately, you could perform perform only inserts in this job while writing update records to a dataset, then run a second job to apply the updates.

Regards,