agents/plugins/mk_db2.linux

   1 #!/bin/bash
   2 # +------------------------------------------------------------------+
   3 # |             ____ _               _        __  __ _  __           |
   4 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   5 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   6 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   7 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   8 # |                                                                  |
   9 # | Copyright Mathias Kettner 2018             mk@mathias-kettner.de |
  10 # +------------------------------------------------------------------+
  11 #
  12 # This file is part of Check_MK.
  13 # The official homepage is at http://mathias-kettner.de/check_mk.
  14 #
  15 # check_mk is free software;  you can redistribute it and/or modify it
  16 # under the  terms of the  GNU General Public License  as published by
  17 # the Free Software Foundation in version 2.  check_mk is  distributed
  18 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  19 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  20 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  21 # tails. You should have  received  a copy of the  GNU  General Public
  22 # License along with GNU Make; see the file  COPYING.  If  not,  write
  23 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  24 # Boston, MA 02110-1301 USA.
  25
  26 # Agent plugin to monitor DB/2 databases on Linux.
  27 #
  28 # Note: The script has to be accessible and executable by the DB/2
  29 #       instance users to function properly.
  30 #
  31 # The script is called in two different modes. Without arguments it:
  32 #
  33 #    - outputs the db2_version section,
  34 #    - collects all db2 instances and databases, and
  35 #    - runs the script for each database as an instance user
  36 #
  37 # With arguments the script queries the information for each database and
  38 # outputs the corresponding sections.
  39
  40 if [ $# -eq 0 ]; then
  41     if type timeout >/dev/null 2>&1 ; then
  42         function waitmax () {
  43             timeout "$@"
  44         }
  45     fi
  46
  47     INSTANCES=$(ps -ef | grep "[d]b2sysc" | awk '{print $1 }')
  48
  49     for INSTANCE in $INSTANCES; do
  50         NOW=$(perl -e "print time();")
  51
  52         echo ""
  53         echo '<<<db2_version:sep(1)>>>'
  54         DBVERSION=$(su - "${INSTANCE}" -c "db2 get snapshot for dbm" | grep -e 'Product name' -e 'Service level' | awk -v FS='=' '{print $2}' | sed 'N;s/\n/,/g' | sed 's/ //g')
  55         echo "$INSTANCE" "$DBVERSION"
  56         VERSION_NUMBER=$(echo "$DBVERSION" | sed  -e 's/DB2v\(.*\),.*/\1/' | awk -v FS="." '{print $1"."$2}')
  57
  58         DBS=$(su - "${INSTANCE}" -c "db2 list active databases" | grep 'Database name' | awk '{ print $NF }')
  59
  60         DB_PORT='port 0'
  61         GET_PORT=1
  62         for DB in $DBS; do
  63             if [ 1 -eq $GET_PORT ] ; then
  64                 # Each database in an instance has the same port information
  65                 db2_tcp_service=$(su - "${INSTANCE}" -c "db2 -x get dbm cfg" | grep "TCP/IP Service" | awk -v FS='=' '{print $2}' | tr -d ' ')
  66                 if ( grep "$db2_tcp_service" /etc/services | grep -q "^$db2_tcp_service " ); then
  67                     DB_PORT='port '$(grep "$db2_tcp_service" /etc/services | grep "^$db2_tcp_service " | awk '{print $2}' | awk -v FS="/" '{print $1}')
  68                 fi
  69                 GET_PORT=0
  70             fi
  71             SCRIPT=$(readlink -f "$0")
  72             waitmax -s 9 10 su - "${INSTANCE}" -c "\"${SCRIPT}\" \"${INSTANCE}\" \"${DB}\" \"${VERSION_NUMBER}\" \"${NOW}\" \"${DB_PORT}\""
  73         done
  74     done
  75 else
  76     INSTANCE=$1
  77     DB=$2
  78     VERSION_NUMBER=$3
  79     NOW=$4
  80     DB_PORT=$5
  81
  82     function compare_version_greater_equal {
  83         GREATER_ONE=$(echo "$1 $2" | awk "{if ($1 >= $2) print $1; else print $2}")
  84         if [ "$GREATER_ONE" == "$1" ] ; then
  85             return 0
  86         else
  87             return 1
  88         fi
  89         return 0
  90     }
  91
  92     millis_before=$(date +"%s%3N")
  93     if db2 +o connect to "$DB"; then
  94         millis_after=$(date +"%s%3N")
  95         millis_diff=$(( millis_after - millis_before ))
  96
  97         echo "<<<db2_connections>>>"
  98         echo "[[[$INSTANCE:$DB]]]"
  99         echo "$DB_PORT"
 100         echo "connections " | tr -d '\n'
 101         db2 -x "SELECT count(*)-1 FROM TABLE(mon_get_connection(CAST(NULL AS BIGINT), -2)) AS t"
 102         echo "latency ${millis_diff}"
 103
 104         echo "<<<db2_tablespaces>>>"
 105         echo "[[[$INSTANCE:$DB]]]"
 106         SQL="SELECT tbsp_name, tbsp_type, tbsp_state, tbsp_usable_size_kb, tbsp_total_size_kb, tbsp_used_size_kb, tbsp_free_size_kb FROM sysibmadm.tbsp_utilization WHERE tbsp_type = 'DMS' UNION ALL SELECT tu.tbsp_name, tu.tbsp_type, tu.tbsp_state, tu.tbsp_usable_size_kb, tu.tbsp_total_size_kb, tu.tbsp_used_size_kb, (cu.fs_total_size_kb - cu.fs_used_size_kb) AS tbsp_free_size_kb FROM sysibmadm.tbsp_utilization tu INNER JOIN ( SELECT tbsp_id, 1 AS fs_total_size_kb, 0 AS fs_used_size_kb FROM sysibmadm.container_utilization WHERE (fs_total_size_kb IS NULL OR fs_used_size_kb IS NULL) GROUP BY tbsp_id) cu ON (tu.tbsp_type = 'SMS' AND tu.tbsp_id = cu.tbsp_id) UNION ALL SELECT tu.tbsp_name, tu.tbsp_type, tu.tbsp_state, tu.tbsp_usable_size_kb, tu.tbsp_total_size_kb, tu.tbsp_used_size_kb, (cu.fs_total_size_kb - cu.fs_used_size_kb) AS tbsp_free_size_kb FROM sysibmadm.tbsp_utilization tu INNER JOIN ( SELECT tbsp_id, SUM(fs_total_size_kb) AS fs_total_size_kb, SUM(fs_used_size_kb) AS fs_used_size_kb FROM sysibmadm.container_utilization WHERE (fs_total_size_kb IS NOT NULL AND fs_used_size_kb IS NOT NULL) GROUP BY tbsp_id) cu ON (tu.tbsp_type = 'SMS' AND tu.tbsp_id = cu.tbsp_id)"
 107         db2 "${SQL}" | awk '{print $1" "$2" "$3" "$4" "$5" "$6" "$7}' | sed -e '/^[ ]*$/d' -e '/^-/d' -e '/selected/d'
 108
 109         echo "<<<db2_counters>>>"
 110         echo "TIMESTAMP $NOW"
 111         echo "$INSTANCE:$DB deadlocks " | tr -d '\n'
 112         db2 -x "SELECT deadlocks from sysibmadm.snapdb" | tr -d ' '
 113         echo "$INSTANCE:$DB lockwaits " | tr -d '\n'
 114         db2 -x "SELECT lock_waits from sysibmadm.snapdb" | tr -d ' '
 115         echo "$INSTANCE:$DB sortoverflows " | tr -d '\n'
 116         db2 -x "SELECT sort_overflows from sysibmadm.snapdb" | tr -d ' '
 117
 118         echo "<<<db2_logsizes>>>"
 119         echo "TIMESTAMP $NOW"
 120         echo "[[[$INSTANCE:$DB]]]"
 121         echo "usedspace " | tr -d '\n'
 122         db2 -x "SELECT total_log_used from sysibmadm.snapdb" | tr -d ' '
 123         db2 -x "SELECT NAME, VALUE FROM SYSIBMADM.DBCFG WHERE NAME IN ('logfilsiz','logprimary','logsecond')"| awk '{print $1" "$2}'
 124
 125         echo "<<<db2_bp_hitratios>>>"
 126         echo "[[[$INSTANCE:$DB]]]"
 127         db2 "SELECT SUBSTR(BP_NAME,1,14) AS BP_NAME, TOTAL_HIT_RATIO_PERCENT, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT, XDA_HIT_RATIO_PERCENT FROM SYSIBMADM.BP_HITRATIO" | grep -v "selected." | sed -e '/^$/d' -e '/^-/d'
 128
 129         echo "<<<db2_sort_overflow>>>"
 130         echo "[[[$INSTANCE:$DB]]]"
 131         db2 -x "get snapshot for database on $DB" | grep -e "^Total sorts" -e "^Sort overflows" | tr -d '='
 132
 133         echo "<<<db2_backup>>>"
 134         echo "[[[$INSTANCE:$DB]]]"
 135         if compare_version_greater_equal "$VERSION_NUMBER" 10.5; then
 136           # MON_GET_DATBASE(-2) gets information of all active members
 137           db2 -x "select LAST_BACKUP from TABLE (MON_GET_DATABASE(-2))" | grep -v "selected." | tail -n 1
 138         else
 139           db2 -x "select SQLM_ELM_LAST_BACKUP from table(SNAPSHOT_DATABASE( cast( null as VARCHAR(255)), cast(null as int))) as ref" | grep -v "selected." | tail -n 1
 140         fi
 141
 142         # disconnect from database
 143         db2 connect reset > /dev/null
 144     fi
 145 fi
 146
 147 exit 0