DB2 Tablespace High Water Mark Reduce Guard with Kestra

id: db2-tablespace-high-water-mark-reduce-guard
namespace: company.team

description: |
  Find DB2 DMS tablespaces that keep allocated-but-free pages below the high water mark, then give
  the space back to the filesystem. After a large delete or a dropped table DB2 releases the extents
  to pending-free, so `TBSP_USED_PAGES` falls while `TBSP_PAGE_TOP` stays where it was. Nothing
  shrinks on disk. This blueprint reads page counts from `MON_GET_TABLESPACE`, alerts Slack, then
  runs `ALTER TABLESPACE ... REDUCE` behind an approval `Pause`, one tablespace at a time. It reads
  the page counts again afterwards. The run fails if the high water mark did not come down.
  Read-only by default.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: JDBC URL
    defaults: "jdbc:db2://localhost:50000/testdb"

  - id: jdbc_username
    type: STRING
    displayName: Database user
    description: >-
      Needs SYSCTRL or SYSADM for the ALTER TABLESPACE step. The detection query
      only needs EXECUTE on MON_GET_TABLESPACE, so a read-only account is enough
      to run this flow in reporting mode.
    defaults: "db2inst1"

  - id: jdbc_password
    type: STRING
    displayName: Database password
    description: Defaults to the DB2_PASSWORD secret. Override per run only for
      local testing.
    defaults: "{{ secret('DB2_PASSWORD') }}"

  - id: name_pattern
    type: STRING
    displayName: Tablespace name pattern
    description: LIKE pattern matched against the tablespace name. Use it to scope a
      run to one storage group.
    defaults: "%"

  - id: exclude_pattern
    type: STRING
    displayName: Excluded name pattern
    description: >-
      LIKE pattern for tablespaces to leave alone. The default skips the catalog
      and the tool tablespaces, whose high water mark is rarely worth moving.
    defaults: "SYS%"

  - id: min_free_pages_below_hwm
    type: INT
    displayName: Minimum free pages below the high water mark
    description: A tablespace is only reported when at least this many allocated
      pages below the mark are free.
    defaults: 1000

  - id: min_free_pct_below_hwm
    type: INT
    displayName: Minimum free percentage below the high water mark
    description: >-
      Second threshold, applied as a percentage of the high water mark. Both
      thresholds have to be met, so a large tablespace with a small absolute gap
      is not reported.
    defaults: 20

  - id: reduce_clause
    type: STRING
    displayName: REDUCE clause
    description: >-
      What to pass to ALTER TABLESPACE ... REDUCE. The clause sizes how much of
      the container file goes back to the filesystem. MAX gives back everything
      above the new high water mark. A size (200 M) or a percentage (25 PERCENT)
      gives back only that much, which leaves the file large enough that the
      tablespace does not immediately re-extend. The high water mark comes down
      as far as the data allows either way.
    defaults: "MAX"

  - id: settle_duration
    type: DURATION
    displayName: Settle before the re-read
    description: Pause between the last REDUCE statement and the re-read, so the
      monitor counters reflect the extent movement.
    defaults: PT5S

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for the REDUCE
      statements to run.
    defaults: true

