Trapping a connection failure and proceeding

RodBarnes · Post by **RodBarnes** » Wed Jun 01, 2005 4:08 pm

[I've searched but I think this is a topic that is difficult to locate with a search. It could be under any number of things.]

I have had to go to manually unscheduling the ETL when a weekend arrives where there is scheduled maintenance on a target DB. If I don't do this, the ETL will fail and abort -- forcing me to reset or recompile in order to run it again the next time.

I am looking for a way to build a job that could be included in the sequence so that, if the connection attempt failed on a source DB, I could trigger a path that would be considered a non-failure from the standpoint of the sequence. The idea is that I could leave the ETL scheduled all the time and, if it attempted the connection and failed, it would simply log the failure and just take a path that terminated the sequence. The sequence would consider itself to have completed successfully so the next scheduled time to run, it would just do so without having to be reset or recompiled.

Anyone have ideas? I'll keep working on this but just wondered if someone had already conquered this hill.

chulett · Post by **chulett** » Wed Jun 01, 2005 5:03 pm

How are you running these scheduled jobs? The concept of master sequencers jobs needing to 'reset or recompiled' manually before they can be run again can be handled by a wrapper script around the dsjob command.

As to your main question, I've never actually done this in Production but that is my understanding what the 'Validate only' option can be used for in the Job Activity stage. Build a simple job that contains a connection to the database is question - and I don't think it even needs to do anything other than connect. In other words, an OCI stage with no output links may be enough for Oracle. Have this job 'Validated' first in your job stream. If the validation fails, the database is inaccessable at that point and you can take you failure route. If it succeeds, process away.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 01, 2005 5:40 pm

I've done exactly what Craig suggested, though in a DIY job control routine (it was before the days of job sequences). The function call you require is DSRunJob(hJob, DSJ.RUNVALIDATE).

RodBarnes · Post by **RodBarnes** » Thu Jun 02, 2005 8:53 am

Ah, "validate". Sure, that makes sense. I hadn't used that yet but had read about it. I'll look into it, thanks.

The jobs are run from a single sequence. That sequence is invoked from master sequence that maintains our control information for the run.

chulett wrote:How are you running these scheduled jobs? The concept of master sequencers jobs needing to 'reset or recompiled' manually before they can be run again can be handled by a wrapper script around the dsjob command.

As to your main question, I've never actually done this in Production but that is my understanding what the 'Validate only' option can be used for in the Job Activity stage. Build a simple job that contains a connection to the database is question - and I don't think it even needs to do anything other than connect. In other words, an OCI stage with no output links may be enough for Oracle. Have this job 'Validated' first in your job stream. If the validation fails, the database is inaccessable at that point and you can take you failure route. If it succeeds, process away.