Oracle View to Text File

[meorz]

Hi, Thank you for all the supports so far.

I run a job to read from Oracle View and Extract to a Text File.
At the time I was running the Job, I have been experiencing a lot of Jobs getting "Abnormal Termination".

Anyway, I got a successful flag for this job, but it turns out it writes less rows than expected.

After that, we reboot the server to claim whatever resources unclaim and it runs Just Fine. (we did a few times issue UNLOCK ALL command using TELNET).

I checked the Oracle Log and Application Log and there's nothing indicates this problem.

Any pointers what caused this problem or what are the usual suspects?

Thanks

[ArndW]

Meorz,

does your director log show any additional information on the abnormal termination? Sometimes when you RESET a job you will get additional information on the cause for an abort. Does this job do anything in a transform stage?

One option you have is to clear out the contents of your project's &PH& directory and look at any files in there after your job aborts again.

Also, DataStage jobs and processes hold locks for a reason, going in as the administrator and doing a UNLOCK ALL will cause more damage than you think and can even cause job aborts!

[kcbland]

What probably happened is that the abnormal termination left a job process connected to Oracle. Whenever a job aborts and was talking to a database you MUST check to see if any zombie processes are still out there. The next time the job runs it might see that zombie process and confuse itself and doesn't actually run correctly. It will show as successful, but in reality it isn't.

As for querying an Oracle view versus a table, that doesn't matter. Your abnormal terminations are probably the result of other information you haven't shared, like what the rest of the job is doing. A simple OCI-->XFM-->SEQ job is very reliable. I suspect you have row buffer, IPC stages, other reference lookups, custom functions, etc going on and those are causing the job to blow up. Worst case, you're on Windoze, you might have run the server out of swap/memory.

[meorz]

Thank you all for the suggestions, really helpful.

Finally we found out that it really was a timing problem, 1st and 2nd job runs parallel, we accidentally missed out the line to wait for the 1st job to finish.

easy to fix, but real hard to track down.