db2Prom

Dynamic Prometheus exporter for IBM Db2 in Python

db2Prom is a Python-based tool designed for exporting metrics from IBM Db2 databases to Prometheus. It builds upon the foundation of db2dexpo by arapozojr, enhancing functionality and flexibility for monitoring Db2 instances.

Overview

db2Prom allows users to define their own SQL queries, execute them against one or more Db2 databases, and create Prometheus gauge metrics based on the results. This enables comprehensive monitoring at both the Db2 system level (e.g., buffer pool performance, hit ratio) and application-specific database metrics.

Key features include:

• Customizable Queries: Write SQL queries tailored to your monitoring needs.
• Dynamic Labeling: Define dynamic labels for metrics based on query results.
• Asynchronous Execution: Run multiple queries concurrently for optimal performance.
• Persistent Connections: Automatically reconnect to databases if the connection is dropped.
• Configuration via YAML: Easily configure which metrics to export and which databases to connect using YAML files.
• Row Limiting: Prevent runaway result sets by capping rows per query with an optional max_rows setting.
• Parameterized Queries: Safely pass parameters to SQL statements using bound placeholders.
• Label Sanitization: Clean DB-sourced label values so they contain only letters, numbers, and underscores. Any character outside [A-Za-z0-9_] (e.g., spaces, hyphens, slashes) is replaced with _, and values longer than 100 characters are trimmed.

Changes from db2dexpo

Enhanced Logging and Error Handling

• Improved logging messages throughout the application for better clarity and error reporting.
• Added comprehensive error handling mechanisms to gracefully manage exceptions and errors.

Extended Metrics Export

• Expanded the metrics export functionality to include additional database performance metrics beyond standard connections.
• Introduced flexibility in defining and exporting metrics based on user-defined queries and configurations.

Configuration Flexibility

• Enhanced configuration options via YAML files to provide more dynamic setup capabilities.
• Users can now easily define and customize database connections, queries, and metrics through configurable YAML files.

Bug Fixes and Optimization

• Optimized codebase for improved performance and reliability.

Documentation and Readme Updates

• Updated README.md to reflect changes, installation instructions, and usage guidelines specific to db2Prom.
• Added comprehensive examples and outputs to demonstrate usage scenarios, including Docker setup and metric visualization.

Grateful to arapozojr for their initial work on db2dexpo, which served as the foundation for this project.

Linux setup

To run db2Prom on Linux, follow these steps:

Prerequisites

• Python 3.10.8 or higher
• pip (Python package installer)

Installation

Clone the repository:

git clone https://github.com/Barryq93/db2Prom.git
cd db2Prom

Install all required packages using pip:

pip3 install -r requirements.txt

Check the example config YAML on how to handle multiple databases with different access. Each query can optionally include a max_rows field to cap the number of rows processed:

- name: "Employee count"
  time_interval: 15
  max_rows: 100
  query: |
    SELECT COUNT(*) FROM employees WHERE department = ?
  params:
    - "SALES"

The values listed under params are bound to the ? placeholders in the SQL statement.

Use this example YAML to also make your own config.yaml file, with your queries and gauge metrics.

Run the application:

python app.py config.yaml

The exporter listens on the port defined in global_config within config.yaml and binds to the machine's hostname. Prometheus servers on other machines can use that hostname to scrape the metrics endpoint.

Set DB2DEXPO_LOG_LEVEL to DEBUG to show query executions and metric updates.

Console logging can be disabled by setting log_console: false in the global_config section of config.yaml.

Example output of application startup:

2023-01-07 10:24:16,858 - db2dexpo.prometheus - INFO - [GAUGE] [db2_applications_count] created
2023-01-07 10:24:16,859 - db2dexpo.prometheus - INFO - [GAUGE] [db2_lockwaits_count] created
2023-01-07 10:24:16,859 - db2dexpo.prometheus - INFO - [GAUGE] [db2_lockwaits_maxwait_seconds] created
2023-01-07 10:24:16,859 - db2dexpo.prometheus - INFO - [GAUGE] [db2_employees_created] created
2023-01-07 10:24:16,860 - db2dexpo.prometheus - INFO - Db2DExpo server started at port 9877
2023-01-07 10:24:17,232 - db2dexpo.db2 - INFO - [127.0.0.1:50000/sample] connected

