Check for DW connection before running the job sequences

kris007 · Post by **kris007** » Wed Mar 08, 2006 9:13 pm

Hi All,

I would like to develop a job which checks for the datawarehouse connection and sees if its up and running before it triggers my job sequences. And, if the datawarehouse in not available I would like this job to wait for some time before it checks for the connection again and then triggers my job sequences. One way I can think of is to execute a sql statement like
"select sysdate from dual"
and see if the DW is up or not. But I am scratching my head on how to make it wait(sleep) for some time before it tries to check the connection again. It would be great if anyone could suggest me some ideas or direction and also let me know if this is a good practise to do.

Thanks
Kris[/code]

Code: Select all

[/quote]

Code: Select all

rasi · Post by **rasi** » Wed Mar 08, 2006 9:36 pm

Kris

Seach on this forum for Wait for File Activity or poll. This has been discussed before

kris007 · Post by **kris007** » Thu Mar 09, 2006 3:20 pm

I did search on it, but my requirements here are quite different just waiting for file wont do,sorry for not being specific earlier. I would like to connect to Database for every min or so (loop activity) until i get the connection to oracle I want to keep tryin say like atleast 5 times or (few min).

P.S my server is Windows

ray.wurlod · Post by **ray.wurlod** » Thu Mar 09, 2006 5:07 pm

If you're on 7.5x2 you can use StartLoop and EndLoop activities in your job sequence.

kris007 · Post by **kris007** » Mon Mar 13, 2006 8:49 am

Ray,

I can use StartLoop and EndLoop.But, if the DW is down I need my job to wait for sometime and check for it again. For that, I am assuming I need to write a routine for a wait time. Am I thinking right. To be more clear, when the loop starts and if my DW is up the next jobs in the sequence run. But if my DW is down, then the job which checks for DW connection fails and the output should go to a routine or a function where it needs to wait for say like 10 min before starting the loop again. I didnt find any routine which can do this. Is there one or do we need to write our own routine. If so, can I get some guidance in writing the routine.

Thanks
Kris.

chulett · Post by **chulett** » Mon Mar 13, 2006 9:09 am

Use the Wait For File stage set to look for a non-existant file and then to give up after your desired 'sleep' time.

DSguru2B · Post by **DSguru2B** » Mon Mar 13, 2006 10:49 am

A job to check the connections can be made by the command you had mentioned

kris007 · Post by **kris007** » Mon Mar 13, 2006 1:20 pm

Thanks Craig. That works, but only if I dont use the ExceptionHandler. I am assuming that it works that way because I am handling the errors by giving a Failed trigger.

Kris.

chulett · Post by **chulett** » Mon Mar 13, 2006 1:30 pm

Yes, because the Wait For File stage has to 'fail' when it doesn't find a file it can trigger the Exception Handler. A couple of things you can do about that -

1) Try using either an 'Unconditional' trigger instead of a Failed one or two triggers, an Ok and an Otherwise so that it thinks you are handling any exceptions so it doesn't need to.

2) Replace the Wait For file with a Routine Activity that calls a custom Sleep X Seconds routine. Minimum code would be all of:

Code: Select all

sleep Arg1
Ans=0

Of course, you may want to make it... smarter... than that.

kris007 · Post by **kris007** » Mon Mar 13, 2006 5:02 pm

Thanks Craig. That helped.

Kris.