How to abort datastage job for sybase deadlock issue?

josejohny · Post by **josejohny** » Wed Feb 10, 2010 11:15 pm

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

chulett · Post by **chulett** » Thu Feb 11, 2010 7:23 am

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.

kld05 · Post by **kld05** » Thu Feb 11, 2010 1:49 pm

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.

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 · Post by **chulett** » Thu Feb 11, 2010 3:17 pm

No, this option is specific to the stage so it does not affect any other aspect of the job.

kld05 · Post by **kld05** » Thu Feb 11, 2010 4:05 pm

chulett wrote:No, this option is specific to the stage so it does not affect any other aspect of the job.

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.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 11, 2010 4:09 pm

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 · Post by **kld05** » Thu Feb 11, 2010 6:47 pm

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.

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.

chulett · Post by **chulett** » Thu Feb 11, 2010 9:22 pm

kld05 wrote:
chulett wrote:No, this option is specific to the stage so it does not affect any other aspect of the job.
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.

Not sure how your testing relates to what I wrote about the option which is in response to your question "These settings apply to the code in the before and after routines, no?". All I noted was that the scope of the option the OP mentioned is the Sybase stage itself and thus has nothing to do with before/after routines or any other aspect of the job. Nor was I commenting on whether any particular error should be fatal or not, just making an observation on the fact that the option the OP asked about specifically tells the stage to treat any normally fatal error as non-fatal.

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.

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.