This plugin will check the bufferpool hit ratio between the last two calls. There is an article about how to analyze this value: http://angocadb2.blogspot.fr/2014/10/how-to-analyse-bufferpool-hit-ratio.html

Requisites

This script uses the MON_GET_BUFFERPOOL table function, and it is required to have the necessary right to call it. Please visit the KnowledgeCenter for more information.

db2 grant execute on function SYSPROC.MON_GET_BUFFERPOOL to user db2inst1
db2 grant execute on function SYSPROC.ENV_GET_PROD_INFO to user db2inst1
db2 grant select on table SYSIBMADM.ENV_INST_INFO to user db2inst1

OR

db2 grant dataaccess on database to user db2inst1

OR

db2 grant dbadm on database to user db2inst1

Depending on the given authorization, the user will need the CONNECTauthority:

db2 grant connect on database to user db2inst1

In order to active the previous table function, it is necessary to have the mon_obj_metrics configuration parameter at least in BASE. Please visit the KnowledgeCenter for more help.

db2 update db cfg using mon_obj_metrics base

Configuration

JNRPE

As the other plugins in db2-jnrpe, the Nagios configuration is done in the jnrpe.ini file, where the JNRPE server is installed. It is just necessary to add a line for each server in the commands section, with all parameters to connect to the database:

check_bufferpool_hit_local_sample : CHECK_BUFFER_POOL_HIT_RATIO -h localhost -p 50000 -d sample -u db2inst1 -P db2inst1

Nagios

Once the JNRPE configuration is done, it can be configured in Nagios, but before it can be tested with:

jcheck_nrpe -H localhost -c check_bufferpool_hit_local_sample  -n

If the plugin answer correctly, it can be configured in Nagios.

Usage

The first time the plugin is executed in a database, it will not return values but the UNKNOWN state because there is not a comparison point. The following call, it will return the right values.

When you use the interactive mode of JNRPE, you can see this behavior like this:

./jnrpe -c ../etc/jnrpe.ini -i
JNRPE> check_bufferpool_hit_local_sample
CHECK_BUFFER_POOL_HIT_RATIO : UNKNOWN - Values have not been gathered
JNRPE> check_bufferpool_hit_local_sample
CHECK_BUFFER_POOL_HIT_RATIO : OK - Bufferpool IBMDEFAULTBP at member 0 has 1830769 logical reads and 0 physical reads, with a hit ratio of 100,0%.|IBMDEFAULTBP=100,000000;90;95

Performance data

The bufferpool hit ratio reflects the performance of the database. Here, we can see a database that has many problems.

This is an example of the performance data:

Queries

These are the queries this script executes:

SELECT PROD_RELEASE FROM TABLE(SYSPROC.ENV_GET_PROD_INFO())

SELECT BP_NAME, POOL_DATA_L_READS + POOL_TEMP_DATA_L_READS
  + POOL_XDA_L_READS + POOL_TEMP_XDA_L_READS
  + POOL_INDEX_L_READS + POOL_TEMP_INDEX_L_READS AS LOGICAL_READS,
  POOL_DATA_P_READS + POOL_TEMP_DATA_P_READS + POOL_INDEX_P_READS
  + POOL_TEMP_INDEX_P_READS + POOL_XDA_P_READS
  + POOL_TEMP_XDA_P_READS AS PHYSICAL_READS, MEMBER 
FROM TABLE(MON_GET_BUFFERPOOL('', -2)) AS METRICS 
WHERE BP_NAME NOT LIKE 'IBMSYSTEMBP%'