DB2 Lock Escalation LOCKLIST Autotune Guard

id: db2-lock-escalation-locklist-autotune-guard
namespace: company.team

description: |
  Catch DB2 lock escalation before it turns row locks into table locks and starts timing out
  transactions. Lock escalation fires when the lock memory (LOCKLIST) fills up or one application
  passes MAXLOCKS percent of it, so DB2 trades thousands of row locks for one table lock to free
  memory. That table lock blocks everyone else. This flow reads LOCK_ESCALS from MON_GET_DATABASE
  and MON_GET_WORKLOAD, compares it against a KV baseline from the previous run so a rising count
  is told apart from an old one. It also reads whether LOCKLIST and MAXLOCKS are fixed or AUTOMATIC.
  When escalations are rising while the memory is a fixed value it hands LOCKLIST and MAXLOCKS to
  the self tuning memory manager (or applies a bounded bump) through SYSPROC.ADMIN_CMD, then reads
  the configuration again and fails the run if the change did not land. 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
    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: escalation_threshold
    type: INT
    displayName: New escalations that count as rising
    description: Lock escalations added since the previous run that make this run a
      finding. 1 means any new escalation.
    defaults: 1

  - id: remediation_mode
    type: SELECT
    displayName: Remediation mode
    description: automatic hands LOCKLIST and MAXLOCKS to the self tuning memory
      manager. bump sets the fixed targets below.
    values:
      - automatic
      - bump
    defaults: automatic

  - id: bump_locklist_pages
    type: INT
    displayName: LOCKLIST target in 4K pages (bump mode)
    description: Fixed LOCKLIST size applied in bump mode. 4096 pages is 16 MB of
      lock memory.
    defaults: 4096

  - id: bump_maxlocks_percent
    type: INT
    displayName: MAXLOCKS target percent (bump mode)
    description: Percent of LOCKLIST one application may hold before it escalates.
      Applied in bump mode.
    defaults: 20

  - 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, before the
      configuration is changed.
    defaults: true

variables:
  baseline_key: "db2_lockescal_baseline_{{ inputs.jdbc_url | replace({'.': '_',
    '-': '_', ':': '_', '/': '_'}) }}"

  escals_sql: |
    SELECT SUM(LOCK_ESCALS) AS LOCK_ESCALS, SUM(LOCK_TIMEOUTS) AS LOCK_TIMEOUTS,
           SUM(LOCK_WAITS) AS LOCK_WAITS, SUM(DEADLOCKS) AS DEADLOCKS
    FROM TABLE(MON_GET_DATABASE(-2))

  workload_sql: |
    SELECT RTRIM(WORKLOAD_NAME) AS WORKLOAD_NAME, SUM(LOCK_ESCALS) AS LOCK_ESCALS
    FROM TABLE(MON_GET_WORKLOAD(CAST(NULL AS VARCHAR(128)), -2))
    GROUP BY WORKLOAD_NAME
    HAVING SUM(LOCK_ESCALS) > 0
    ORDER BY 2 DESC

  config_sql: |
    SELECT RTRIM(NAME) AS NAME, RTRIM(VALUE) AS VALUE, RTRIM(VALUE_FLAGS) AS VALUE_FLAGS,
           RTRIM(DEFERRED_VALUE) AS DEFERRED_VALUE, RTRIM(DEFERRED_VALUE_FLAGS) AS DEFERRED_VALUE_FLAGS
    FROM SYSIBMADM.DBCFG
    WHERE NAME IN ('locklist', 'maxlocks')
    ORDER BY NAME

