Hi,
For below deadlock situation datastage job not got aborted but it send a warning only.
sybase stage is using and treat error as non fatal check box in
output->SQL->before and after is ticked. Is it due to this job not aborted.
Or Some setting problem in DB.
How to make datastage job abort in this condition?Please find log below...
Datastage Log:
Composite_View: Sybase Server warning 1205 (severity 13): Your server command (family id #0, process id #163) encountered a deadlock situation. Please re-run your
command.
Sybase Log:
Deadlock Id 1: Process (Familyid 0, Spid 235) was waiting for a 'exclusive page' lock on page 64844816 of the 'xxx'table in database 6 but
process (Familyid 0, Spid 163) already held a 'shared page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 163) was waiting for a 'shared page' lock on page 64846073 of the 'xxx' table in database 6 but pro
cess (Familyid 0, Spid 235) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 163) was chosen as the victim. End of deadlock information.
Thanks And Regards
Jose Johny
How to abort datastage job for sybase deadlock issue?
Moderators: chulett, rschirm, roy
These settings apply to the code in the before and after routines, no? I have the same question actually. It seems like a bug to me.chulett wrote:Sorry, but you enabled an option that says 'treat errors as non-fatal' and then you're wondering why your normally fatal error was logged as a warning?![]()
Yes, it is due to this that the job is not aborted. Uncheck that option.
Not according to our testing. We recreated the deadlock with the check boxes in both states and what should be a fatal error is a warning in both cases. What compounds the problem is that we cannot promote the warning to fatal in 7.5.1. We can only demote it. This is a bug.chulett wrote:No, this option is specific to the stage so it does not affect any other aspect of the job.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ray I'm referring to the warning being a bug not the promotion of a warning to fatal. Every other Sybase error is fatal. It makes no sense that a deadlock that was killed by a RDBMS would be a warning. We have a ticket into IBM and they have agreed it is an issue but have not provided a resolution.ray.wurlod wrote:No it's not. There never has been, and never will be, the possibility to promote warning to fatal. There are too many implications.
kld05 wrote:Not according to our testing. We recreated the deadlock with the check boxes in both states and what should be a fatal error is a warning in both cases.chulett wrote:No, this option is specific to the stage so it does not affect any other aspect of the job.
![Confused :?](./images/smilies/icon_confused.gif)
Now, not having access to DataStage lately means I can't build a job and open the stage to see for myself what options are where. I also didn't pick up the fact that the problem statement noted the options were checked in the 'Before SQL' and 'After SQL' tabs. Meaning they would not be affecting this issue at all because (I assume) both tabs are otherwise empty and thus doing nothing in the stage. I don't recall there being any option in the 'main' portion of the stage to do this. I'm also seeing now that you probably meant to say 'the before and after sql statements' rather than 'routines' which are before and after the job or stage. So went a little off target but still basically correct. Basically.
![Wink :wink:](./images/smilies/icon_wink.gif)
Can't do anything about the fact that the stage treats that fatal error as a warning but it's hardly alone in that behaviour. You'll find that with other stages and situations as well, unfortunately.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers