Custom alert code snippets

Use code snippets to customize alerts in IBM® Db2® Data Management Console.

Custom alerts are primarily designed to allow users to code customized alerts in the form of SQL, Shell Scripts, and Stored Procedures. These custom alerts return the values -1, -2, 0 corresponding to the severity levels - Warning, Critical, and Information.

The following snippets of code conform to the expected format of custom alerts and can be used to define custom alerts:

SQL Script

Generate an alert based on diagnostic records

In this example, the SQL script generates a critical alert if log records with level of 'E' (Error), 'C' (Critical), or 'S' (Severe) are found in the Db2 general diagnostic logs.

with critical_tab as (
SELECT COUNT(*) AS critical_count from table(pd_get_diag_hist('ALL','ALL','','2020-09-10-00.00.00.000000', '2020-09-11-00.00.00.000000',-2)) where level = 'E' or level = 'C' or level = 'S')
select case 
when critical_count > 0 then -2
else 0
end as critical_level
FROM 
    critical_tab
;

Generate an alert based on diagnostic records (WARNING)

In this example, the SQL script generates a warning alert if log records with level of 'W' (Warning) are found in the Db2 general diagnostic logs.

with 
warning_tab  as (select count(*) AS warning_count  from table(pd_get_diag_hist('ALL','ALL','','2020-09-10-00.00.00.000000', '2020-09-11-00.00.00.000000',-2)) where level = 'W')
select case 
when w.warning_count > 0 then -1
else 0
end as critical_level
FROM 
    warning_tab w
;

Generate an alert based on current number of sort overflows

In this example, the SQL script generates alerts based on the count of sorts that ran out of sort heap (sort overflows) and might required disk space for temporary storage. For custom alerts that are written in SQL script format, the return value of the first column is to determine the severity of the alert. Optionally, a second column can be used to return auxiliary information for the custom alert.

SQL script example with only severity column as output

SELECT
  CASE WHEN COUNT(SORT_OVERFLOWS) > 5 THEN -2
       WHEN COUNT(SORT_OVERFLOWS) > 0 THEN -1
  ELSE '0' END SORT_OVERFLOWS_LEVEL
FROM TABLE(MON_GET_ACTIVITY(NULL, -2));

The following is an example output of the alert. It has only one column, which is SORT_OVERFLOWS_LEVEL. The value in this column is used to determine the severity of the alert.

Database        : jasonrestrict      
Instance        : db2inst1           
Host name       : reno1.fyre.ibm.com 
Authorization id: admin1             
Run method      : SQL script         
Date            : Sep 7, 2022 3:45 PM
Status          : Success            



==============================================================================================================================================================================
SELECT
  CASE WHEN COUNT(SORT_OVERFLOWS) > 5 THEN -2
       WHEN COUNT(SORT_OVERFLOWS) > 0 THEN -1
  ELSE '0' END SORT_OVERFLOWS_LEVEL
FROM TABLE(MON_GET_ACTIVITY(NULL, -2))

Run time (seconds): 0.036
SORT_OVERFLOWS_LEVEL
--------------------
0

SQL script example with severity column and second column to return auxiliary information as output

In this example, the custom alert returns a second column to show how many concurrent sort overflows occurs when the alert is triggered.

SELECT
  CASE WHEN COUNT(SORT_OVERFLOWS) > 5 THEN -2
       WHEN COUNT(SORT_OVERFLOWS) > 0 THEN -1
  ELSE '0' END SORT_OVERFLOWS_LEVEL,
  'Number of current sort overflows: ' || COUNT(SORT_OVERFLOWS) as NUM_SORT_OVERFLOWS
FROM TABLE(MON_GET_ACTIVITY(NULL, -2));

Following is an example output of the alert. The SORT_OVERFLOWS_LEVEL column is used to determine the severity of the alert and the second column NUM_SORT_OVERFLOWS is used to display auxiliary information for number of sort overflows when the alert is triggered.

Database        : jasontpcds           
Instance        : db2inst1             
Host name       : dsmrepo1.fyre.ibm.com
Authorization id: admin1               
Run method      : SQL script           
Date            : Sep 7, 2022 3:57 PM  
Status          : Success              



==============================================================================================================================================================================
SELECT
  CASE WHEN COUNT(SORT_OVERFLOWS) > 5 THEN -2
       WHEN COUNT(SORT_OVERFLOWS) > 0 THEN -1
  ELSE '0' END SORT_OVERFLOWS_LEVEL,
  'Number of current sort overflows: ' || COUNT(SORT_OVERFLOWS) as NUM_SORT_OVERFLOWS
FROM TABLE(MON_GET_ACTIVITY(NULL, -2))

