Check the database availability
Posted: Thu Jul 14, 2005 12:23 am
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.
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.
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