| 1 | # IBM DB2 health alerts |
| 2 | |
| 3 | # Service health checks |
| 4 | template: db2_connection_failed |
| 5 | on: db2.service_health |
| 6 | class: Errors |
| 7 | type: Database |
| 8 | component: DB2 |
| 9 | calc: $connection |
| 10 | units: status |
| 11 | every: 10s |
| 12 | crit: $this == 0 |
| 13 | summary: DB2 connection check |
| 14 | info: Cannot connect to DB2 database |
| 15 | to: dba |
| 16 | |
| 17 | template: db2_database_critical_state |
| 18 | on: db2.service_health |
| 19 | class: Errors |
| 20 | type: Database |
| 21 | component: DB2 |
| 22 | calc: $database |
| 23 | units: status |
| 24 | every: 10s |
| 25 | warn: $this == 1 |
| 26 | crit: $this >= 2 |
| 27 | delay: down 5m multiplier 1.5 max 1h |
| 28 | summary: DB2 database state |
| 29 | info: DB2 database is in ${this == 1 ? "warning" : "critical"} state (quiesced or unknown) |
| 30 | to: dba |
| 31 | |
| 32 | # Connection usage |
| 33 | template: db2_connection_usage |
| 34 | on: db2.connections |
| 35 | class: Utilization |
| 36 | type: Database |
| 37 | component: DB2 |
| 38 | calc: ($max_allowed > 0) ? ($active * 100 / $max_allowed) : 0 |
| 39 | units: % |
| 40 | every: 10s |
| 41 | warn: $this > (($status >= $WARNING) ? (80) : (85)) |
| 42 | crit: $this > (($status == $CRITICAL) ? (90) : (95)) |
| 43 | delay: down 5m multiplier 1.5 max 1h |
| 44 | summary: DB2 connection usage |
| 45 | info: Active connections are at ${value}% of maximum on DB2 database |
| 46 | to: dba |
| 47 | |
| 48 | # Lock timeouts |
| 49 | template: db2_lock_timeout_rate |
| 50 | on: db2.locking |
| 51 | class: Errors |
| 52 | type: Database |
| 53 | component: DB2 |
| 54 | lookup: sum -1m unaligned of timeouts |
| 55 | units: timeouts |
| 56 | every: 10s |
| 57 | warn: $this > (($status >= $WARNING) ? (5) : (10)) |
| 58 | crit: $this > (($status == $CRITICAL) ? (20) : (30)) |
| 59 | delay: down 5m multiplier 1.5 max 1h |
| 60 | summary: DB2 lock timeouts |
| 61 | info: Lock timeouts in the last minute on DB2 database |
| 62 | to: dba |
| 63 | |
| 64 | # Deadlocks |
| 65 | template: db2_deadlock_rate |
| 66 | on: db2.deadlocks |
| 67 | class: Errors |
| 68 | type: Database |
| 69 | component: DB2 |
| 70 | lookup: sum -1m unaligned of deadlocks |
| 71 | units: deadlocks |
| 72 | every: 10s |
| 73 | warn: $this > 0 |
| 74 | crit: $this > 5 |
| 75 | delay: down 5m multiplier 1.5 max 1h |
| 76 | summary: DB2 deadlocks |
| 77 | info: Deadlocks detected in the last minute on DB2 database |
| 78 | to: dba |
| 79 | |
| 80 | # Buffer pool hit ratio |
| 81 | template: db2_bufferpool_hit_ratio_low |
| 82 | on: db2.bufferpool_hit_ratio |
| 83 | class: Performance |
| 84 | type: Database |
| 85 | component: DB2 |
| 86 | lookup: average -5m unaligned of hit_ratio |
| 87 | units: % |
| 88 | every: 10s |
| 89 | warn: $this < (($status >= $WARNING) ? (85) : (80)) |
| 90 | crit: $this < (($status == $CRITICAL) ? (75) : (70)) |
| 91 | delay: down 15m multiplier 1.5 max 1h |
| 92 | summary: DB2 buffer pool hit ratio |
| 93 | info: Buffer pool hit ratio is ${value}% on DB2 database (poor performance) |
| 94 | to: dba |
| 95 | |
| 96 | # Sort overflows |
| 97 | template: db2_sort_overflow_ratio |
| 98 | on: db2.sorting |
| 99 | class: Performance |
| 100 | type: Database |
| 101 | component: DB2 |
| 102 | calc: $overflows * 100 / ($sorts + $overflows) |
| 103 | units: % |
| 104 | every: 10s |
| 105 | warn: $this > (($status >= $WARNING) ? (3) : (5)) |
| 106 | crit: $this > (($status == $CRITICAL) ? (8) : (10)) |
| 107 | delay: down 15m multiplier 1.5 max 1h |
| 108 | summary: DB2 sort overflows |
| 109 | info: Sort overflow ratio is ${value}% on DB2 database |
| 110 | to: dba |
| 111 | |
| 112 | # Log space usage |
| 113 | template: db2_log_space_usage |
| 114 | on: db2.log_space |
| 115 | class: Utilization |
| 116 | type: Database |
| 117 | component: DB2 |
| 118 | calc: $used * 100 / ($used + $available) |
| 119 | units: % |
| 120 | every: 10s |
| 121 | warn: $this > (($status >= $WARNING) ? (70) : (75)) |
| 122 | crit: $this > (($status == $CRITICAL) ? (85) : (90)) |
| 123 | delay: down 5m multiplier 1.5 max 1h |
| 124 | summary: DB2 log space usage |
| 125 | info: Log space usage is ${value}% on DB2 database |
| 126 | to: dba |
| 127 | |
| 128 | # Log utilization percentage (new metric) |
| 129 | template: db2_log_utilization_high |
| 130 | on: db2.log_utilization |
| 131 | class: Utilization |
| 132 | type: Database |
| 133 | component: DB2 |
| 134 | calc: $utilization |
| 135 | units: % |
| 136 | every: 10s |
| 137 | warn: $this > 70 |
| 138 | crit: $this > 85 |
| 139 | delay: down 5m multiplier 1.5 max 1h |
| 140 | summary: DB2 log utilization |
| 141 | info: Transaction log utilization is ${value}% |
| 142 | to: dba |
| 143 | |
| 144 | # Lock wait time (new metric) |
| 145 | template: db2_lock_wait_time_high |
| 146 | on: db2.lock_wait_time |
| 147 | class: Latency |
| 148 | type: Database |
| 149 | component: DB2 |
| 150 | calc: $wait_time |
| 151 | units: milliseconds |
| 152 | every: 10s |
| 153 | warn: $this > 5000 |
| 154 | crit: $this > 10000 |
| 155 | delay: down 5m multiplier 1.5 max 1h |
| 156 | summary: DB2 average lock wait time |
| 157 | info: Average lock wait time is ${value} milliseconds |
| 158 | to: dba |
| 159 | |
| 160 | # Lock details (new metric) |
| 161 | template: db2_high_lock_contention |
| 162 | on: db2.lock_details |
| 163 | class: Workload |
| 164 | type: Database |
| 165 | component: DB2 |
| 166 | calc: $waiting_agents |
| 167 | units: agents |
| 168 | every: 10s |
| 169 | warn: $this > 10 |
| 170 | crit: $this > 20 |
| 171 | delay: down 5m multiplier 1.5 max 1h |
| 172 | summary: DB2 lock contention |
| 173 | info: ${value} agents are waiting for locks |
| 174 | to: dba |
| 175 | |
| 176 | # Buffer pool detailed hit ratios (new metrics) |
| 177 | template: db2_buffer_pool_data_hit_ratio_low |
| 178 | on: db2.bufferpool_detailed_hit_ratio |
| 179 | class: Utilization |
| 180 | type: Database |
| 181 | component: DB2 |
| 182 | calc: $data |
| 183 | units: % |
| 184 | every: 10s |
| 185 | warn: $this < 80 |
| 186 | crit: $this < 70 |
| 187 | delay: down 5m multiplier 1.5 max 1h |
| 188 | summary: DB2 buffer pool data hit ratio |
| 189 | info: Buffer pool data page hit ratio is ${value}% |
| 190 | to: silent |
| 191 | |
| 192 | template: db2_buffer_pool_index_hit_ratio_low |
| 193 | on: db2.bufferpool_detailed_hit_ratio |
| 194 | class: Utilization |
| 195 | type: Database |
| 196 | component: DB2 |
| 197 | calc: $index |
| 198 | units: % |
| 199 | every: 10s |
| 200 | warn: $this < 80 |
| 201 | crit: $this < 70 |
| 202 | delay: down 5m multiplier 1.5 max 1h |
| 203 | summary: DB2 buffer pool index hit ratio |
| 204 | info: Buffer pool index page hit ratio is ${value}% |
| 205 | to: silent |
| 206 | |
| 207 | # Per-tablespace usage (if tablespace metrics are enabled) |
| 208 | template: db2_tablespace_usage |
| 209 | on: db2.tablespace_usage |
| 210 | class: Utilization |
| 211 | type: Database |
| 212 | component: DB2 |
| 213 | lookup: average -1m unaligned of used |
| 214 | units: % |
| 215 | every: 10s |
| 216 | warn: $this > (($status >= $WARNING) ? (80) : (85)) |
| 217 | crit: $this > (($status == $CRITICAL) ? (90) : (95)) |
| 218 | delay: down 15m multiplier 1.5 max 1h |
| 219 | summary: DB2 tablespace ${label:tablespace} usage |
| 220 | info: Tablespace ${label:tablespace} usage is ${value}% on DB2 database |
| 221 | to: dba |
| 222 | |
| 223 | # Per-bufferpool hit ratio (if bufferpool metrics are enabled) |
| 224 | template: db2_bufferpool_instance_hit_ratio |
| 225 | on: db2.bufferpool_instance_hit_ratio |
| 226 | class: Performance |
| 227 | type: Database |
| 228 | component: DB2 |
| 229 | lookup: average -5m unaligned of overall |
| 230 | units: % |
| 231 | every: 10s |
| 232 | warn: $this < (($status >= $WARNING) ? (85) : (80)) |
| 233 | crit: $this < (($status == $CRITICAL) ? (75) : (70)) |
| 234 | delay: down 15m multiplier 1.5 max 1h |
| 235 | summary: DB2 buffer pool ${label:bufferpool} hit ratio |
| 236 | info: Buffer pool ${label:bufferpool} hit ratio is ${value}% on DB2 database |
| 237 | to: dba |
| 238 | |
| 239 | # Per-connection CPU usage alert removed - db2.connection_cpu_time context does not exist |
| 240 | # The connection instance metrics only include state, executing_queries, rows_read, rows_written, total_cpu_time |
| 241 | # but there's no chart context for per-connection CPU time monitoring |
| 242 | |
| 243 | # Long running queries |
| 244 | template: db2_long_running_queries |
| 245 | on: db2.long_running_queries |
| 246 | class: Performance |
| 247 | type: Database |
| 248 | component: DB2 |
| 249 | lookup: average -1m unaligned of critical |
| 250 | units: queries |
| 251 | every: 10s |
| 252 | warn: $this > 0 |
| 253 | crit: $this > 2 |
| 254 | delay: down 5m multiplier 1.5 max 1h |
| 255 | summary: DB2 long running queries |
| 256 | info: ${value} critical long running queries on DB2 database |
| 257 | to: dba |
| 258 | |
| 259 | # Backup age |
| 260 | template: db2_backup_age |
| 261 | on: db2.backup_age |
| 262 | class: Backup |
| 263 | type: Database |
| 264 | component: DB2 |
| 265 | lookup: average -1m unaligned of full |
| 266 | units: hours |
| 267 | every: 1h |
| 268 | warn: $this > (($status >= $WARNING) ? (24) : (36)) |
| 269 | crit: $this > (($status == $CRITICAL) ? (48) : (72)) |
| 270 | delay: down 1h multiplier 1.5 max 6h |
| 271 | summary: DB2 last full backup age |
| 272 | info: Last full backup was ${value} hours ago on DB2 database |
| 273 | to: dba |
| 274 | |
| 275 | # Backup status |
| 276 | template: db2_backup_status |
| 277 | on: db2.backup_status |
| 278 | class: Backup |
| 279 | type: Database |
| 280 | component: DB2 |
| 281 | lookup: max -1m unaligned of status |
| 282 | units: status |
| 283 | every: 1h |
| 284 | warn: $this > 0 |
| 285 | delay: down 1h multiplier 1.5 max 6h |
| 286 | summary: DB2 last backup failed |
| 287 | info: Last backup failed on DB2 database |
| 288 | to: dba |
| 289 | |
| 290 | # Table scan ratio (if index metrics are enabled) |
| 291 | template: db2_table_scan_ratio |
| 292 | on: db2.index_usage |
| 293 | class: Performance |
| 294 | type: Database |
| 295 | component: DB2 |
| 296 | calc: ($full * 100) / ($full + $index) |
| 297 | units: % |
| 298 | every: 1m |
| 299 | warn: $this > (($status >= $WARNING) ? (20) : (30)) |
| 300 | crit: $this > (($status == $CRITICAL) ? (40) : (50)) |
| 301 | delay: down 15m multiplier 1.5 max 1h |
| 302 | summary: DB2 index ${label:index} high full scan ratio |
| 303 | info: Index ${label:index} has a full scan ratio of ${value}%, which may indicate poor query performance. |
| 304 | to: dba |