Introduction
To monitor the DB2 HADR Connect Status, this alert queries the hadr_connect_status column from the SNAPHADR snapshot table. The reason for the UNION in the query is that this table can be empty when HADR is in a down state, but the query needs to return ast least one row.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a custom alert of type Custom SQL Alert - Single Boolean Return. Do this twice, once for each SQL statement listed below and configure the alert with values similar to these:
Alert Name: HADR State
Execution Interval: 10 Minutes or less
Notification Text: <the message you want to convery with the email>
SQL Statement:
With temp1 as (
select case coalesce(hadr_connect_status, 'NULL')
when 'PEER' then 'FALSE'
else 'TRUE'
end case as hadr_connect_status
from SYSIBMADM.SNAPHADR
Union
Select 'TRUE' as hadr_connect_status
From sysibm.sysdummy1
)
Select hadr_state
From temp1
Fetch first 1 rows only
Execute Against: Monitored Instance
Units: N/A