tasks:
  - id: read_escals
    type: io.kestra.plugin.jdbc.db2.Query
    description: Read-only. Database wide lock escalation, timeout and wait counts
      from MON_GET_DATABASE.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.jdbc_username }}"
    password: "{{ inputs.jdbc_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.escals_sql) }}"

  - id: read_workload
    type: io.kestra.plugin.jdbc.db2.Query
    description: Read-only. Lock escalations per workload from MON_GET_WORKLOAD, so
      the alert can name what is escalating.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.jdbc_username }}"
    password: "{{ inputs.jdbc_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.workload_sql) }}"

  - id: read_config
    type: io.kestra.plugin.jdbc.db2.Query
    description: Read-only. The current LOCKLIST and MAXLOCKS values and whether
      each is fixed or AUTOMATIC.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.jdbc_username }}"
    password: "{{ inputs.jdbc_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.config_sql) }}"

  - id: assess
    type: io.kestra.plugin.core.output.OutputValues
    description: >-
      Compare the escalation count against the KV baseline, read the LOCKLIST
      and MAXLOCKS state, decide the verdict and build the remediation command
      and the messages.
    values:
      items: >-
        {{ {'esc': outputs.read_escals.rows, 'cfg': outputs.read_config.rows,
        'wl': outputs.read_workload.rows,
            'base': (kv(render(vars.baseline_key), errorOnMissing=false) ?? {}),
            'thr': inputs.escalation_threshold, 'mode': inputs.remediation_mode, 'bl': inputs.bump_locklist_pages,
            'bm': inputs.bump_maxlocks_percent, 'ar': inputs.auto_remediate, 'dr': inputs.dry_run,
            'armed': (inputs.auto_remediate and not inputs.dry_run), 'url': inputs.jdbc_url} | jq('
          . as $i
          | ($i.esc[0] // {}) as $e
          | (($e.LOCK_ESCALS // 0) | tonumber) as $cur
          | (($e.LOCK_TIMEOUTS // 0) | tonumber) as $timeouts
          | (($e.LOCK_WAITS // 0) | tonumber) as $waits
          | (($e.DEADLOCKS // 0) | tonumber) as $deadlocks
          | (reduce ($i.cfg // [])[] as $r ({}; .[$r.NAME] = $r)) as $cm
          | ($cm.locklist // {}) as $ll
          | ($cm.maxlocks // {}) as $mx
          | (($ll.VALUE // "0") | tonumber) as $ll_val
          | (($mx.VALUE // "0") | tonumber) as $mx_val
          | (($ll.VALUE_FLAGS // "") == "AUTOMATIC") as $ll_auto
          | (($mx.VALUE_FLAGS // "") == "AUTOMATIC") as $mx_auto
          | ($ll_auto and $mx_auto) as $both_auto
          | ($i.base.escals // null) as $base
          | ($base != null) as $has_base
          | (if $has_base then ($cur - ($base | tonumber)) else null end) as $delta
          | ($has_base and ($delta >= $i.thr)) as $rising
          | (if ($i.wl // []) == [] then [] else [ $i.wl[] | {name: .WORKLOAD_NAME, escals: (.LOCK_ESCALS | tonumber)} ] end) as $wls
          | (if ($has_base | not) then "BASELINE" elif ($rising | not) then "OK" elif $both_auto then "TUNED_ALREADY" else "ACTION" end) as $verdict
          | (if $i.mode == "automatic" then "UPDATE DB CFG USING LOCKLIST AUTOMATIC MAXLOCKS AUTOMATIC" else "UPDATE DB CFG USING LOCKLIST \($i.bl) MAXLOCKS \($i.bm)" end) as $cmd
          | ($verdict == "ACTION" and $i.armed) as $will_change
          | (($verdict == "ACTION") or ($verdict == "TUNED_ALREADY")) as $attention
          | ("DB2 lock escalation on " + $i.url + ": LOCK_ESCALS \($cur)"
             + (if $has_base then " (\(if $delta >= 0 then "+" else "" end)\($delta) since the previous run)" else " (first run, baseline recorded)" end)
             + ". LOCKLIST \($ll_val) pages \(if $ll_auto then "AUTOMATIC" else "fixed" end). MAXLOCKS \($mx_val) percent \(if $mx_auto then "AUTOMATIC" else "fixed" end)."
             + " LOCK_TIMEOUTS \($timeouts). LOCK_WAITS \($waits). DEADLOCKS \($deadlocks)."
             + (if ($wls | length) > 0 then " Escalating workloads: " + ([$wls[] | "\(.name) \(.escals)"] | join("; ")) + "." else "" end)) as $core
          | (if $will_change then "Applying " + $cmd + " now." elif $verdict == "ACTION" then "Fix available: " + $cmd + ". No change applied (auto_remediate=\($i.ar) dry_run=\($i.dr))." elif $verdict == "TUNED_ALREADY" then "LOCKLIST and MAXLOCKS are already AUTOMATIC so a configuration change will not help. Look at the escalating workload." elif $verdict == "BASELINE" then "First run. Baseline recorded. Rerun to measure new escalations." else "No new lock escalations since the baseline." end) as $action
          | {verdict: $verdict, cur_escals: $cur, base_escals: $base, delta: $delta, lock_timeouts: $timeouts,
             lock_waits: $waits, deadlocks: $deadlocks, locklist_pages: $ll_val, locklist_flags: ($ll.VALUE_FLAGS // ""),
             maxlocks_percent: $mx_val, maxlocks_flags: ($mx.VALUE_FLAGS // ""), both_automatic: $both_auto,
             rising: $rising, has_baseline: $has_base, threshold: $i.thr, mode: $i.mode, remediation_cmd: $cmd,
             will_change: $will_change, attention: $attention, workloads: $wls,
             baseline: {escals: $cur, at: (now | floor)},
             report: ($core + " Verdict " + $verdict + ". " + $action),
             slack: ($core + " Verdict " + $verdict + ". " + $action)}
          ') | first | toJson }}

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: "{{ fromJson(outputs.assess.values.items) | jq('.report') | first }}"

  - id: record_baseline
    type: io.kestra.plugin.core.kv.Set
    description: >-
      Store this run's LOCK_ESCALS so the next run can measure new escalations.
      No ttl is set: an expired KV key throws on read and would lose the
      baseline silently.
    key: "{{ render(vars.baseline_key) }}"
    kvType: JSON
    value: "{{ fromJson(outputs.assess.values.items) | jq('.baseline') | first |
      toJson }}"

  - id: route
    type: io.kestra.plugin.core.flow.If
    description: Alert and consider remediation when escalations are rising.
      Otherwise log and stop.
    condition: "{{ fromJson(outputs.assess.values.items) | jq('if .attention then 1
      else 0 end') | first > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': (fromJson(outputs.assess.values.items) | jq('.slack') | first)} | toJson }}

      - id: planned
        type: io.kestra.plugin.core.log.Log
        description: What a remediation run would do, printed on every attention run
          including a dry one.
        message: >-
          {{ fromJson(outputs.assess.values.items) | jq('"Fix: " +
          .remediation_cmd + ". " + (if .will_change then "Running it now." else
          "No change applied this run (auto_remediate and dry_run gate it)."
          end)') | first }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        description: The configuration change runs only when the verdict is ACTION,
          auto_remediate is true and dry_run is false.
        condition: "{{ fromJson(outputs.assess.values.items) | jq('if .will_change then
          1 else 0 end') | first > 0 }}"
        then:
          - id: apply_config
            type: io.kestra.plugin.jdbc.db2.Query
            description: Change LOCKLIST and MAXLOCKS through ADMIN_CMD. UPDATE DB CFG is a
              utility command, not plain SQL.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.jdbc_username }}"
            password: "{{ inputs.jdbc_password }}"
            fetchType: NONE
            sql: "CALL SYSPROC.ADMIN_CMD(:cmd)"
            parameters:
              cmd: "{{ fromJson(outputs.assess.values.items) | jq('.remediation_cmd') | first
                }}"

          - id: reverify
            type: io.kestra.plugin.jdbc.db2.Query
            description: Read LOCKLIST and MAXLOCKS again. The run fails if the change did
              not land.
            url: "{{ inputs.jdbc_url }}"
            username: "{{ inputs.jdbc_username }}"
            password: "{{ inputs.jdbc_password }}"
            fetchType: FETCH
            sql: "{{ render(vars.config_sql) }}"

          - id: verify_result
            type: io.kestra.plugin.core.flow.If
            condition: >-
              {{ {'rows': outputs.reverify.rows, 'mode':
              inputs.remediation_mode, 'bl': inputs.bump_locklist_pages, 'bm':
              inputs.bump_maxlocks_percent} | jq('
                . as $i
                | (reduce ($i.rows // [])[] as $r ({}; .[$r.NAME] = $r)) as $m
                | ($m.locklist // {}) as $ll | ($m.maxlocks // {}) as $mx
                | (if $i.mode == "automatic" then ((($ll.VALUE_FLAGS // "") != "AUTOMATIC") or (($mx.VALUE_FLAGS // "") != "AUTOMATIC")) else ((($ll.VALUE // "0") | tonumber) < $i.bl) or ((($mx.VALUE // "0") | tonumber) < $i.bm) end)
                | if . then 1 else 0 end') | first > 0 }}
            then:
              - id: remediation_failed
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: >-
                  LOCKLIST and MAXLOCKS did not reach the target after
                  ADMIN_CMD. Current state: {{ outputs.reverify.rows |
                  jq('map("\(.NAME) VALUE \(.VALUE) flags \(.VALUE_FLAGS)
                  deferred \(.DEFERRED_VALUE)") | join("; ")') | first }}. A
                  change that shows only in DEFERRED_VALUE needs the database
                  reactivated.
            else:
              - id: remediated
                type: io.kestra.plugin.core.log.Log
                message: >-
                  {{ {'r': outputs.reverify.rows, 'oll':
                  fromJson(outputs.assess.values.items).locklist_pages, 'omx':
                  fromJson(outputs.assess.values.items).maxlocks_percent, 'ofl':
                  fromJson(outputs.assess.values.items).locklist_flags, 'omf':
                  fromJson(outputs.assess.values.items).maxlocks_flags} | jq('
                    . as $i | (reduce ($i.r // [])[] as $x ({}; .[$x.NAME] = $x)) as $m
                    | ($m.locklist // {}) as $ll | ($m.maxlocks // {}) as $mx
                    | "LOCKLIST was \($i.oll) (\($i.ofl)) now \(($ll.VALUE // "?")) (\(($ll.VALUE_FLAGS // "?"))). MAXLOCKS was \($i.omx) (\($i.omf)) now \(($mx.VALUE // "?")) (\(($mx.VALUE_FLAGS // "?"))). Change verified."
                  ') | first }}
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "{{ fromJson(outputs.assess.values.items) | jq('.report') | first }}"

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

triggers:
  - id: hourly
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled. Always runs in safe mode. Hourly gives the
      baseline a short gap so a fresh burst of escalations shows up.
    cron: "20 * * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true