Hi All,
Our DataStage jobs that have running fine for over a month now have suddenly started hanging without returning any error. These are simple jobs using OCI9i Stage for the source and target and couple of transformer stages.
It does not seem to be an issue with the database as all projects using different databases are facing the same issue.
Are there any known issues with DataStage in this regard?
I have come across a suggestion in this forum to run the CLEAR.FILE &PH& command to resolve this issue. I have done that and executed the jobs again to see that they are running ok. But before executing the jobs again, I had to bounce my target database to close all the open datastageconnections(although the jobs were manually stopped/aborted) that were locking the target table.
But how do I make sure that the jobs do not hang again in today night's run?
And, what is the relation between the files in the &PH& directory and the jobs hanging?
Appreciate any help on this.
Thanks,
Raghu
Jobs hanging
Moderators: chulett, rschirm, roy
Hi,
The &PH& direcory contains files with run information of all job/stage runs.
It should be cleared on a regular basis (preferebly when no jobs are running or via a script deleting only old files.)
Hunged jobs are usually a rsult of a wait for something, like a table lock, resource lock and so on, that has no timeout limit.
Try monitoring what is going on if and when you get to this situation again and find out what is going on.
Having the deadock daemon up and running (read the DS docs for more details) might also help if you had any deadlocks occuring.
If you have multiple instance jobs, you might also want to check your log purge policy since the are all using hte same phisical log.
By the way if your DS server crashes you'll probably see jobs as running ,since no one updated their status, where in fact they are not running at all and you'll need to clear their status files (usually you should build a startup script that will fix things like that).
Last but not least:
I had a situation where another user's connection was locking DW tables and we were in the same hunged situation, I'm not sure if he was using some kind of 3rd party tool or not but he was locking the tables even when he was only reading from them so check all possible connections;
only after DBAs killed he's session our jobs continued running.
IHTH,
The &PH& direcory contains files with run information of all job/stage runs.
It should be cleared on a regular basis (preferebly when no jobs are running or via a script deleting only old files.)
Hunged jobs are usually a rsult of a wait for something, like a table lock, resource lock and so on, that has no timeout limit.
Try monitoring what is going on if and when you get to this situation again and find out what is going on.
Having the deadock daemon up and running (read the DS docs for more details) might also help if you had any deadlocks occuring.
If you have multiple instance jobs, you might also want to check your log purge policy since the are all using hte same phisical log.
By the way if your DS server crashes you'll probably see jobs as running ,since no one updated their status, where in fact they are not running at all and you'll need to clear their status files (usually you should build a startup script that will fix things like that).
Last but not least:
I had a situation where another user's connection was locking DW tables and we were in the same hunged situation, I'm not sure if he was using some kind of 3rd party tool or not but he was locking the tables even when he was only reading from them so check all possible connections;
only after DBAs killed he's session our jobs continued running.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org