DSXchange

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.

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.

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.

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.

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

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.

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.

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

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

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.