variables:
  detect_sql: |
    SELECT
      RTRIM(TBSP_NAME) AS TS_NAME,
      TBSP_PAGE_SIZE AS PAGE_SIZE,
      TBSP_TOTAL_PAGES AS TOTAL_PAGES,
      TBSP_USED_PAGES AS USED_PAGES,
      TBSP_FREE_PAGES AS FREE_PAGES,
      TBSP_PENDING_FREE_PAGES AS PENDING_FREE_PAGES,
      TBSP_PAGE_TOP AS HWM_PAGES,
      TBSP_PAGE_TOP - TBSP_USED_PAGES AS FREE_BELOW_HWM,
      (TBSP_PAGE_TOP - TBSP_USED_PAGES) * TBSP_PAGE_SIZE / 1048576 AS RECLAIM_MB,
      (TBSP_PAGE_TOP - TBSP_USED_PAGES) * 100 / TBSP_PAGE_TOP AS FREE_BELOW_HWM_PCT
    FROM TABLE(MON_GET_TABLESPACE('', -2))
    WHERE TBSP_TYPE = 'DMS'
      AND TBSP_CONTENT_TYPE IN ('ANY', 'LARGE')
      AND RECLAIMABLE_SPACE_ENABLED = 1
      AND TBSP_AUTO_RESIZE_ENABLED = 1
      AND TBSP_STATE = 'NORMAL'
      AND TBSP_PAGE_TOP > 0
      AND RTRIM(TBSP_NAME) LIKE CAST(:name_pattern AS VARCHAR(128))
      AND RTRIM(TBSP_NAME) NOT LIKE CAST(:exclude_pattern AS VARCHAR(128))
      AND TBSP_PAGE_TOP - TBSP_USED_PAGES >= CAST(:min_pages AS BIGINT)
      AND (TBSP_PAGE_TOP - TBSP_USED_PAGES) * 100 >= CAST(:min_pct AS BIGINT) * TBSP_PAGE_TOP
    ORDER BY TBSP_PAGE_TOP - TBSP_USED_PAGES DESC

