# 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**
--confPath to the configuration file.
--target\_databasesPath to the configuration file containing the database targets.
--target\_agentsPath to the configuration file containing the agent targets.
--custom\_queriesPath 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/](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 > Discovery > 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:
AGENT\_WAIT\_TIME\_PERCENTRepresents the percentage of time that DB2 agents are waiting for some activity, such as locks or external requests.
APP\_RQSTS\_COMPLETED\_TOTALIndicates the total number of application requests completed in the DB2 database.
AVG\_RQST\_CPU\_TIMERepresents the average CPU time used by each application request on the database.
CF\_WAIT\_TIME\_PERCENTShows the percentage of time that DB2 agents are waiting for shared resources on multi-node systems.
IO\_WAIT\_TIME\_PERCENTIndicates the percentage of time that DB2 agents are waiting for input/output (I/O) operations.
LOCK\_WAIT\_TIME\_PERCENTRepresents the percentage of time that DB2 agents are waiting for resource locks.
NETWORK\_WAIT\_TIME\_PERCENTShows the percentage of time that DB2 agents are waiting for network operations.
RECLAIM\_WAIT\_TIME\_PERCENTIndicates the percentage of time that DB2 agents are waiting for resource releases.
ROUTINE\_TIME\_RQST\_PERCENTRepresents the percentage of time used by stored routines (stored procedures, functions, etc.) in each application request.
RQST\_WAIT\_TIME\_PERCENTShows the percentage of time that application requests are waiting to be executed by DB2 agents.
TOTAL\_BP\_HIT\_RATIO\_PERCENTIndicates the percentage of hits in the buffer cache (buffer pool), which represents the efficiency in accessing data in memory.
TOTAL\_BP\_HIT\_RATIO\_PERCENTRepresents the percentage of time used by transaction completion operations in each application request.
If **transactional\_log** is activated:
Log utilization percentPercentage of log used in Kilobytes
If **db\_size** is activated:
Database sizeDatabase size in Megabytes
If **analyze\_connections** is activated:
Active connectionsNumber 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.