DSXchange

HI
I am facing a problem. I am creating a job where I fetch the data from the database and doing some operation and than again insert into the database.The database I am using is oracle9i. The oracle database is present in some remote mechine. My job runs fine.But job fails when the database is down .It happens once in a month.So I want to know how to know that database is alive or down and if it is down than I can send a mail by saying that the database is not working on this date with time. So can any one help me in this rergard.

Regards
Niranjani

In a post-job subroutine you can read the Datastage job log from where you can get the reason for the job failure. If it is due to database down you can use DSSendMail function to send a mail notifying the database failure to the intended persons.

Wrap it in a Sequencer job. That would greatly simplify how you could send an email for any problems in your job.

If you specifically want to only email when the problem is a 'database down' type problem, one 'trick' is to first use a Job Activity stage that only does a Validate on the job. If it fails, you can pretty safely assume that the source/target database are unavailable and you should send your email. It the job validates Ok, go ahead and run it.

Hi
Thanks for a great reply. But the problem is how I know the database is down or it is alive from my job. As once if I know that than I can send the mail using the sendMail function in datastage. So the problem is to caputure whether the database is alive or down.
Thanks

Reread my post. If your first stage is a Job Activity stage set to 'Validate' your job, success or failure will tell you if the database is up or down. Have a 'Success' trigger go from there to another Job Activity stage that actually runs the job for real this time, and an 'Otherwise' trigger from the Validate stage to an Email Notification stage that sends out your 'database is down' email.

From the second Job Activity stage onward, you are in your normal Seqeuncer 'run and check success' mode. Continue on if a job runs well, stop and send the notification of the failure to Those Who Care if not.

Why not just create a job which selects count(*) from a valid_table and send it to a sequential output. If that job succeeds then your database is up and running. Otherwise send email or do whatever you wanted to.

Thanks
Siva

Just trying to point out that you don't need a special job to do it, that you can use your 'normal' first job in an 'extra' step at the beginning of your sequence.

Truth be told, I'm not sure why in the heck else they have the 'Validate only' option to the Job Activity stage. Anyone have any other uses for it?

Validate only is good immediately after deploying into production, as an extra check that DSNs, pathnames and passwords are all good.

Unfortunately, since this can't be a parameter, it means deploying other than in read-only mode. I have my own mechanisms (a routine that mimics the way Version Control asserts read-onlyness) for subsequently setting components to be read-only.