# DB2

# 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.

In turn the following permissions are required for the user used to connect.

Monitoring views (SYSIBMADM):  
SYSIBMADM.ENV\_INST\_INFO: SELECT.  
SYSIBMADM.MON\_DB\_SUMMARY: SELECT, MONITOR role  
SYSIBMADM.SYSIBMADM.MON\_TRANSACTION\_LOG\_UTILIZATION: SELECT, role MONITOR  
SYSIBMADM.ADMINTABINFO: SELECT, access to objects  
SYSIBMADM.MON\_CONNECTION\_SUMMARY: SELECT, role MONITOR  
SYSIBMADM.BP\_HITRATIO: SELECT, role MONITOR

# Parameters and configuration

**Parameters**

<table border="1" id="bkmrk---conf-ruta-al-archi"><tbody><tr><td>--conf</td><td>Path to the configuration file.</td></tr><tr><td>--target\_databases</td><td>Path to the configuration file containing the database targets.</td></tr><tr><td>--target\_agents</td><td>Path to the configuration file containing the agent targets.</td></tr><tr><td>--custom\_queries</td><td>Path to the configuration file containing custom queries.</td></tr></tbody></table>

 **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.
```

<span style="text-decoration: underline;">Example</span>

```
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
```

<span style="text-decoration: underline;">Example</span>:

```
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/](https://pandorafms.com/library/)

[![image-1687944701817.png](https://pandorafms.com/guides/public/uploads/images/gallery/2023-06/scaled-1680-/image-1687944701817.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2023-06/image-1687944701817.png)

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

[![image-1687944752646.png](https://pandorafms.com/guides/public/uploads/images/gallery/2023-06/scaled-1680-/image-1687944752646.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2023-06/image-1687944752646.png)

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

- DB2 target string: List of DB2 targets to be monitored by the task. It will be a list separated by commas or by lines. Each target database can be defined with the format IP:PORT/SID or IP/SID.
- User: Connection user to the target databases.
- Password: Password of the indicated user.

[![image-1684831012628.png](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/scaled-1680-/image-1684831012628.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/image-1684831012628.png)

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

- Max threads: To optimize the execution time, multiple threads can be configured to monitor the agents of the task. Keep in mind that setting up multiple threads can increase the CPU usage of the task.
- Target agent: List of target agents for the DB2 targets to monitor. That is, the names with which the agents of each objective defined in the task will be generated. It will be a list separated by commas or by lines. The position of the names in the list must match the position of the DB2 targets in your list, that is, the first name will be used for the first target and so on. If the list is separated by lines, blank lines will be ignored. If an agent name is not specified for a target, its IP or FQDN will be used as the agent name.
- Custom module prefix: Text included as a prefix for all generated module names. It is useful to locate the modules generated by the task or distinguish them from others.
- Get database summary: If activated, it will monitor the summary of the databases.
- Check transactional log utilization: If enabled, it will monitor the use of the transactional log.
- Get number of connections: If activated, it will monitor the connections.
- Check DB size: If activated, it will monitor the size of the databases.
- Retrieve cache statistics: If enabled, it will monitor cache statistics.
- Execute custom queries: If enabled, it will allow executing custom queries to each DB2 target.
- Custom queries: Configuration block to define the custom queries to be executed. Each query will generate a new module for each agent in the task.

[![image-1684831055212.png](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/scaled-1680-/image-1684831055212.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/image-1684831055212.png)

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

- Total agents: Total agents generated by the task.
- Targets up: Total number of targets to which it has been possible to connect.
- Targets down: Total number of targets to which it has not been possible to connect.

[![image-1684831099946.png](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/scaled-1680-/image-1684831099946.png)](https://pandorafms.com/guides/public/uploads/images/gallery/2023-05/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:

<table border="1" id="bkmrk-agent_wait_time_perc" style="border-collapse: collapse; width: 100%; height: 348px;"><tbody><tr style="height: 29px;"><td style="width: 50%; height: 29px;">AGENT\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Represents the percentage of time that DB2 agents are waiting for some activity, such as locks or external requests.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">APP\_RQSTS\_COMPLETED\_TOTAL</td><td style="width: 50%; height: 29px;">Indicates the total number of application requests completed in the DB2 database.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">AVG\_RQST\_CPU\_TIME</td><td style="width: 50%; height: 29px;">Represents the average CPU time used by each application request on the database.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">CF\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Shows the percentage of time that DB2 agents are waiting for shared resources on multi-node systems.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">IO\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Indicates the percentage of time that DB2 agents are waiting for input/output (I/O) operations.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">LOCK\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Represents the percentage of time that DB2 agents are waiting for resource locks.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">NETWORK\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Shows the percentage of time that DB2 agents are waiting for network operations.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">RECLAIM\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Indicates the percentage of time that DB2 agents are waiting for resource releases.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">ROUTINE\_TIME\_RQST\_PERCENT</td><td style="width: 50%; height: 29px;">Represents the percentage of time used by stored routines (stored procedures, functions, etc.) in each application request.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">RQST\_WAIT\_TIME\_PERCENT</td><td style="width: 50%; height: 29px;">Shows the percentage of time that application requests are waiting to be executed by DB2 agents.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">TOTAL\_BP\_HIT\_RATIO\_PERCENT</td><td style="width: 50%; height: 29px;">Indicates the percentage of hits in the buffer cache (buffer pool), which represents the efficiency in accessing data in memory.</td></tr><tr style="height: 29px;"><td style="width: 50%; height: 29px;">TOTAL\_BP\_HIT\_RATIO\_PERCENT</td><td style="width: 50%; height: 29px;">Represents the percentage of time used by transaction completion operations in each application request.</td></tr></tbody></table>

If **transactional\_log** is activated:

<table border="1" id="bkmrk-log-utilization-perc" style="border-collapse: collapse; width: 100%; height: 29px;"><tbody><tr style="height: 29px;"><td style="width: 50%; height: 29px;">Log utilization percent</td><td style="width: 50%; height: 29px;">Percentage of log used in Kilobytes</td></tr></tbody></table>

  
If **db\_size** is activated:

<table border="1" id="bkmrk-database-size-tama%C3%B1o" style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 50%;">Database size</td><td style="width: 50%;">Database size in Megabytes</td></tr></tbody></table>

If **analyze\_connections** is activated:

<table border="1" id="bkmrk-active-connections-n" style="border-collapse: collapse; width: 100%;"><tbody><tr><td style="width: 50%;">Active connections</td><td style="width: 50%;">Number of active connections</td></tr></tbody></table>

If **cache\_stats** is activated:

<table border="1" id="bkmrk-cache-hit-ratio-%3Cnom" style="border-collapse: collapse; width: 100%; height: 29px;"><tbody><tr style="height: 29px;"><td style="width: 50%; height: 29px;">cache hit ratio &lt;buffer cache name of each row&gt;</td><td style="width: 50%; height: 29px;"> 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.</td></tr></tbody></table>

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