Failure Notification

klarson88 · Post by **klarson88** » Fri Jan 27, 2006 1:08 pm

We have a sequencer job that calls seven sequencer jobs (1 each for 7 different tables, each including extract, transform, load stages and some routine calls). Each of the seven sequencer jobs has notification set up for failures of any stage, and this works well. We want to send out notification from the outer stage to a pager if any of the seven inner sequences fail, but the inner sequencers show finished ok in most cases.

The condition that we are trying to avoid is a page for each inner job (which is run for 4 different locations, i.e. possible 28 pages if major problems occur). I have thought of a couple of methods to do this via a routine which may work, but wonder if there is a built-in method for identifying this.

chulett · Post by **chulett** » Fri Jan 27, 2006 1:30 pm

What exact version of DataStage?

klarson88 · Post by **klarson88** » Fri Jan 27, 2006 1:31 pm

chulett wrote:What exact version of DataStage?

DataStage Server version 7.5.1.A on AIX 5L.

chulett · Post by **chulett** » Fri Jan 27, 2006 1:57 pm

Then (in a nutshell) you should be able to leverage the checkpointing and the ability to 'Automatically handle activities that fail'. Between those two and the Exception Handler you should be fine.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 27, 2006 2:22 pm

Direct all failures to the UtilityWarningToLog routine as well as any other processing. This will propagate warnings "up the chain of control".

Your top-level controller can then detect that a warning has occurred.

Ideally create your jobs so that no other warnings occur.

klarson88 · Post by **klarson88** » Fri Jan 27, 2006 4:15 pm

All good suggestions. We had not used any jobs with Checkpointing, as most of the jobs originally were written for version 6 with job controls. I was able to get the major failures to generate warnings in the inner sequencers so that I can identify any jobs with status other than 1 (OK). Then I can use a conditional after all jobs have finished to call a notification stage if any status of the seven is not 1. With a bit of testing, that should work well enough.

klarson88 · Post by **klarson88** » Fri Jan 27, 2006 4:31 pm

ray.wurlod wrote:Direct all failures to the UtilityWarningToLog routine as well as any other processing. This will propagate warnings "up the chain of control".

Your top-level controller can then detect that a warning has occurred.

Ideally create your jobs so that no other warnings occur.

I will research this utility to see if I can make this work for me. Thanks for the suggestion.

Yes, I'd like to do this without warnings.

Unfortunately for me, the source systems for our jobs are scattered around the world (US and Canada in this case).

The likely portion of my jobs to fail is extracting them from the source, since WAN failures or remote system maintenance are always the bane of our DataStage jobs. Our target system is down less frequently because of availability requirements and is local so sees less network issues. I have eliminated many of the warning messages since I assumed most of these jobs and my immediate predicessor fixed some of them, but we average 1-2 outages a week no matter how good the error trapping in the jobs.