Problem in job running, design...

WoMaWil · Post by **WoMaWil** » Thu Feb 26, 2004 6:17 am

Hi woosy,

Problem 1: You need to find the source of this problem for to stop it. Perhaps you should shut down DS Server once a week.
Problem 2: This is a timeout problem when server hung. This item is already often discussed in this forum. Use the search function of the forum.

Wolfgang

vmcburney · Post by **vmcburney** » Thu Feb 26, 2004 11:10 am

You'll need to provide some more details about your hanging job problem. Is it the same job hanging each time? What stages are in the hanging jobs?

woosy · Post by **woosy** » Thu Feb 26, 2004 9:54 pm

>> about 'Job Hung'

The each stage of each Job hung irregularly..

Thanks!

kcbland · Post by **kcbland** » Thu Feb 26, 2004 10:14 pm

We need a lot more information. What kind of jobs are hanging? Are they having database connections? Can you describe the condition of hanging? Are you finding that the job is running, and never finishes? That the link statistics stop changing? That the underlying unix processes disappear? Are the jobs that "hang" doing any before/after stage/job routine calls?

woosy · Post by **woosy** » Fri Feb 27, 2004 1:23 am

>> Hanging Job in Server Job

The job has step (
1. starting point ->
2. environment variable setting ->
3. set nls ->
4. and hung..)

Original next step is calling oracle stored procedure.

I did not watch for the underlying unix processes at that time.

Thanks..

vmcburney · Post by **vmcburney** » Fri Feb 27, 2004 2:32 am

The oracle stored procedure is ringing some alarm bells with me. What if this stored procedure is stuck in an infinite loop? Have you been able to look at the active processes in the database during the hang? Really there is nothing else in the job you have described that could have caused it to hang other than that stored procedure.

kcbland · Post by **kcbland** » Fri Feb 27, 2004 6:48 am

How is the job designed? Is the stored procedure being called in an OCI stage, or are you talking about a Sequencer job that is executing a Command stage that executes a stored procedure? Are you sure that the stored procedure has not begun execution? Still highly confused as we do not have the necessary information to help you.

woosy · Post by **woosy** » Mon Mar 01, 2004 8:09 pm

vmcburney wrote:The oracle stored procedure is ringing some alarm bells with me. What if this stored procedure is stuck in an infinite loop? Have you been able to look at the active processes in the database during the hang? Really there is nothing else in the job you have described that could have caused it to hang other than that stored procedure.

The oracle stored procedure goes well most of the time and hasn't an infinite loop.
Before job' hang, step is calling procedure or stage of other type.
So, job' hang isn't confined to procedure.

Thanks!

woosy · Post by **woosy** » Mon Mar 01, 2004 8:16 pm

kcbland wrote:How is the job designed? Is the stored procedure being called in an OCI stage, or are you talking about a Sequencer job that is executing a Command stage that executes a stored procedure? Are you sure that the stored procedure has not begun execution? Still highly confused as we do not have the necessary information to help you.

The job is designed to calling a procedure in 'before SQL' of an OCI Stage.
The job didn't progress even call a procedure, just set envrinments variable and NLS.

Thanks!!

vmcburney · Post by **vmcburney** » Mon Mar 01, 2004 8:32 pm

The before-SQL of an OCI stage doesn't necessarily send a message to the log before it runs. It is likely that your before-SQL is being executed when the job hangs. You really need to run a database trace to find out whether the before-SQL procedure call has started and what it is doing.

kcbland · Post by **kcbland** » Mon Mar 01, 2004 9:19 pm

You might consider putting some event logging into your stored procedure to see what it is doing, how far it is getting, etc. I think between Vincent and I we would put money on a bet that your stored procedure is doing something for which you have not accounted. It's probably in la-la land, or busy doing something that never finishes and you are mistaking this for a DataStage issue. Try inserting audit messages into an audit table as your procedure progresses thru whatever steps it's doing.

woosy · Post by **woosy** » Thu Mar 04, 2004 12:09 am

Hi !

After the day..
the job doesn't cause an accident yet..

See you later~
Thanks!!