DB2

This document describes the DB2 functionality of PandoraFMS discovery.

Introduction

The purpose of this plugin is to monitor DB2 databases, through queries that will extract information about data that is key to knowing the performance and status of the databases, such as the number of connections, cache, database size, percentage of log used and many more stats. These data will be reflected in PandoraFMS, in modules that will provide the statistical value, within an agent that will represent each database.

This plugin is developed to be used with Pandora FMS Discovery, so unlike other plugins it does not generate agents by XML, but rather everything discovered is returned in the JSON output of the plugin.

Prerrequisites

This plugin makes remote connections to the databases to be monitored, so it is necessary to ensure connectivity between the Pandora FMS server and said databases.

Parameters and configuration

Parameters

--conf Path to the configuration file.
--target_databases Path to the configuration file containing the database targets.
--target_agents Path to the configuration file containing the agent targets.
--custom_queries Path to the configuration file containing custom queries.


Configuration file (--conf)

agents_group_id: The ID of the group where agents will be created.
interval: The agent monitoring interval in seconds.
user: The connection username.
password: The password for the user.
threads: The number of threads to be used for agent creation.
modules_prefix: The prefix for module names.
execute_custom_queries: Activate with 1 to enable the use of custom queries.
analyze_connections: Activate with 1 to enable connection monitoring.
database_summary =  < Activate with 1 to enable database stats >
transactional_log =  < Activate with 1 to enable log stats >
db_size =  < Activate with 1 to enable database size stats >
cache_stats: Activate with 1 to enable cache statistics monitoring.

Example

agents_group_id = 10
interval = 300
user = sa 
password = HHgD85V@
threads = 1
modules_prefix = 
execute_custom_queries = 1
analyze_connections = 1
database_summary =  1
transactional_log =  1
db_size =  1
cache_stats = 1

List of target databases (--target_databases):

The content of the file should be a list of target databases, with each database separated by commas or lines. The format for a database can be any of the following:

ip/sid
ip:puerto/sid

If the port is not specified, the default TCP port 50000 will be used for connecting to the target.

Example:

172.17.0.2:50000/SAMPLE

Each line in the file should represent a separate target database using one of these formats. This file is used to specify the databases that the plugin should monitor.

List of target agents (--target_agents):

The content of the file should be a list of agent names, with each agent separated by commas or lines. These agent names will be used to associate the information from each target database with the corresponding specified agent name, instead of letting the plugin generate agent names automatically.

The position of each agent name in the list should match the position of the target database in its own list. In other words, the name for the first target database should be the first name in this list, considering that blank lines are ignored.

Example:

agente1,,agente3
agente4
agente5,agente6,agente7,,agente9

Custom queries (--custom_queries)

A module must be introduced for each custom query that is intended to be monitored. The modules must follow a structure, which is as follows:

check_begin --> Module opening label
name --> Module name
description --> Description of the module.
operation --> Operation type
datatype --> Module type
min_warning --> Configuration of the minimum warning threshold
max_warning --> Configuration of the maximum warning threshold
str_warning --> Warning string configuration
warning_inverse --> Activate the inverse interval with 1 for warning threshold
min_critical --> Setting the minimum critical threshold
max_critical --> Setting the maximum critical threshold
str_critical --> Critical string configuration
critical_inverse --> Activate inverse interval with 1 for critical threshold
module_interval --> This interval is calculated as a multiplier of the agent interval.
target --> Custom query
target_databases --> Database agents in which the module will be created
check_end --> Module closing tag

Example:

check_begin
name Select 1
description 
operation value
datatype generic_data
min_warning 5
target VALUES (1);
target_databases all
check_end

check_begin
name sace_controlators.total_registers
description Number of registros in sace_ontrolators table
operation value
datatype generic_data
min_warning 20
target SELECT COUNT(*) AS TotalRegistros FROM esquema.sace_controlators;
target_databases pandora
check_end

check_begin
name operators.max_value.sace_controlators
description max value in column operators
operation full
datatype generic_data
target SELECT MAX(operators) AS ValorMaximo FROM esquema.sace_controlators;
target_databases pandora
check_end

Manual execution

The plugin execution format is as follows:

./pandora_db2 \
--conf < path to configuration file > \
--target_databases < path to the configuration file containing the target databases > \
[ --target_agents < path to agent configuration file > ] \
[ --custom_queries < path to the configuration file containing the custom queries > ]

For example :

./pandora_db2 \
--conf /usr/share/pandora_server/util/plugin/db2.conf \
--target_databases /usr/share/pandora_server/util/plugin/targets.conf \
--target_agents /usr/share/pandora_server/util/plugin/target_agents.conf \
--custom_queries /usr/share/pandora_server/util/plugin/custom_queries.conf


Discovery

This plugin can be integrated with Pandora FMS Discovery.

To do this, you must load the ".disco" package that you can download from the Pandora FMS library:

https://pandorafms.com/library/ 

image-1687944701817.png

Once loaded, DB2 environments can be monitored by creating Discovery tasks from the Management > Discovery > Applications section.

image-1687944752646.png

For each task, the following minimum data will be requested:

image-1684831012628.png

Task settings can also be adjusted to customize the desired monitoring:

image-1684831055212.png

Tasks that are successfully completed will have an execution summary with the following information:

image-1684831099946.png

The tasks that are not completed successfully will have an execution summary recording the errors produced.

Agents and modules generated by the plugin

The plugin will create an agent for each target database. That agent will contain the following modules:

If database_summary is activated:

AGENT_WAIT_TIME_PERCENT Represents the percentage of time that DB2 agents are waiting for some activity, such as locks or external requests.
APP_RQSTS_COMPLETED_TOTAL Indicates the total number of application requests completed in the DB2 database.
AVG_RQST_CPU_TIME Represents the average CPU time used by each application request on the database.
CF_WAIT_TIME_PERCENT Shows the percentage of time that DB2 agents are waiting for shared resources on multi-node systems.
IO_WAIT_TIME_PERCENT Indicates the percentage of time that DB2 agents are waiting for input/output (I/O) operations.
LOCK_WAIT_TIME_PERCENT Represents the percentage of time that DB2 agents are waiting for resource locks.
NETWORK_WAIT_TIME_PERCENT Shows the percentage of time that DB2 agents are waiting for network operations.
RECLAIM_WAIT_TIME_PERCENT Indicates the percentage of time that DB2 agents are waiting for resource releases.
ROUTINE_TIME_RQST_PERCENT Represents the percentage of time used by stored routines (stored procedures, functions, etc.) in each application request.
RQST_WAIT_TIME_PERCENT Shows the percentage of time that application requests are waiting to be executed by DB2 agents.
TOTAL_BP_HIT_RATIO_PERCENT Indicates the percentage of hits in the buffer cache (buffer pool), which represents the efficiency in accessing data in memory.
TOTAL_BP_HIT_RATIO_PERCENT Represents the percentage of time used by transaction completion operations in each application request.

If transactional_log is activated:

Log utilization percent Percentage of log used in Kilobytes


If db_size is activated:

Database size Database size in Megabytes

If analyze_connections is activated:

Active connections Number of active connections

If cache_stats is activated:

cache hit ratio <buffer cache name of each row>  Cache hit ratio for each BP_NAME. By combining the cache hit ratio with "bp_name" (buffer cache name), you can evaluate the buffer cache performance for each of the existing caches in the DB2 database. You can monitor and compare the cache hit ratio for each buffer cache individually, allowing you to identify which buffer caches are operating most efficiently and which may require adjustments.

The plugin will also create a module for each custom query defined in the configuration file.