You can then open http://<exporter-host>:9877/ and see the exported metrics.

Ctrl+c will stop the application.

Building a Linux executable

To bundle the exporter into a standalone binary on Linux (e.g. RHEL), install PyInstaller and build the executable:

pip install pyinstaller
pyinstaller --onefile --name db2Prom app.py

The resulting binary is placed in dist/db2Prom and can be run with:

./db2Prom config.yaml

Windows setup

The exporter also runs on Windows. The steps are similar:

1. Install Python 3.10.8 or later and ensure pip is available.

2. Install required packages:

   pip install -r requirements.txt

3. Run the exporter and pass your configuration file:

   python app.py config.yaml

Building a Windows executable

You can bundle the exporter into a standalone .exe using PyInstaller:

pip install pyinstaller
pyinstaller --onefile --name db2Prom app.py

The executable is written to dist/db2Prom.exe and can be invoked with the configuration file argument:

db2Prom.exe config.yaml

Platform-specific signal handling

db2Prom registers handlers for SIGINT and SIGTERM using asyncio.loop.add_signal_handler for graceful shutdown. Some platforms, such as the default Windows event loop, do not implement this API. In these cases the exporter falls back to signal.signal and, if even that is unavailable, skips signal registration entirely. This means graceful shutdown may not be possible on every platform.

Running in Docker

Clone this repo:

git clone https://github.com/arapozojr/db2dexpo.git
cd db2dexpo/

Check the example config YAML on how to handle multiple databases with different access. Use this example YAML to also make your own config.yaml file with custom queries, metrics, and optional max_rows limits.

Build Docker image:

docker build -t db2prompy .

Run a container:

docker run --name db2dexpo -it --env-file .env db2dexpo

See the exported metrics:

docker exec -it db2dexpo curl 127.0.0.1:9877

Example output:

...
# HELP db2_applications_count Amount of applications connected and their states
# TYPE db2_applications_count gauge
db2_applications_count{appname="myapp",appstate="UOWWAIT",db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test"} 5.0
db2_applications_count{appname="myapp",appstate="UOWEXEC",db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test"} 2.0
db2_applications_count{appname="db2bp",appstate="UOWEXEC",db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test"} 1.0
# HELP db2_lockwaits_count Amount of lockwaits
# TYPE db2_lockwaits_count gauge
db2_lockwaits_count{db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test"} 0.0
# HELP db2_lockwaits_maxwait_seconds Maximum number of seconds apps are waiting to get lock
# TYPE db2_lockwaits_maxwait_seconds gauge
db2_lockwaits_maxwait_seconds{db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test"} 0.0
# HELP db2_employees_created Number of employees
# TYPE db2_employees_created gauge
db2_employees_created{db2instance="db2inst1",dbhost="127.0.0.1",dbname="sample",dbport="50000",dbenv="test",persontype="employee"} 1442.0

Deployment considerations

The built-in HTTP server used by db2Prom binds to the machine's hostname and does not provide transport security or authentication. For production deployments, run the exporter behind a reverse proxy that handles TLS termination and optional authentication middleware (e.g., basic auth) to restrict access to the metrics endpoint.

GitHub releases with binaries

Automate building and publishing executables for both Linux and Windows by adding a GitHub Actions workflow, for example at .github/workflows/release.yml:

1. Trigger the workflow on tag creation.

2. Use a job matrix with ubuntu-latest and windows-latest runners.

3. Each job installs Python and project requirements, then runs:

   pyinstaller --onefile --name db2Prom app.py

4. Upload the resulting binaries with actions/upload-artifact.

5. Publish a release and attach the artifacts using an action such as softprops/action-gh-release.

After pushing a tag (e.g. git tag v1.0.0 && git push origin v1.0.0), the workflow builds both binaries and attaches them to the GitHub release page.