This alert should only be required if you do not have LOCKTIMEOUT value set in your database. IBM, in their infinite wisdom, allowed this to default to -1 (off) in the LUW environment.
Type :
Custom SQL Alert - Multiple Numeric Return
Description:
This alert is for DB2 instances only and will alert you when the DB2 Lockwaits > 30 seconds (or your chosen threshold) are detected.
Adjust the thresholds to suit your environment and workload.
Alert Name:
ServerName DatabaseName Lockwaits
Alert Parameters
SQL Statement:
WITH
MYDBNAME (DB_SCHEMA_TAB, DURATION) as
(
SELECT CHAR('MYDBNAME Table '||TRIM(COALESCE(TABSCHEMA, ' '))||'.'||TRIM(COALESCE(TABNAME, ' '))||' Lock Wait: ',60), CHAR(TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - LOCK_WAIT_START_TIME)))
FROM TABLE (SNAP_GET_LOCKWAIT('MYDBNAME', - 1)) AS X
WHERE TIMESTAMPDIFF(2,CHAR(CURRENT TIMESTAMP - LOCK_WAIT_START_TIME)) > 30
)
SELECT DB_SCHEMA_TAB, DURATION
FROM MYDBNAME
;
Execute Against: Monitored Database
Description: LOCKWAITS Detected.
Units: Seconds
Notification text:
LOCKWAITS Detected
Run db2 "call monreport.LOCKWAIT" to gather details.
Units: Seconds
Alert Levels and Notifications:
High Threshold: MIN 0, MAX 60
Medium Threshold: (leave blank) or set some warning thresholds
Low Threshold: (leave blank)
Info Threshold: (leave blank)
Example alert message:
Alert: MYSERVER MYDBNAME Lockwaits
Database Instance: DBINSTANCE
Execution Time: Thursday - May 26, 2016 05:12:25
View Alert Status: YourDPASERVER:8123/.../alertMain.iwc
Alert Parameters:
Description: LOCKWAITS Detected
LOCKWAITS Detected
Run db2 "call monreport.LOCKWAIT" to gather details.
Parameter: MYDBNAME Table SCHEMA.TABLENAME Lock Wait:
Value: 702 Seconds