Introduction
To monitor the DB2 HADR State, this alert queries the hadr_state 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_state, 'NULL')
when 'PEER' then 'FALSE'
else 'TRUE'
end case as hadr_state
from SYSIBMADM.SNAPHADR
Union
Select 'TRUE' as hadr_state
From sysibm.sysdummy1
)
select hadr_state
from temp1
fetch first 1 rows only
Execute Against: Monitored Instance
Units: N/A