tasks:
  - id: detect
    type: io.kestra.plugin.jdbc.db2.Query
    description: >-
      Read-only. Lists tablespaces whose high water mark sits above their used
      pages by more than both thresholds. The filter keeps only DMS tablespaces
      with reclaimable storage, because REDUCE cannot move the mark on anything
      else.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.jdbc_username }}"
    password: "{{ inputs.jdbc_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.detect_sql) }}"
    parameters:
      name_pattern: "{{ inputs.name_pattern }}"
      exclude_pattern: "{{ inputs.exclude_pattern }}"
      min_pages: "{{ inputs.min_free_pages_below_hwm }}"
      min_pct: "{{ inputs.min_free_pct_below_hwm }}"

  - id: plan
    type: io.kestra.plugin.core.output.OutputValues
    description: The tablespace names to reduce, plus the page counts read before
      any change.
    values:
      names: >-
        {{ outputs.detect.rows | jq('map(.TS_NAME)') | first | toJson }}
      before: >-
        {{ outputs.detect.rows | jq('map(.TS_NAME + " hwm " +
        (.HWM_PAGES|tostring) + " used " + (.USED_PAGES|tostring) + " total " +
        (.TOTAL_PAGES|tostring) + " pendingfree " +
        (.PENDING_FREE_PAGES|tostring) + " reclaimable " +
        (.RECLAIM_MB|tostring) + "MB") | join("; ")') | first }}
      reclaim_mb: >-
        {{ outputs.detect.rows | jq('map(.RECLAIM_MB) | add // 0') | first }}

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      DB2 high water mark scan on {{ inputs.jdbc_url }}. {{ outputs.detect.size
      }} tablespace(s) hold free pages below the mark{{ outputs.detect.size > 0
      ? ', about ' ~ outputs.plan.values.reclaim_mb ~ ' MB recoverable. ' ~
      outputs.plan.values.before : '' }}.

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.detect.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'DB2 ' ~ inputs.jdbc_url ~ ': ' ~ outputs.detect.size ~ ' tablespace(s) keep allocated pages below the high water mark, about ' ~ outputs.plan.values.reclaim_mb ~ ' MB that the filesystem will not get back until a REDUCE runs. ' ~ outputs.plan.values.before ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Waiting for approval to run ALTER TABLESPACE REDUCE ' ~ inputs.reduce_clause ~ '.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: approve
            type: io.kestra.plugin.core.flow.Pause
            description: >-
              Extent movement reads and rewrites every page it relocates, so it
              competes with live work. Approve inside a maintenance window.
              Killing the execution here leaves the tablespaces untouched.
            onResume:
              - id: approved
                type: BOOL
                displayName: Run ALTER TABLESPACE REDUCE now
                description: Set true to reclaim the space. Anything else leaves every
                  tablespace as it is.

          - id: approved_gate
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approve.onResume.approved ?? false }}"
            then:
              - id: remediate
                type: io.kestra.plugin.core.flow.Loop
                description: >-
                  Sequential remediation (concurrencyLimit 1). One tablespace at
                  a time, because extent movement is I/O heavy and two at once
                  just lengthens the window.
                concurrencyLimit: 1
                values: "{{ outputs.plan.values.names }}"
                tasks:
                  - id: reduce
                    type: io.kestra.plugin.jdbc.db2.Query
                    description: >-
                      Lowers the high water mark and hands the freed extents
                      back. ALTER TABLESPACE is plain SQL, so it runs directly
                      rather than through SYSPROC.ADMIN_CMD, which only accepts
                      CLP commands.
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.jdbc_username }}"
                    password: "{{ inputs.jdbc_password }}"
                    fetchType: NONE
                    allowFailure: true
                    sql: 'ALTER TABLESPACE "{{ item.value }}" REDUCE {{ inputs.reduce_clause }}'

              - id: settle
                type: io.kestra.plugin.core.flow.Sleep
                description: Let the monitor counters catch up with the extent movement before
                  the re-read.
                duration: "{{ inputs.settle_duration }}"

              - id: reverify
                type: io.kestra.plugin.jdbc.db2.Query
                description: >-
                  Same query, read again. A REDUCE that DB2 refused still
                  reports success on the client, so this re-read is what decides
                  whether the space came back.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.jdbc_username }}"
                password: "{{ inputs.jdbc_password }}"
                fetchType: FETCH
                sql: "{{ render(vars.detect_sql) }}"
                parameters:
                  name_pattern: "{{ inputs.name_pattern }}"
                  exclude_pattern: "{{ inputs.exclude_pattern }}"
                  min_pages: "{{ inputs.min_free_pages_below_hwm }}"
                  min_pct: "{{ inputs.min_free_pct_below_hwm }}"

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.reverify.size > 0 }}"
                then:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      The high water mark did not come down far enough. Before:
                      {{ outputs.plan.values.before }}. After: {{
                      outputs.reverify.rows | jq('map(.TS_NAME + " hwm " +
                      (.HWM_PAGES|tostring) + " used " + (.USED_PAGES|tostring)
                      + " total " + (.TOTAL_PAGES|tostring)) | join("; ")') |
                      first }}. DB2 refuses ALTER TABLESPACE with SQL0552N
                      unless the account holds SYSCTRL or SYSADM, which the task
                      reports as success. Check that first, then check that no
                      load or backup is holding the tablespace.
                else:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: >-
                      Reclaimed and verified on {{
                      fromJson(outputs.plan.values.names) | join(', ') }}.
                      Before: {{ outputs.plan.values.before }}. Every tablespace
                      is now under both thresholds.
            else:
              - id: declined
                type: io.kestra.plugin.core.log.Log
                message: >-
                  Approval declined. No ALTER TABLESPACE statement ran. Still
                  holding free pages below the mark: {{
                  outputs.plan.values.before }}.
        else:
          - id: report_only
            type: io.kestra.plugin.core.log.Log
            message: >-
              Reporting mode (auto_remediate={{ inputs.auto_remediate }},
              dry_run={{ inputs.dry_run }}). Would run ALTER TABLESPACE "<name>"
              REDUCE {{ inputs.reduce_clause }} on {{
              fromJson(outputs.plan.values.names) | join(', ') }} to recover
              about {{ outputs.plan.values.reclaim_mb }} MB. Nothing changed.
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: >-
          No tablespace matching {{ inputs.name_pattern }} holds {{
          inputs.min_free_pages_below_hwm }} or more free pages below its high
          water mark at {{ inputs.min_free_pct_below_hwm }} percent. Nothing to
          reclaim.

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'DB2 high water mark guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). Check the logs.'} | toJson }}

triggers:
  - id: weekly
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled. Always runs in safe mode.
    cron: "40 3 * * 0"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true

A DB2 DMS tablespace grows to its high water mark and then stays that big. Drop a large table or delete most of its rows. DB2 hands the extents back to the tablespace as pending-free. The container file on disk does not shrink. TBSP_USED_PAGES falls, so a used-pages check looks healthy, while TBSP_TOTAL_PAGES and TBSP_PAGE_TOP stay where the data left them. The volume stays full. Nothing in the database raises an alert, because from the database's point of view there is no problem at all.

