Jobs Hung link status starting [resolved]

roy · Post by **roy** » Tue Aug 26, 2003 9:23 am

Hi All,
I have DS 6 on Win2K.
I run many jobs in parallel (each 15 minutes) reading form ORACLE 9i also on Win2K.

my "problem" is that after several full runs some jobs that read from the ORACLE are stuck in starting status but stay that way, till someone kils/stops/stumps/shoots/etc' [:o)] them.

to be more clear it is even some links and not the whole job !!!

anyone has any idea[?]

My real constraint is that the whole process runs every 15 minutes !!!
so it is not likely to "unparalel" the jobs.

Thanks In Advance,

Roy R.

=================

after applying the registry update ascential suggets, to provide processes with more resources the problem went away, so far so I hope it solved it.

datastagedummy · Post by **datastagedummy** » Tue Aug 26, 2003 2:53 pm

Are you doing Sorting or Agregation or Link Collector in this job ?

If yes try to stage the data into a temporary file before sending to the above mentioned stage.

I had the same problem with one of the jobs using Link Collector.

roy · Post by **roy** » Wed Aug 27, 2003 6:29 am

Hi,
No use of anything you mentioned.

all I have is several select with ORA9i in paralel,
which go to a transformer then seq files.

Has anyone enconterd this[?]

Thanks,

Roy R.

Teej · Post by **Teej** » Wed Aug 27, 2003 8:14 am

quote:Originally posted by roy
[br]all I have is several select with ORA9i in paralel,
which go to a transformer then seq files.

As far as I know, the Startup status clock ticks while Oracle is trying to figure out how to handle the queries.

Is your queries complex and pulls from tables with large set of data? If so, perhaps you need to index and/or analyze the tables used?

-T.J.

* * *

... now if this can make breakfast, my life is complete.

spracht · Post by **spracht** » Wed Aug 27, 2003 11:37 am

Roy

I see links with status 'Starting' when the data is aggregated within a DBMS. As soon as the first row is returned, the status changes to 'Running'. Don't know how to check in Oracle, if the sessions you are killing are still active? In Informix, for example, I could check, whether the query is attached to the DBMS and if it is still reading data.

Stephan

roy · Post by **roy** » Thu Aug 28, 2003 1:56 am

Hi,
First thanks for the replies[:)].

now what I have is several corelated subqueries with exists and not exists.

i.e. "select < columns >
from a,b
where < join Part >
and exists ( select c.val from c
where a.key = c.key )"

I have indexes and theese queries are quite quick to finish.

the problem, as I mentioned, is that after 20, or so, good runs of the entire process from start to finish I get the hunged links with status starting and they simply stay like that indefinitly[:(]

anyone had that[?],any sugestions[?]

Thanks in advance,

Roy R.

Teej · Post by **Teej** » Thu Aug 28, 2003 10:59 am

quote:Originally posted by roy
[br]the problem, as I mentioned, is that after 20, or so, good runs of the entire process from start to finish I get the hunged links with status starting and they simply stay like that indefinitly[:(]

Sure sounds like the job just died or...

1. Are you auto-purging old logs?
2. Is your &PH& directory jammed with junk? Try cleaning that directory out.
3. Make sure your Temp, Scratch, and Datasets folders are not too full.

Anyone else have any suggestions?

-T.J.

* * *

... now if this can make breakfast, my life is complete.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 28, 2003 4:29 pm

Once you reach this point where the DataStage job hangs, can you execute the SELECT statement from PL/SQL or TOAD? This will help to isolate the problem to Oracle or DataStage.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

degraciavg · Post by **degraciavg** » Thu Aug 28, 2003 8:14 pm

Hi Roy,

On the database side...

Did you check your table locks? Do you have other processes that update, insert or delete data from your tables on the 20th iteration of this job?

Since you mention that you run many jobs in parallel, I suggest you monitor the table locking in Oracle. You can monitor your sessions via the DBA Studio using an id with a DBA role. Otherwise, ask your DBA to monitor the sessions for you.

Note: if no data is passed on to your output link from a passive stage like OraOCI stage, then your process, most likely, is stuck in Oracle not in DataStage.

Hope this helps [:)]

regards,
vladimir

roy · Post by **roy** » Tue Sep 02, 2003 3:43 am

Hi All,
Thanks for the replies[:)].

Auto purge of logs is older then 3 days.
got over 150GB free disk space.
&PH& is ok.

I made the "magic" registry entry update Ascential suggests,
I hope this will work, to enlarge the process resources.

I'll Update this topic if and when I think the situation is resolved.

Thanks All,

Roy R.