Check the database availability

[anupam]

Hi,

We need to check the database availabilty and then run the Jobs. The requriment is to check the database availability continuosly with 10 mins gap and as and when the database is up, run the subsequent Jobs.

We have written a routine in which we are using tnsping but unfortunately the Job calling the routine is always in hang stage even though the Database has become available.

And there is also a time limit untill which it should check continuously the availablility of the database. After that the routine should send a mail and come out.

Code: Select all

$IFNDEF JOBCONTROL.H 
$INCLUDE DSINCLUDE JOBCONTROL.H 
$ENDIF 

*- Accepting Input Parameters 
      DsnPStoCheck=PassDsnPStoCheck 
      DsnADCtoCheck=PassDsnADCtoCheck 
      TimeLimit=PassTimeLimit 
      MailRecipient=PassMailRecipient 
      MailSender=PassMailSender 
      MailServer=PassMailServer 

*- Converting timelimit from hrs to seconds 
*-      TimeLimitInSS = (TimeLimit*60) 
      TimeLimitInSS = (TimeLimit*1) 


      For TimeCounter = 0 To TimeLimitInSS Step 10 
         Call DSExecute("UNIX", 'tnsping ' : DsnPStoCheck : ' | grep OK | wc -l', Output, SystemReturnCode) 
         PSStatus = Trim(Output) 
         Call DSExecute("UNIX", 'tnsping ' : DsnADCtoCheck : ' | grep OK | wc -l', Output, SystemReturnCode) 
         ADCStatus = Trim(Output) 

         If (PSStatus[1,1]=1 And ADCStatus[1,1]=1) 
         Then 
            Call DSLogInfo("BCV Databases (PS and ADC) are UP for use", "TNSPingStatus") 
            ErrorCode = 0 
            Ans = "PS Status is ": PSStatus:" And ADC Status is ": ADCStatus 
            Exit 
         End 
         
         Sleep 10 
      Next TimeCounter 



         If (PSStatus[1,1]=0 And ADCStatus[1,1]=0) 
         Then 
            Alert = DSSendMail("From:":MailSender:"\n To:":MailRecipient:"\nSubject:TNSPing Status\n Server: " :MailServer: "\nBody: Please Check for the BCV Databases (ADC and PS) status")

            Call DSLogFatal("The Job aborted because the BCV database is not UP", "TNSPingStatus") 
         End 
         If (PSStatus[1,1]=0 And ADCStatus[1,1]=1) 
         Then 
            Alert = DSSendMail("From: " :MailSender: "\n To: " :MailRecipient: "\nSubject: TNSPing Status\n Server: " :MailServer: "\nBody: Please Check for the BCV Database (PeopleSoft) status")

            Call DSLogFatal("The Job aborted because the BCV database (PeopleSoft) is not UP", "TNSPingStatus") 
         End 
         If (PSStatus[1,1]=1 And ADCStatus[1,1]=0) 
         Then 
            Alert = DSSendMail("From: " :MailSender: "\n To: " :MailRecipient: "\nSubject: TNSPing Status\n Server: " :MailServer: "\nBody: Please Check for the BCV Database (ADC) status")

            Call DSLogFatal("The Job aborted because the BCV database (ADC) is not UP", "TNSPingStatus") 
         End 

         Ans = "PS Status is ": PSStatus:" And ADC Status is ": ADCStatus

In the code, there are 2 databases and availability should be checked gfor both of these and if both are up then subsequent jobs should be triggered.

[ray.wurlod]

Why not just create a job sequence (or job control code) that runs some trivial job in VALIDATE mode? The trivial job must, of course, access the database in question. Test its exit status to determine whether to proceed.

[anupam]

But i want it to check it every 10 minutes. Can it be done with the Job ?

[ray.wurlod]

Assuming version 7.5 you have StartLoop and EndLoop activities. Otherwise you can embed the loop in a job control routine, in much the way that the ClockMaster job control routine worked at a site where you worked earlier. Conceptually:

Code: Select all

Loop
   GoSub CheckWhetherShutdownRequested
While Not(ShutdownRequested)  
   GoSub DoAvailabilityCheck
   If DataBaseAvailable
   Then
      GoSub RunJobs
   End
   Sleep 600  ; * wait 10 minutes before checking again
Repeat

[chulett]

But i want it to check it every 10 minutes. Can it be done with the Job ?

Actually, you said you want to check it 'continuously'. And your routine is only sleeping 10 seconds between tries. Is that really what you meant to do?

[anupam]

Checking continuosly means checking the connection regularly with some interval. that interval can be of 5/10 minutes also as we need to check the availabilty for about 5 Hours.

The sleep in the routine is of 10 seconds for testing purpose only.