Findings

On the rig behind this blueprint, a tablespace with 120000 rows in it read 8192 total pages, 8160 used pages, 0 free pages, high water mark 8160. After DROP TABLE the same tablespace read 96 used pages and a high water mark still at 8160, with 8064 pages parked in TBSP_PENDING_FREE_PAGES. So 32 MB of a 33 MB tablespace was dead space that only an ALTER TABLESPACE ... REDUCE would return.

TBSP_FREE_PAGES is the wrong number to watch here. It counts pages above the high water mark, which is why it read 0 for the tablespace holding 8064 recoverable pages. The number that matters is TBSP_PAGE_TOP - TBSP_USED_PAGES.

Safety

Detection is a single read of MON_GET_TABLESPACE. The write needs auto_remediate true, dry_run false, then a typed approval at a Pause. Killing the execution at the pause leaves every tablespace as it was. Each ALTER TABLESPACE runs on its own, never two at once, because extent movement competes with live queries for I/O.

How it works

detect reads MON_GET_TABLESPACE('', -2) and keeps the rows where TBSP_PAGE_TOP - TBSP_USED_PAGES clears both a page threshold and a percentage threshold. The filter also drops anything REDUCE cannot move: SMS tablespaces, temporary content, tablespaces without reclaimable storage, tablespaces without automatic resize, anything not in NORMAL state.
plan records the names plus the page counts as they were before the run.
summary logs the scan. route sends a Slack message naming each tablespace, its high water mark, its used pages, its pending-free pages, the megabytes on offer.
In reporting mode the flow stops there and logs the statement it would have run.
With auto_remediate true and dry_run false, approve pauses for a typed true.
remediate loops over the tablespaces at concurrencyLimit: 1 and runs ALTER TABLESPACE "<name>" REDUCE <clause> on each.
settle waits, then reverify runs the same query again. If any tablespace is still over both thresholds, remediation_failed fails the run with the before and after page counts.

What you get

A read of real page counts, not a guess from table sizes. MON_GET_TABLESPACE reports what the storage layer actually holds.
A detector that cannot fire on a tablespace it could not fix, because the same predicate that selects a target also checks that REDUCE is supported on it.
Two thresholds instead of one, so a 2 TB warehouse tablespace with 1 GB of slack stays quiet.
A reduce_clause that controls how much of the container file goes back, for the case where you want the mark lowered without handing the whole file to the filesystem.
A run that fails loudly when the space did not come back. A refused ALTER TABLESPACE reports success to the client, so the re-read is the only honest check.

Who it's for

DBAs and platform teams running DB2 LUW where storage is billed or capped. The classic trigger is an archive job that drops last quarter's partitions every month, after which the database reports plenty of room while the mount point stays at 95 percent. Also useful after a one off purge, a table rebuild, a migration that left a staging tablespace behind.

Why orchestrate this with Kestra

Reclaiming space is a read, a decision, a write, then a second read that has to be able to fail the job. A cron script can do the first three. It cannot pause for a human, keep the approval next to the evidence that prompted it, then prove the change landed. Kestra gives the Pause, the typed approval, the per task logs that show the page counts before and after, the errors: block that reaches Slack when the connection itself breaks. The flow ships with the schedule disabled, so it is a reporting job until someone turns it on.

Tested end to end

Run against DB2 12.1.5.0 in a container, with DMS tablespaces under automatic storage.

