Dead End

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 8:40 am

Hi all,

I am facing a strange issue. I designed a job and is a very normal job with a source and target and few lookups in it. Nostages other than DRS,hash files and IPCs are used in the job.

The job runs fine for some rows and then slowly dies out. What I mean is the job starts running with a rate of 150 rows / sec and then slowly goes to 1 row / sec and then when i see the performance stats the jobs looks like it has died. Does n't abort and doesn't have any warning messages in the log file.

Can any one help me in this . What may be the reason??

chulett · Post by **chulett** » Mon Sep 12, 2005 10:22 am

What stages are you writing to in the job? They would be a typical culprit in a situation like this, especially a hashed file that's not properly sized.

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 10:27 am

I am writing to a Target Table ( DRS ) and yes I am writing to a hash file as well.

So is that the problem ?? But why ?? what is the reason??

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 10:28 am

Oops Forgot to mention I am writing to a Shared container as well.

DeepakCorning wrote:I am writing to a Target Table ( DRS ) and yes I am writing to a hash file as well.

So is that the problem ?? But why ?? what is the reason??

rajkraj · Post by **rajkraj** » Mon Sep 12, 2005 10:34 am

I think problem is not with DS, problem might be with your target tables...it happens some times...when you are loading in to the tables some times tables are get locked. It might be because of your user access permission or your oracle server problem.

chulett · Post by **chulett** » Mon Sep 12, 2005 10:34 am

If you don't properly size a hashed file for the number of rows you will be sending it, it starts to 'overflow' and slows down. When it overflows badly, it starts slowing down badly as well - until the job seems to crawl or stand still.

One test would be to replace the hashed file with a Sequential stage and see if the writes stay steady that way.

To size the hashed file, find the sizes of the DATA.30 and OVER.30 in the hashed file directory, add them and then divide by 2048. Take that number and put at least that amount in as the Minimum Modulus of the hashed file.

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 11:36 am

I don't think so its a hash file load problem as I did a diff logic now and removed loading the hash file from the job . Still the job goes in to a stand still state although fetches lil more amount of data.

chulett · Post by **chulett** » Mon Sep 12, 2005 12:04 pm

Well, then you need to work through the other stages one by one until you find the culprit.

sun rays · Post by **sun rays** » Mon Sep 12, 2005 3:15 pm

I had a similar issue couple of times, the reason was there were some locks on the target table. once you are able to unlock them , then I guess you can get rid of the issue.

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 3:22 pm

hey sun rays,

Can you please give more explaination what do yo umean by Locks on tables????

sun rays wrote:I had a similar issue couple of times, the reason was there were some locks on the target table. once you are able to unlock them , then I guess you can get rid of the issue.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 12, 2005 4:25 pm

Every database uses locks of some kind to prevent data anomalies (for example lost data caused by two processes trying to change the same data at the same time).

This is a big topic. Ask your DBA to explain.

The DataStage process doing inserts or updates will request locks. If there is some other process in the database already holding those locks, then the DataStage process must wait.

A really bad DataStage design can actually lock itself out, for example by starting two processes trying to update the same table. (What's happening inside the shared container??)

DeepakCorning · Post by **DeepakCorning** » Mon Sep 12, 2005 4:29 pm

Shared container is selecting data from a table and writing to a HASH file.
Its selecting data from the table which I am writing to.

ray.wurlod wrote:Every database uses locks of some kind to prevent data anomalies (for example lost data caused by two processes trying to change the same data at the same time).

This is a big topic. Ask your DBA to explain.

The DataStage process doing inserts or updates will request locks. If there is some other process in the database already holding those locks, then the DataStage process must wait.

A really bad DataStage design can actually lock itself out, for example by starting two processes trying to update the same table. (What's happening inside the shared container??)

ray.wurlod · Post by **ray.wurlod** » Mon Sep 12, 2005 8:00 pm

So?

UniVerse (DataStage Engine) is a database, and hashed files are how that particular database implements its tables. Row-level locks are still taken.

If you populate it with a UV stage, the lock is promoted to table-level once a configurable threshold (MAXRLOCK) is reached.

DSXchange

Dead End

Dead End

Re: Dead End

Re: Dead End