Run time (seconds): 0.030
SORT_OVERFLOWS_LEVEL NUM_SORT_OVERFLOWS                 
-------------------- -----------------------------------
-1                   Number of current sort overflows: 1

Shell script

Generate an alert if the amount of free space in memory is low

In this example, the shell script generates a warning alert if the amount of free memory space is less than 20 megabytes and generates a critical alert if the free memory space is less than 10 megabytes. For shell script, the exit code is used to determine the severity level and the script output is shown as second column in the result.

freemem=$(cat /proc/meminfo | grep SwapFree  | cut -d ":" -f 2 | awk '{print $1}')
echo freemem is $freemem
if [ $freemem -le 1000000 ]; then
  echo "critical"
  returnValue=-2
elif [ $freemem -le 2000000 ]; then
  echo "warning"
  returnValue=-1
else
  echo "OK"
  returnValue=0
fi
exit $returnValue

Generate an alert if the file system utilization is high

#!/bin/sh

# Percent usage thresholds that will flag a problem -- change as needed
myCriticalLimit=95
myWarningLimit=90

cumulativeSeverity=0

checkThresholdBreach()
{
    fsUsage=$1
    limit=$2
    severity=$3
    percentValue=`echo ${fsUsage} | cut -d% -f1`
    if [ ${percentValue} -ge ${limit} ]; then
        if [ ${cumulativeSeverity}  -gt ${severity} ]; then
            cumulativeSeverity=${severity}
        fi
        echo "Threshold  ${limit}% reached for ${fsUsage}. Severity: ${severity}"
    fi
}

checkFileSystemUsage()
{
    capacityOutput=`df -kP | grep -vE '^Filesystem|tmpfs|cdrom|/proc' | awk '{ print $5 "," $1 }' `
    for output in ${capacityOutput}
    do
        checkThresholdBreach ${output} ${myCriticalLimit} -2
        checkThresholdBreach ${output} ${myWarningLimit} -1
    done
}

checkFileSystemUsage
echo cumulativeSeverity = ${cumulativeSeverity}
exit $cumulativeSeverity

Check the trial license expiration date on a Db2 instance and generate an alert if the remaining trial days reach the threshold

. ~/.profile

daysLeft=0
rc=0

# Thresholds of days left for a Db2 trail license to flag an alert. Can be customized as needed
myCriticalLimit=3
myWarningLimit=7

calculateDaysLeft()
{
  ts_now=`date +%s`
  ts_expire=`date --date="$1" +%s`
  daysLeft=$((($ts_expire - $ts_now)/86400))
}

getLicenseInfo()
{
  expiryDate=`db2licm -l | grep 'Expiry date' | grep -v 'Permanent' | awk '{ print $3}' | tr -d '"'`
  for output in ${expiryDate}
    do
      calculateDaysLeft ${output}
  done
}

getLicenseInfo
echo daysLeft=$daysLeft
if [ ${daysLeft} -le ${myCriticalLimit} ]; then
   echo "Threshold  ${myCriticalLimit} days reached. Current days left is ${daysLeft}. Severity: Critical"
   rc=-2
fi
if [ ${daysLeft} -le ${myWarningLimit} ]; then
   echo "Threshold  ${myWarningLimit} days reached. Current days left is ${daysLeft}. Severity: Warning"
   rc=-1
fi
exit $rc

Stored procedure

In stored procedures, an OUT parameter must be included to return an exit code (-2,-1, or 0) to indicate the severity of the alert to be generated.

Example

OUT parameter + Return value

DROP PROCEDURE jsizto.db_underutilization_uda_out@

CREATE PROCEDURE jsizto.db_underutilization_uda_out(OUT severity INTEGER)
RESULT SETS 1
LANGUAGE SQL
BEGIN
   DECLARE dbsizets TIMESTAMP;
   DECLARE dbsize BIGINT;
   DECLARE dbcapacity BIGINT;
   DECLARE utilization FLOAT;

   /*10080 mins is 1 week */
   CALL GET_DBSIZE_INFO(dbsizets, dbsize, dbcapacity, 10080) ;

   SET utilization = (dbsize*100/dbcapacity);
   
   IF utilization > 90 THEN
      SET severity = -2;
   ELSEIF utilization > 80 THEN
      SET severity = -1;
   ELSE
      SET severity = 0;
   END IF;
   
   RETURN severity;
END
@
CALL jsizto.db_underutilization_uda_out(?)
@

DROP PROCEDURE UDATEST@

CREATE PROCEDURE UDATEST(IN name VARCHAR(20), OUT severity INTEGER)
LANGUAGE SQL
P1:BEGIN
     INSERT INTO tmpSchema.NamesTable VALUES(name);
     SET severity=-2;
END P1;

@ 
CALL UDATEST('DMC', ?)
@