Compact control tablespace, 160 used pages at high water mark 160: zero findings, clean ran.
Degraded tablespace, 96 used pages at high water mark 8160: detection fired with 8064 free pages below the mark, 98 percent, 31 MB recoverable. Both tablespaces were inside the scan pattern on that run, so the compact one was in scope. Only the degraded one was reported.
dry_run=true: logged the statement it would run, changed nothing. The page counts read the same from the DB2 CLI afterwards.
auto_remediate=true, dry_run=false, approval true, clause MAX: high water mark 8160 to 96, total pages 8288 to 192, read back from the DB2 CLI.
Same run with clause 1 PERCENT: high water mark 8160 to 96, total pages 8384 to 8288. The clause sized the container shrink. The mark came down either way.
Approval declined: no ALTER TABLESPACE ran, page counts unchanged at 8160 and 8384.
Account without SYSCTRL: DB2 refused the ALTER TABLESPACE with SQL0552N while the task itself reported success. The re-read found the mark still at 8160 with 8384 total pages, so the run failed with the before and after counts in the message. The errors: block posted to Slack.
Unreachable JDBC URL: detect failed, the errors: block posted to Slack.

Prerequisites

DB2 LUW 10.1 or later, since ALTER TABLESPACE ... REDUCE needs reclaimable storage, which is a property of tablespaces created at DB2 9.7 or later. A tablespace carried forward from an older release reports RECLAIMABLE_SPACE_ENABLED = 0 and is skipped by the detector.

Secrets

DB2_PASSWORD: password for the database user.
SLACK_WEBHOOK_URL: incoming webhook for the alert and the failure notification.

Inputs

jdbc_url, jdbc_username, jdbc_password: the connection. The user needs EXECUTE on MON_GET_TABLESPACE to detect, SYSCTRL or SYSADM to reduce.
name_pattern, exclude_pattern: LIKE patterns scoping the scan. Defaults are every tablespace except SYS%.
min_free_pages_below_hwm (default 1000), min_free_pct_below_hwm (default 20): both have to be met before a tablespace is reported.
reduce_clause (default MAX): what follows REDUCE. MAX, a size such as 200 M, a percentage such as 25 PERCENT.
settle_duration (default PT5S): wait between the last statement and the re-read.
auto_remediate (default false), dry_run (default true): both have to be flipped before any write happens.

Quick start

Add the DB2_PASSWORD secret plus SLACK_WEBHOOK_URL.
Run it as it is. You get a Slack message listing every tablespace holding reclaimable pages with the megabytes on offer, no change to the database.
Tune min_free_pages_below_hwm and min_free_pct_below_hwm until the list is the one you would act on.
To reclaim, run with auto_remediate=true and dry_run=false, then answer the pause with approved=true inside a maintenance window.
Enable the Schedule trigger once the reporting output looks right. It forces safe mode, so enabling it only turns on the weekly report.

How to extend

Set reduce_clause to a size or a percentage if you want the mark lowered while the container file keeps most of its space, so a tablespace that refills every month is not re-extending.
Add an io.kestra.plugin.jdbc.db2.Query step before the loop that runs CALL SYSPROC.ADMIN_CMD('REORG TABLE ...') on the tables in the target tablespace, so rows pack down before the mark is lowered. REDUCE moves extents, it does not compact rows.
Replace the Slack task with io.kestra.plugin.opsgenie.notifications.OpsgenieAlert to route the finding at an on call rota.
Write the before and after page counts to a KV pair with io.kestra.plugin.core.kv.Set to trend how fast each tablespace refills.
Point name_pattern at one storage group per execution if you want separate maintenance windows per group.

DB2 Tablespace High Water Mark Reduce Guard

Findings

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

DB2 Tablespace High Water Mark Reduce Guard

Findings

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Data Blueprints

AES-256 Argon2id Cryptographic Landing and Compliance Gate

Adanos Asset Sentiment Drift Sentinel

AI-Powered Inventory Demand Forecasting, Auto-Generate Purchase Orders with OpenAI

Aerospike Stop-Writes Headroom Sentinel

Automate Customer Feedback Triage, Sentiment Analysis, and Escalation with Google Gemini and Slack

AI Data Quality Anomaly Advisor and SQL Remediation Generator

More Infrastructure Blueprints

ADLS World-Writable ACL Sentinel

ACM Certificate Expiry Compliance Sentinel

Renew TLS certificates through ACME, verify them, deploy them and roll back on failure

Environmental Disaster Early Warning System

AI Incident Post-Mortem and Root Cause Generator

Aerospike Stop-Writes Headroom Sentinel

New to Kestra?