DESCRIPTION
This alert is for DB2 instances only and will alert you when the DB2 Bufferpool hit ratio drops below your chosen thresholds.
Adjust the thresholds to suit your environment and workload.
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return.
Configure the alert with values similar to these:
Alert Name: DB2 BUFFER POOL HIT RATIO
Execution Interval: 15 Minutes
Notification Text: The following bufferpools have a lower than expected hit-ratio. Investigation of the SQL running may be required.
SQL Statement:
WITH BP_METRICS AS (
SELECT DB_NAME, BP_NAME,
POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS AS TOTAL_LOGICAL_READS
,POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS AS TOTAL_PHYSICAL_READS
FROM TABLE (SNAP_GET_BP_V95(NULL, - 1))
AS METRICS)
SELECT DB_NAME||' - '||BP_NAME,
CASE WHEN TOTAL_LOGICAL_READS > 0
THEN DEC(((FLOAT(TOTAL_LOGICAL_READS) - FLOAT(TOTAL_PHYSICAL_READS))/FLOAT(TOTAL_LOGICAL_READS)) * 100,5,2)
ELSE NULL
END AS HIT_RATIO
FROM BP_METRICS
WHERE TOTAL_LOGICAL_READS > 0
;
Execute Against: Monitored Database
Description: BUFFER POOL HIT RATIO below 95%
Units: %
High Threshold: MIN 0, MAX 85
Medium Threshold: MIN 85, MAX 90
Low Threshold: MIN 90, MAX 95
Info Threshold: (leave blank)
Example alert message:
Alert: DB2 BUFFER POOL HIT RATIO
Database Instance: PROD:50000
Execution Time: Friday - October 17, 2014 09:08:48
View Alert Status: http://servername:8123/
Alert Parameters:
Description: BUFFER POOL HIT RATIO below 95%
The following bufferpools hav a sub-optimal hitratio. Investigation of the SQL running may be required.
Parameter: MYDBNAME - BP_4K
Value: 90.5